Most Efficient Count?

[Gary Dolliver]

Hi all,
I am looking to create a query/report that will display a count of 3 tables.
The tables are t1, t2, t3 - the key OID links tables t1 and t2 (and is also
available in t3), where t2 can have multiple records from t1 and t3 may have
multiple records of t2, but also has the key of CID
What would be the most efficient way to display a count of all three tables
so my data line would be something like
t1Count t2Count t3Count
312 1231 34451
I have started creating 3 separate count queries and then joining them, but
it takes a long time for them to process. Would it be better to have the
table feed to a form and then have VBA run the count?
Any help would be greatly appreciated, thanks
-gary

 

[Douglas J. Steele]

It's almost always more efficient to use SQL than to use VBA to do the same
thing.

 

[Gary Dolliver]

Thank you for the reply. In your opinion, what would be the best and most
efficient method to acheive these counts? Would it be a count query for each
table, then combine these queries or is there a better way? Thanks again for
your time.
-gary

 

[Douglas J. Steele]

I'm having a hard time grasping what exactly your counts are.

Could you post some sample data for the three tables and what the counts
should be? Note that I'm not suggesting you post a sample database: I just
want something like

t1
OID
1
2
3

t2
OID Field1
1 A
1 B
2 C
3 C

and so on.

 

[Gary Dolliver]

Thanks again for the reply. Here is some sample data:
t1
t1ID
1
2
3
4
5

t2
t2ID t1ID
1 1
2 1
3 2
4 3
5 4
6 5

t3
t3ID t2ID t1ID
1 1 1
2 1 1
3 2 1
4 2 1
5 3 2
6 4 3
7 5 4
8 5 4
9 6 5
10 6 5

each table will also have other fields that I may filter on later, but with
the above, I would like to present it as follows in the most efficient,
quickest way:
t1Count t2Count t3Count
5 6 10

thanks again
-gary

 

[Douglas J. Steele]

Based on what you're showing, I'd say 3 separate Count queries (one per
table) would likely be best.

 

[Marshall Barton]

Hi all,
I am looking to create a query/report that will display a count of 3 tables.
The tables are t1, t2, t3 - the key OID links tables t1 and t2 (and is also
available in t3), where t2 can have multiple records from t1 and t3 may have
multiple records of t2, but also has the key of CID
What would be the most efficient way to display a count of all three tables
so my data line would be something like
t1Count t2Count t3Count
312 1231 34451
I have started creating 3 separate count queries and then joining them, but
it takes a long time for them to process. Would it be better to have the
table feed to a form and then have VBA run the count?

If you just want to display the three table counts in form
text boxes, then DCount is an easy way to go.

If you really want a query, you can combine all of them into
one using something like:

SELECT Count(*) as t1Count,
(SELECT Count(*) FROM t2) As t2Count,
(SELECT Count(*) FROM t3) As t3Count,
FROM t1