Combine Query

[Hudas]

Hi -

I have a query: the first one shows the number of emails received for each date

SELECT tbl_rEceived_eMail.ReceivedDate, Count(tbl_rEceived_eMail.EmailID) AS EmailCount
FROM tbl_rEceived_eMail
GROUP BY tbl_rEceived_eMail.ReceivedDate;

The second one shows the number of emails received for each that processed transaction in it, basically tbl_transaction_processed and tbl_received_email is related to each other with the EmailID

SELECT tbl_rEceived_eMail.ReceivedDate, Count(tbl_tRansaction_pRocessed.EmailID) AS EmailCount
FROM tbl_rEceived_eMail INNER JOIN tbl_tRansaction_pRocessed ON tbl_rEceived_eMail.EmailID = tbl_tRansaction_pRocessed.EmailID
GROUP BY tbl_rEceived_eMail.ReceivedDate;

Im hoping to get both data on one query if possible.

Thank you Hudas

 

[Cronk]

I hope for lots of things too.

You can't get your output in one query unless you want to pass on data into VBA for processing.

You could have a third query which combines the output in one query.

 

[plog]

You can definitely do this with just queries, no VBA necessary. However, you might not be able to do it in just 1 query. It depends on the relationship between tbl_rEceived_eMail and tbl_tRansaction_pRocessed.

For an EmailID in tbl_rEceived_eMail can there be more than one record in tbl_tRansaction_pRocessed? If so, it will take 2 queries, if not just 1.

Can you post some sample data from each table? Then the results you ultimately expect?

 

[Hudas]

Thank you Cronk

Thank you plog

Yes your right there can be multiple Email ID in tbl_transaction_processed while in tbl_Received_Email there can only be one. Kindly see attached example.

 

[plog]

You provided SQL for two queries, let's call the first one A and the second one B. B needs to become a sub query in A.

First rename the field in B that is currently named EmailCount. I don't know what to advise you to name it, but it can't be named the same as the field in A. For reference sake, let's call it 'CountB'. After you do that, open A in design view and add B to it. Link them via their ReceivedDate fields, bring in CountB and Group By it. Run that query and they should be combined.