Help on Total Query

[bhofmann]

Using Access 2007...



I have a data table with appointment dates, appointment statuses, patient names, a yes/no field and some other data not currently being used for this query. I am trying to use a query to aggregate data by week or month (I will be doing both eventually, but I am starting with weekly) to give me a numerator and a denominator based on the yes/no field.

The first field in my query normalizes the appointment date to the date on the first day of the week (this is working as expected). The second field is a count of patient names, then I have two non-displayed fields, one for a filter on yes/no, and the other for a filter on appointment status. There are two possible appointment statuses that I want included in the query result, but I want the counts of patient names combined together for both appointment statuses. Currently the counts are separated by appointment status. I've tried two different things for the filter criteria:

"CHECKED OUT" Or "INPATIENT APPOINTMENT"
and
Not "IsNull" And Not Like "*Missing*" And Not "ACTION REQUIRED"

Both of these filter criteria give me the same result.

How can I get the counts of patient names combined together for both appointment statuses?

-Ben

[pbaldy]

What is the SQL of the query? It sounds like the status is in the GROUP BY clause, and you don't want it to be.

[bhofmann]

Here is the SQL:

SELECT MonthName(Month([pact7]![AppointmentDateTime]),True) AS [Month], Count(pact7.PatientName) AS Denominator
FROM pact7
GROUP BY MonthName(Month([pact7]![AppointmentDateTime]),True), pact7.VisitAppointmentStatus
HAVING (((pact7.VisitAppointmentStatus)="CHECKED OUT" Or (pact7.VisitAppointmentStatus)="INPATIENT APPOINTMENT"))
ORDER BY MonthName(Month([pact7]![AppointmentDateTime]),True);

-Ben

[pbaldy]

So I see a status field in the GROUP BY clause like I mentioned. Did you try removing it?

[bhofmann]

You were right. I played with this a little yesterday and this morning. I had to remove the status field from the GROUP BY section, but I also had to add it back to a WHERE section. It appears to be working as desired now. Thanks for the assistance.

SELECT DateAdd("d",(-Weekday([pact7]![AppointmentDateTime])+1),[pact7]![AppointmentDateTime]) AS WeekBeginning, Count(pact7.PatientName) AS Denominator
FROM pact7
WHERE (((pact7.VisitAppointmentStatus)="CHECKED OUT" Or (pact7.VisitAppointmentStatus)="INPATIENT APPOINTMENT"))
GROUP BY DateAdd("d",(-Weekday([pact7]![AppointmentDateTime])+1),[pact7]![AppointmentDateTime])
ORDER BY DateAdd("d",(-Weekday([pact7]![AppointmentDateTime])+1),[pact7]![AppointmentDateTime]);

-Ben

[pbaldy]

Happy to help, and welcome to the site by the way.