Add Year Parameter based on Union to CrossTab Query

[topwaya]

I'm trying to add a YEAR parameter to the following working query:

TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT [qryUnionProgramMonths].M, qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM qryNONMembers LEFT JOIN qryUnionProgramMonths ON qryNONMembers.PID=[qryUnionProgramMonths].pid
GROUP BY [qryUnionProgramMonths].M, qryNONMembers.Mtype
ORDER BY [qryUnionProgramMonths].M
PIVOT qryNONMembers.LocW;

I created a union query for the program years and I'm trying to use it here so it will display only the months for the inputed year. But when I run it, I don't get any data. Any idea what's wrong?

PARAMETERS [Year] Text ( 255 );
TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT qryUnionProgramMonths.M, qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM (qryNONMembers LEFT JOIN qryUnionProgramMonths ON qryNONMembers.PID = qryUnionProgramMonths.pid) INNER JOIN qryUnionProgramYearsTRY ON qryNONMembers.PID = qryUnionProgramYearsTRY.pid
WHERE (((Year([qryUnionProgramYearsTRY.YR]))=[Year]))
GROUP BY qryUnionProgramMonths.M, qryNONMembers.Mtype
ORDER BY qryUnionProgramMonths.M
PIVOT qryNONMembers.LocW;

What the query does - is it figures out what nonmembers have participated in any program, ordered by month (trying to get a YEAR parameter to work).

Thanks for any help!!

 

[PHV]

PARAMETERS [Year] Integer;

What are the data type and the values returned by qryUnionProgramYearsTRY.YR ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Thank you for your quick response, but it is still giving no data.

qryUnionProgramYearsTRY sql is:

SELECT pid, Year(AssessD) as YR from tblFCAssess
union
Select pid,  Year(GaspD) as YR from tblGASP
union
Select pid, Year(LnlD) as YR from tblLNL
union
Select pid, Year(MDGSD) as YR from tblMDG
union
Select pid, Year(NLD) as YR from tblNL
union
select pid, Year(PedSD) as YR from tblPed
union
select pid, Year(ScrD) as YR from tblScr
union
select pid, Year(TobCesD) as YR from tblTobCes
union
select pid, Year(WalkD) as YR from tblWalk
union
select pid, Year(WBD) as YR from tblWB
UNION Select pid, Year(ConsD) as YR from tblConsult;

It returns two columns, one with the PID and one with the year. Meaning, that person participated in a program in that year.

However, am I messing up?
Does the union month know what union year it is in?

For example, I want to know how many people participated in any program in January of 2005, or in any month and year.

Clear as mud? Thanks for any help, I'm becoming brain dead

 

[PHV]

So, replace this:
WHERE (((Year([qryUnionProgramYearsTRY.YR]))=[Year]))
By this:
WHERE qryUnionProgramYearsTRY.YR=[Year]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Wow!!!
I think it works!!!! I got data - I just need to confirm it is correct - thank you so much, I never would have figured it out!!!