By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,600 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,600 IT Pros & Developers. It's quick & easy.

Query/report problem - display total records for each of 20 queries

P: n/a
I have 20 queries that compare fields in one table with fields in
another table - the query results are the records that do not match in
Table1 and Table2. ie

Table1
DOB 28/02/78

Table2
DOB 27/02/78

This example would show up in my DOB query.

Because I have so many queries is is possible to count how many records
are in each query? Either to display the total count for each query in
a report or in another query.

I've tried DCount function in the query window (added a further column
to the original 20 queries)but it doesnt look like i want it to (shows
no of records on each row/record)

I've tried placing all the queries in a new query and using Expr1:
Count(*) for each of the 20 queries but "query too complex"is
displayed.

I just hope that I dont have to create a count (*) query for each of my
20 queries.

Any help would be greatly appreciated!!

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 23 May 2005 08:02:46 -0700, ge*********@hotmail.com wrote:
I have 20 queries that compare fields in one table with fields in
another table - the query results are the records that do not match in
Table1 and Table2. ie

Table1
DOB 28/02/78

Table2
DOB 27/02/78

This example would show up in my DOB query.

Because I have so many queries is is possible to count how many records
are in each query? Either to display the total count for each query in
a report or in another query.

I've tried DCount function in the query window (added a further column
to the original 20 queries)but it doesnt look like i want it to (shows
no of records on each row/record)

I've tried placing all the queries in a new query and using Expr1:
Count(*) for each of the 20 queries but "query too complex"is
displayed.

I just hope that I dont have to create a count (*) query for each of my
20 queries.

Any help would be greatly appreciated!!


I think what you could do is make a table of query names, then use DCount,
passing '*' as the Expr argument, and the query name field value as the Domain
argument.
Nov 13 '05 #2

P: n/a
Could you explain how I would do this please - I've created a new table
with column headings the same as the actual queries. But I cant do the
rest.........

Thankyou

Nov 13 '05 #3

P: n/a
On 24 May 2005 01:36:08 -0700, ge*********@hotmail.com wrote:
Could you explain how I would do this please - I've created a new table
with column headings the same as the actual queries. But I cant do the
rest.........

Thankyou


That's not what I meant. I meant you would have a table with one field, and
each row of the table holds the name of one query. Then you just write a
query of that table that executes DCount using the field value from the table
of queries as the Domain argument. The result should be a set with one row
for each of the queries, showing the count of each one. If you have to have
that in a horizontal layout, you can do a crosstab.
Nov 13 '05 #4

P: n/a
Thankyou you very much, just one other small problem when I use
DCount(*, Field1) an error occurrs

Field1 is the only field from the table of queries and I used the above
expression in a query of that table like you suggested!

I know I'm writing the expression wrong but have tried for hours now
and I simply cannot do it. How does the DCount know that I want to use
the field1 value( link to the query name)?

Many Thanks

Flick. x

Nov 13 '05 #5

P: n/a
If you typed exactly what you said...
DCount(*, Field1)
then yes, you would get an error. What you need is...
DCount('*', Field1)

On 25 May 2005 02:45:13 -0700, ge*********@hotmail.com wrote:
Thankyou you very much, just one other small problem when I use
DCount(*, Field1) an error occurrs

Field1 is the only field from the table of queries and I used the above
expression in a query of that table like you suggested!

I know I'm writing the expression wrong but have tried for hours now
and I simply cannot do it. How does the DCount know that I want to use
the field1 value( link to the query name)?

Many Thanks

Flick. x


Nov 13 '05 #6

P: n/a


Steve Jorgensen wrote:
If you typed exactly what you said...
DCount(*, Field1)
then yes, you would get an error. What you need is...
DCount('*', Field1)

On 25 May 2005 02:45:13 -0700, ge*********@hotmail.com wrote:
Thankyou you very much, just one other small problem when I use
DCount(*, Field1) an error occurrs

Field1 is the only field from the table of queries and I used the above
expression in a query of that table like you suggested!

I know I'm writing the expression wrong but have tried for hours now
and I simply cannot do it. How does the DCount know that I want to use
the field1 value( link to the query name)?

Many Thanks

Flick. x


Thankyou very much - it worked!!!

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.