Access Data Retrieval
Hello, all you Access experts on this site. I hope you can help me resolve the problem described below.
I have two tables in a database with approximately 200,000 records in each table with 165 relevant names in the AdjName field in the Determinations table and the StaffId field in the Voids table (the screen shots in the attached document show the two tables filtered for just my identifying information).
The Determinations table has in the DeterminationDate field the exact date that a determination was entered, while the Voids table has in the DeterminationDate field the week-ending date when the action stored in that table was performed.
The Determinations table has as an identifying name for the person entering the determination the value in the AdjName field, while the Voids table has as an identifying name for the person performing the action the value in the StaffId field.
The Staff table uses StaffId as the primary key.
The WeekEndDates table uses WeekEndDate as the primary key.
What I want to know is how do I construct a query to extract the total number of records by week-ending date in the Determinations and Voids tables for each one of the 165 relevant names, so that there is a count for the number of actions performed in each particular week-ending period?
In other words, for AdjName "Isenberg, Errol" I would want to see the number of determinations entered during the week ending 1/7/17, 1/14/17, 1/21/17, etc. through the week ending 5/13/17 (there should be a separate count for each week).
Then, for staffId "isenbe0000" I would want to see the number of void actions performed for each one of the individual weeks.
Unfortunately, for business reasons, I am not able to attach the actual database to this question.
If anyone can help me with this, I would greatly appreciate it. Thank you very much.
The attached document shows the existing relationships within the database and screenshots of the relevant tables.
Experts-Exchange-Access-Question.docx
I have two tables in a database with approximately 200,000 records in each table with 165 relevant names in the AdjName field in the Determinations table and the StaffId field in the Voids table (the screen shots in the attached document show the two tables filtered for just my identifying information).
The Determinations table has in the DeterminationDate field the exact date that a determination was entered, while the Voids table has in the DeterminationDate field the week-ending date when the action stored in that table was performed.
The Determinations table has as an identifying name for the person entering the determination the value in the AdjName field, while the Voids table has as an identifying name for the person performing the action the value in the StaffId field.
The Staff table uses StaffId as the primary key.
The WeekEndDates table uses WeekEndDate as the primary key.
What I want to know is how do I construct a query to extract the total number of records by week-ending date in the Determinations and Voids tables for each one of the 165 relevant names, so that there is a count for the number of actions performed in each particular week-ending period?
In other words, for AdjName "Isenberg, Errol" I would want to see the number of determinations entered during the week ending 1/7/17, 1/14/17, 1/21/17, etc. through the week ending 5/13/17 (there should be a separate count for each week).
Then, for staffId "isenbe0000" I would want to see the number of void actions performed for each one of the individual weeks.
Unfortunately, for business reasons, I am not able to attach the actual database to this question.
If anyone can help me with this, I would greatly appreciate it. Thank you very much.
The attached document shows the existing relationships within the database and screenshots of the relevant tables.
Experts-Exchange-Access-Question.docx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.