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

[geronimo_me]

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!!

[Steve Jorgensen]

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.

[geronimo_me]

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

[Steve Jorgensen]

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.

[geronimo_me]

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

[Steve Jorgensen]

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

[geronimo_me]

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!!!