Count Records by Reject type on Max Date

[crystal2000a]

Hi Experts,

I'm extremely new to access and have been given the task to create and send a report for the number of rejects at the end of the day. I'm trying to set up a query to do this but it isn't working quite as I want it to.

I have Q1-C that is set to count the number of rejects at stations. However when I run this query, it counts total rejects by type (all records) and not by date. For example, on 12/3/12 there are 2 rejects (1 - Epoxy on screen and 1 - electrolyte leaks) but the query counts 17 electrolyte leaks and 1 epoxy on screen for 12/3/12.




Next I would like to build a report that would display the type of rejects of the last day for both 'Rejects at Stations' and 'Screen Rejects'. So the report "Productions Rejects"should show rejects on 12/3/12 for Q1-C and 12/4/12 for Q1-O. However, the report now shows all the dates of rejects.

I have attached the database i'm working on. Any help would be appreciated.

[alansidman]

See if attached is what you are looking for. Needed to re-arrange your queries a bit.

[crystal2000a]

Thank you for the quick reply. Actually no, the numbers still do not match up. Basically, I want to do 3 things:

1. Count number of rejects on the max date grouped by station and reject type for "Reject by station" table
2. Sum of Quantities on max date grouped by station and reject type for "Screen Rejects" table.
3. Create a report to incorporate these 2 information (Report should show: 2 rejects for "Reject by Station" on 12/3/12 and 7 rejects for "Screen Rejects" on 12/4/12. All grouped by station and reject type)