Subquery Help

[MTSPEER]

Hello,



I'm trying to figure out the best solution to this problem. I'm trying to find the total count of emails, oldest date in folder, and number of emails with the oldest date per folder that I have linked from Microsoft Outlook. I have about 20 folder I have linked to in Microsoft Access. I have figured out how to get the oldest date and the count of total emails per folder. But I need to get the number of emails with the oldest date per folder. I don't know if I can do this with all one query since I would have 2 count functions. And I can't think of the best way for a subquery with this many tables. I would have so many queries. Your help would be much appreciated.

Thanks

[June7]

Maybe like:

SELECT "T1" AS Source, Max(datefield) AS MaxDate, Count(*) As TotalEmails, (SELECT Count(*) AS CM FROM table1 WHERE datefield=DMax("datefield","table1")) AS CntMax FROM table1 GROUP BY "T1"
UNION SELECT "T2", Max(datefield), Count(*), (SELECT Count(*) AS CM FROM table2 WHERE datefield=DMax("datefield","table2")) FROM table2 GROUP BY "T2";

or

SELECT "T1" AS Source, Max(datefield) AS MaxDate, Count(*) As TotalEmails, (SELECT Count(*) AS CM FROM table1 WHERE datefield=(SELECT Max(datefield) FROM table1)) AS CntMax FROM table1 GROUP BY "T1"
UNION SELECT "T2", Max(datefield), Count(*), (SELECT Count(*) AS CM FROM table2 WHERE datefield=(SELECT Max(datefield) FROM table2)) FROM table2 GROUP BY "T2";