Access Query
Hi, all you Access experts out there. I have a query of a table in my database that returns approximately 200,000 records with the following fields:
WeekEndDate (dates range from 1/7/2017 - 6/10/2017)
AdjName (there are approximately 140 different names returned by the query)
Determinations_StaffId
Determinations_IssueTypeSu
Double (Yes/No field)
Charge (Yes/No field)
ProdReport (Yes/No field)
What I want to know is how do I construct a query that returns the following weekly counts for each name?
Name
WeekEndDate
Doubles (Double = Yes)
Charges (Charge = Yes)
Singles (Double = No and Charge = No)
For business reasons, I am not allowed to post the DB online. I have attached a .pdf file that shows a sample of what is returned by the query.
Thank you very much for your help.
Sincerely,
Errol Isenberg
Government Operations Specialist
Access-Query-Question.pdf
WeekEndDate (dates range from 1/7/2017 - 6/10/2017)
AdjName (there are approximately 140 different names returned by the query)
Determinations_StaffId
Determinations_IssueTypeSu
Double (Yes/No field)
Charge (Yes/No field)
ProdReport (Yes/No field)
What I want to know is how do I construct a query that returns the following weekly counts for each name?
Name
WeekEndDate
Doubles (Double = Yes)
Charges (Charge = Yes)
Singles (Double = No and Charge = No)
For business reasons, I am not allowed to post the DB online. I have attached a .pdf file that shows a sample of what is returned by the query.
Thank you very much for your help.
Sincerely,
Errol Isenberg
Government Operations Specialist
Access-Query-Question.pdf
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Doubles (Double = Yes)
Charges (Charge = Yes)
Singles (Double = No and Charge = No)
Modification to Singles part.
Abs(Sum(Not(Double)+Not(Charge))) As SinglesASKER
Hi, guys, and thank you for your help. I used material from both Pat Hartman's response and Huseyin Kharaman's response to create the SQL statement shown below, which was successful.
SELECT AdjName, WeekEndDate, abs(sum([Double])) as Doubles, abs(sum([Charge])) as Charges, sum(iif([Double]+[Charge] = 0, 1, 0)) as Singles
FROM qryDeterminations
GROUP BY qryDeterminations.AdjName,
Sincerely,
Errol Isenberg
SELECT AdjName, WeekEndDate, abs(sum([Double])) as Doubles, abs(sum([Charge])) as Charges, sum(iif([Double]+[Charge] = 0, 1, 0)) as Singles
FROM qryDeterminations
GROUP BY qryDeterminations.AdjName,
Sincerely,
Errol Isenberg
ASKER
You guys are great. Thanks for your help.
You're welcome.
Jim.