How can I subquery using different parameters?

[psPaul]

In my form, I set 3 monthly parameters --
M3 = "Mar 2004" ' 3 months ago
M2 = "Apr 2004" ' 2 months ago
M1 = "May 2004" ' 1 month ago

and then call report
rptGetEachPerson which has source of
qryGetEachPerson which has sql

Select tblMember.ID,
tblMember.Name,
' here I want to call a common query 3 times
qryGetHours(M3).General as G3, 'what can go here
qryGetHours(M2).General as G2, 'to send 3 params
qryGetHours(M1).General as G1 'to qryGetHours?
From tblMember
Left Join
qryGetHours on tblMember.ID = qryGetHours.ID
Group by tblMember.ID;

and where qryGetHours has sql
Select tblHours.ID,
sum(IIF(HoursType="General"),Hours,0) as General
From tblHours
Where (((Format$([Date],'mmm yyyy')) = [&Mx]))
Group by tblHours.ID;

---or do I need to create 3 sub-queries (duplicated code)---
qryGetHoursM3
qryGetHoursM2
qryGetHoursM1
and hardcode the Where parameter names as M3, M2, M1?
????

tkx, Paul

 

[Dale Fye]

Paul,

1. Pirst things first. I would change the name of the [Date] field in
tblHours to something else ([DateWorked], [DateVolunteered], whatever).
Date is a Reserved word in Access and should not be used as the name of a
field.

2. I would rewrite qryGetHours to something like.

SELECT ID
, Format([DateWorked], "yyyy mm") asMonthWorked,
, [HoursWorked]
FROM tblHours
WHERE [DateWorked] >= Dateserial(Year(Date()), Month(Date())-3, 1))
AND [DateWorked] < DateSerial(Year(Date()), Month(Date()), 0)

This should convert the dates to months and give you only records for the
three months preceeding the current month. The first DateSerial() function
should return that first day of the month 3 months ago, the second will
return that last day of the month preceeding the current month.

3. Next, create a query

SELECT M.ID, M.Name, H.MonthWorked, SUM(H.HoursWorked) as Cum Hours
FROM tblMembers M
INNER JOIN tblHours H
ON M.ID = H.ID
GROUP BY M.ID, M.Name, H.MonthWorked

This should give you three records per person, one for each of the last
three months.

4. Now, convert this from a SELECT query to a Crosstab query. Include the
ID and Name fields as the Row headings, make the MonthWorked column the
Column heading, and the CumHours column the Value setting.

NOTE: I changed the format of the MonthWorked column to "yyyy mm" because
Access automatically alphabetizes the column headers. Because of this,
Access would automatically put "01 2005" before "12 2004". By chaning it to
year, then month, this will not be a problem.

HTH
Dale