Counting records in queries w/parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Eeeeek! I'm frazzled at this point and could use some help.

My data is Clinic, Appt Date and Pymnt Date. I want the user to enter the
month they want to view which would relate to the Appt Date. Then I need to
have a count of all the records for that month (all the appointments). Then I
also need a count of records where the Pymnt Date is the same as the Appt
Date, which I will need to calculate the percentage of these matching records
to the total records for the month.

Apparently, there are issues with using parameters and some expressions (?),
so I am having a difficult time knowing if I'm writing the expressions wrong
or if it's just a glitch. Either way I don't know what to do about it.

I have tried using one query with the parameter then another query based on
the first with Count expressions with no luck even if I clarify the Parameter
with Parameters under the Query menu. I have managed to get a total per month
in a report and a running total of records with the matching dates but then I
don't know how to use the running total in another calculation.

Ack! Could someone please help me find clarity?

Any help is greatly appreciated!

Kristine
 
You can do it with three queries:

SELECT Month([Appt Date]) AS Expr1, Count(Appt Date) AS CountOfappt
FROM YourTable
GROUP BY Month([Appt Date])
HAVING (((Month([Appt Date]))=[WhatMonth]));

SELECT Month([Appt Date]) AS Expr1, Sum(IIf(Month([Pymnt Date])=Month([Appt
Date]),1,0)) AS Payed
FROM Table1
GROUP BY Month([Appt Date])
HAVING (((Month([appt]))=[WhatMonth]))

SELECT Query1.Expr1, Query1.CountOfappt, Query2.Payed, [Payed]/[CountOfappt]
AS Expr2
FROM Query1 INNER JOIN Query2 ON Query1.Expr1 = Query2.Expr1
 
Back
Top