How to count document received by dates

[winxmun]

Hi, I am trying to use Access to perform calculation based on data input by user. For example:

Based on data below, when I run query with received date = 13 Mar 2016, I am hoping Access to filter out record with received date = 13 Mar 2016 (which is Alice and William only) and auto fill up field "TotalDocReceived". thanks in advance for any help. :yes



Ref_No
Document1
Doc1ReceivedDate
Document2
Doc2ReceivedDate
TotalDocReceived



Alice
Title
15 Mar 2016
Option
13 Mar 2016




Stephen
Option
15 Mar 2016






William
Payslip
13 Mar 2016






May


Certificate
15 Mar 2016

[jonh]

SELECT a.*,
(SELECT Sum(1)
FROM YOURTABLE
GROUP BY Doc1ReceivedDate=#3/13/2016# Or Doc2ReceivedDate=#3/13/2016#
HAVING (Doc1ReceivedDate=#3/13/2016# Or Doc2ReceivedDate=#3/13/2016#)=True
) as TotalDocReceived
FROM YOURTABLE a
where (Doc1ReceivedDate=#3/13/2016# Or Doc2ReceivedDate=#3/13/2016#)=True

[winxmun]

Hi jonh, pardon me as I am not familiar with SQL, may I have it in query format? thank you very much!

[jonh]

How would you suggest I do that? Queries ARE sql. Create a query, go to sql view and paste it in.

[winxmun]

Finally figure out the sql and pasted into my query successfully. However the result of TotalDocReceived is not as expected. For example, if I execute the query with received date = 13 Mar 2016, the result should be 2 records only, ie Alice and William. TotalDocReceived for Alice should be 1 and William should be 1 too. Any idea which part of the sql should I amend to get the result? thank you!

[jonh]

Add fields to your query.
Turn on Totals (funny looking E)
A new row 'Total' is added to the property sheet and each property is set to 'Group By'.


Group By means that rows with the same values will be grouped together.
Use the drop down to select some other function for the field.


If you add a new field "TotalDocReceived: 1" every row will be 1 for that field and by turning on totals and setting the total option to sum or count you get the number of matching records.




SELECT Ref_No, Document1, Doc1ReceivedDate, Document2, Doc2ReceivedDate, Sum(1) AS TotalDocReceived
FROM yourtable
GROUP BY Ref_No, Document1, Doc1ReceivedDate, Document2, Doc2ReceivedDate
HAVING Doc1ReceivedDate=#3/13/2016# OR Doc2ReceivedDate=#3/13/2016#

[winxmun]

I still cant the result using this new field (may be I didn't create correctly). Anyway, I have found a longer way to get the result by using multiple queries. That is Query 1 to make a table (named Table 1) for Document 1 + Doc1ReceivedDate, then Query 2 to append Document 2 + Doc2ReceivedDate into Table1. Query 3 to group the data using Totals (using Group By and Count). Thank you for sharing on Totals.