I have a query that is derived from 3 tables with relationships. i want the
user to open a form and be able to change data in that query. i'm assuming
it is not letting me because it is derived from more than one table. is the
only solution to this is to create a table off the query and then base my
form on that table.
wasnt sure if there was a different way around this.
> I have a query that is derived from 3 tables with relationships. i
> want the user to open a form and be able to change data in that
> query. i'm assuming it is not letting me ...Query with two date field for Date range
How do I create a Query using two tables that have dates field to
calculate a value using both the tables for a Date Range. For Example
I have four TABLES- Time Card(TC), PROJECT, TC hours(HOURS), and TC
expenses(EXPENSE). TC contains name of employees and their billing
rates. PROJECT contains Name of Project. HOURS field are TCId,
PROJECTId, Dateworked, and Hours. EXPENSE contains fields TCId,
PROJECTId, Dateexpense, Expense.
I want to build a query that over a Date Range that sums billable
amount=hours worked* billing rate+ expense.
I set the Dateworked >=[forms]![Report Date Range]![Be...Word wrapping paragraphs within cells
I support a user who stores narrative data within a spreadsheet. They want
to know why Excel 2000 SR-1 inconsistently wraps text within a cell after
the narrative exceeds 8 lines. This occurs regardless of cell height. They
also want to know how to fix it so the text wraps consistently regardless of
I know the obvious answer (store the data somewhere else) - but does anyone
have any more politically acceptable ideas?
Thanks in advance.
I don't store that much text in cells, but you may want to have them try putting
some alt-enters in their text.
Exce...Pivot Query Help
I have the following query. I would like 'createnamerole' to become the
columns and 'ordersourcedesc' to become the rows with the count values being
the data contained between. I beleive I should use PIVOT but am unaware how.
Select b.OrderSourceDesc, b.CreateNameRole, Count(b.OrderID) as Countof
(Select a.OrderID, a.CustomerNumber, a.OrderDate, a.ProductID,
From dbo.tblOrderSource S With (nolock) Inner join
(SELECT O.OrderID, O.CustomerNumber, O.OrderDate, OL.ProductID,
FR...Problems saving pivot table in same workbook as export query
The user exported a query into a workbook, in the same workbook she added a
pivot table worksheet. She saved the workbook but only the export saved. She
needs to know if the pivot table can be saved and refreshed with new data
later on and saved in the same workbook from an export query. Or does it have
to be in a workbook by itself and saved. Please help.
...Filter recordset using query results
I have a form based on a query called [qry Quarterly Planning], it lists all
Itineraries on the system. On this form you can filter records by specifying
a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a
subform on a main unbound form, lets call this Subform1. Along side this I
have another subform (Subform2) which displays ReviewDates that exist
against an Itinerary. In other words Subform1 has a start date of an
activity and if the activity lasts longer than 1 day, then the additional
dates are stored in Subform2 (ItineraryDates). Currently when I...Query parameter "refresh when value changes" doesn't refresh
In excel I set up a parameter query. In the query parameter properties I
checked "Get the value from the following cell" and checked the box "Refresh
Automatically when cell value changes". It's doesn't refresh automatically
when I change the paramenter value and I still have to manually refresh. Any
clues why the automatic refresh isn't working?
...With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no
auditor records (Table 3). Somehow, I need to use the relationship between
tables 2 and 3 to find what's not in table 1. The following query gives me a
list of all records that do have auditor records. I'm at a dead end on this
AuditDet...Query on "Yes" Brings Back both
I have created a table where the value is Yes/No - text with default as No.
I have a query in which I am trying to bring back only the "Yes", but both
yes and no are being shown.
Any ideas? Thanks
It keeps saying "Data Type mismatch" but how can that be when I created the
query from scratch?
SELECT [all 05].Examiner, [all 05].[Risk Number], [all 05].Company, [all
05].Address1, [all 05].Address2, [all 05].City, [all 05].State, [all
05].[Postal Code], [all 05].Assn, [all 05].[07 Group], [all 05].[Retainer for
2007], [all 05].[Claim Salutation], [all 05].[Cla...dropdown data for form fill-in
I've created a form and it includes selecting data from a dropdown box, and
it populates my table. However, when I run a query, it won't pick up the
record that has the data criteria from the "dropdown" list.
Just a guess, but it sounds to me like you may have one of those evil table
lookups defined at the table or query level. If this is the case, the value
being stored in the table may not be as it appears. Please see the 2nd
Commandment of Access here:
The Ten Commandments of Access
If you see a combo...Duplicates not added in a Cross Tab Query
I have a table that contains transactions amounts, transaction codes, and
dates. I have created a Cross Tab query that that has the Transaction Code
and the Row Heading, the Date as the Column heading, and the Transaction
Amount as the value (summed). My goal is a to produce a query that shows the
Sum Transaction amount by code and date. The issue I have is that when I
have a duplicate transaction amount on the same day, which is very frequent,
the Transaction Amount does not sum all of the duplicates. If the amounts
are different, they are added. If they are the same, the query on...Need to automate print reports-- for diff values in the query field
I want to email each of my sales reps a snapshot of a Access report that
lists their sales invoices for the month (only their sales invoices)
Presently I do this by running a macro that runs a query, report & emails a
I physically enter the value for a field [REP] as a variable for the query.
It works fine, but we now have 20 reps so I have to run this macro and type
in each of teh 20 rep codes, and I trasnpose.
I need to create to automate this so that Access can change the query based
on thte [REP] field in a table.
I have table #1 that has one record for each rep ...Macros and Queries
I have someone who has queries and macros on a spreadsheet
and they would like to get those on someone else's
computer for that person to use as well. How do you
transfer those to another computer to use in another
Thanks in Advance,
...removing a web query
i have managed to make 2 web queries so that when i run them they both
ask for input
how can i simply remove one of them
thank for the help
highlight it in the worksheet and do Edit=>Clear => All
"bd" <firstname.lastname@example.org> wrote in message
> i have managed to make 2 web queries so that when i run them they both
> ask for input
> how can i simply remove one of them
> thank for the help
...Microsoft query does not recognize tables in Excel spreadsheet
Using Excel 2007 12.0 SP 2 on Windows XP
I have created an Excel spreadsheet, C:\Temp\PracDbase.xlsx with two tables:
Both of these are on the same worksheet, sheet1. The first table occupies
the range A1:B3, the second occupies the range D1:E3.
Both of these ranges have been converted to tables via Home/Format As Table.
If I look up names under Formulas/Name Manager, both of these
table names are there and they refer to the correct range.
ie, I am certain that the tables Table1 and Table2 exist in
and...Query Setup with Two Tables
Is this possible in a query?
There is a link or join between the two tables. Table A is the main table,
B is a sub or secondary.
When you view the records in datasheet, you see what is there. I want to
see ALL THE OTHER records, the ones that do not meet that requirement, they
do not have any records in table B. So they are being filtered out and I
want to only see those records.
Double click on the join line. Select option 2 or option 3 whichever says ALL
records in Table A and matching in Table B.
This should work AS LONG AS...Query Problems in access 2007
Hi,i am a new user to access 2007.i have a select Query with partial name
input.The criteria goes like this-Like * & [Enter a Part Number] & *
Intially,everything was fine.But whenever i am entering data to my
database.and i tried to use the query,it always prompt me twice.I tried
closing access and opening it again.But it stil prompts me twice.Only when i
delete and re-enter the criteria again,then it works fine again.Please
help,is there something wrong with my way of writing the criteria?Thank you.
Try putting double quotes around both asterisks.
Providing Cust...Having trouble with web query, importing not consistent.
I have a nice book set up with web querys and it's doing a fantastic job for
me, except for one little problem:
The data does not always get imported to exact same place in the sheet, I'll
The first column is supposed to contain a name, while the next 10 columns is
supposed to contain data after running a web update. That's the ideal
situation every time, and i'm updating once a day.
Now the problem is that somedays the data is imported in a different way.
For instance the name which is supposed to end up in the A column gets split
up so it now takes up t...return primary key for record after append query
Is there a way to find the primary key for a new record added to a table
using an append query in VBA? I need to assign that to a variable so that I
can use it an a second append query to a different table. For example:
tblCust: CustID (pk), CustName, CustAddress
tblOrder: OrderID (pk), CustID (fk), ProdID
If I use append query to add CustName and CustAddress to the tblCust table,
how do I get the CustID for that record to use in an append query to add
records to the tblOrder table?
It is possible to query the last key value like this:
Function ShowIdentity() As Variant
...Need shaded area in gantt chart to show lines around each grid squ
Hi - I have created a gantt chart using excel 2002. I have a shaded area on
my chart from 9/1-9/10/07. Can I make the shaded area show lines through it?
This would make it much easier to follow down the page on the date selected.
I know it is easily done in excel when you fill in say 3 squares in yellow
and then highlight the area and select borders. I tried doing this in my
chart, but can not make it work. Any help would be appreciated.
...Use Same Variable multiple times in a query with different criteri
ok i have this variable i am trying to use twice with different criteria and
it doesnt work help after i do with i want to count them
November: Date_mailed (Variable Name)
tblRecruitment2 (Table name)
Between #11/1/2009# And #11/30/2009# (Criteria)
Between #10/1/2009# And #10/31/2009#
I think you want to count? If so, set the E looking button on your tool bar,
which turns on the Group ON feature then change the Group On to count.
State of Arkansas
...Cursor position jumping around during document composition
I recently purchased an ASUS laptop with Windows 7 pre-installed. I have been
have major problems with cursor control when composing documents, even now as
I type. If I don't continue typing at lightning speed, the cursor position
changes in the document, sometimes wipes out text just typed or the computer
jumps to another open Microsoft application.
Any suggestions on the source of this problem and how to correct it?
...Assigning a banding marker in a query
I have a table that has a numeric column "sales" that ranges from 1 to
1,000,000. In a query, I am attempting to assign a banding marker to each
record. I have 200 bands so an IIf statement won't work.
So for instance, one of the 200 bands that is >=22 and less than 29 called
"Quite Low". How can I append the "Quite Low" marker to a new column,
"band", in my query for records that fit the criteria.
In the end, each row in the query should have a value for a similarly
defined range in the "Band" column.
Thanx - John
Build ...USe a Parameter query
I have a simple query that I wish to use to search for titles of Music CD's.
I wish to establish a parameter query that will allow the user to enter a
word and the query looks for that word in the title. I have lried .....Like
*[Enter the word]* in the query designand various combinations in between but
to no avail. Can it be done
try to use under the "Title filed "
Like "*" & ["Enter the Word" ] & "*"
so it can filter that word out of your database.
> I have a simple query that I w...Multiple domains in Query-based Distribution List
I would like to find out if/how it is possible to setup a query-based DL
with the following:
Include: All users with email address @domain1.com **OR** all users
I know you can add multiple domains/email addresses but what it effectivley
does is look for All users with email address @domain1.com **AND** all users
i.e The object has to satisfy both the criteria. I need a DL that picks up
the users if they satisfy either/or.
Your assistance is greatly appreciated
Here's a response I gave to a similar issue that was posted a few days