Counting records in queries w/parameters

[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

 

[Guest]

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