Between Date Parameters using Union Query in Crosstab Query 1

[topwaya]

I want to be able to enter a Start Date and End Date in a form for a report based on this query (I know how to make the form, just can't figure out the code in the query).

Currently the query has a YEAR parameter and then shows the data by month of that year.

How do I change the query below to be able to show data for one or more months based on a Start & End Date?
qryUnionProgramDates.MN is the MONTH
qryUnionProgramDates.YR is the YEAR

PARAMETERS [Year] Long;
TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT qryUnionProgramDates.MN, qryUnionProgramDates.YR, qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM qryNONMembers LEFT JOIN qryUnionProgramDates ON qryNONMembers.PID = 
qryUnionProgramDates.pid
WHERE (((qryUnionProgramDates.YR)=[Year]))
GROUP BY qryUnionProgramDates.MN, qryUnionProgramDates.YR, qryNONMembers.Mtype
ORDER BY qryUnionProgramDates.MN, qryUnionProgramDates.YR
PIVOT qryNONMembers.LocW;

The Union query that it refers to looks like this:

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

Thanks for any help!

 

[dhookom]

Try:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT qryUnionProgramDates.MN, qryUnionProgramDates.YR, qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM qryNONMembers LEFT JOIN qryUnionProgramDates ON qryNONMembers.PID = 
qryUnionProgramDates.pid
WHERE DateSerial([YR],[MN],1) BETWEEN [Enter Start Date] AND [Enter End Date]
GROUP BY qryUnionProgramDates.MN, qryUnionProgramDates.YR, qryNONMembers.Mtype
ORDER BY qryUnionProgramDates.MN, qryUnionProgramDates.YR
PIVOT qryNONMembers.LocW;

Also, you don't need to use the "as MN" and "as Yr" beyond your first "SELECT...". To improve performance, you might want to replace " UNION SELECT " with " UNION ALL SELECT "

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[topwaya]

Thank you very much. I tried it and got the following message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assiging parts of the expression to variables."

Regarding the Union Query - there are other fields in all of the tables that are being unioned, so I don't think I can use Union All Select. Is there a way to do that? Or am I misunderstanding?

Thanks so much for your help!

 

[dhookom]

Could you paste your current SQL that errors?

UNION ALL has nothing to do with the fields selected for the union. It only keeps the union query from attempting to remove duplicates. A union without the UNION ALL attempts to group by the selected fields and might take much longer to process.

I'm not sure how you did it but you had a space in your union query where there shouldn't have been one:

SELECT pid, Month (AssessD) as MN,

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[topwaya]

When I run the query and enter the start and end date it works for second and then box comes up with that message that I can only X out of - no debugging offered, however it has a "help" button and when I click that it says:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)
Possible causes:

You typed an expression that has invalid syntax. For example, an operand or operator may be missing, you may have typed an invalid character or comma, or you may have typed text without enclosing it with quotation marks ( " ). Check the expression to make sure you typed it correctly.
You typed an expression that is too complex. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning some parts to variables."

I'm not sure about the Union All thing. Here is my new code, If I use the previous code I get 44 records returned. If I use this code I get 88 records returned. I guess I'll have to try to figure out which one is correct.

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

I ran that union query, then tried the other query again, but got the same error box.
Thanks so much for helping!!

 

[dhookom]

The UNION ALL will return duplicates of Month and Year values.

I think your issue might be date fields that are null. Try create your union queries with

WHERE AssessD Is Not Null
WHERE GaspD Is Not Null
...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[topwaya]

I put in the WHERE GaspD Is Not Null, etc. so that my code is now:

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

I'm getting 88 records in the union query, there should be 44 (I confirmed 44).

That means that the original union query supposedly worked, should I just use that again?

Regarding the parameter query, I am still getting the same error box.

Thank you for any help!!

 

[dhookom]

As I stated, the original union query removed duplicates. The UNION ALL union query allows duplicates.

If you aren't concerned about the month and year anymore, change your union query to return one date per month

SELECT pid, DateAdd("d",-Day(AssessD)+1,AssessD) as FirstOfMth
FROM tblFCAssess
WHERE AssessD Is Not Null
UNION
SELECT pid,  DateAdd("d",-Day(GaspD)+1,GaspD) 
FROM tblGASP
WHERE GaspD Is Not Null
UNION
--etc--

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT qryUnionProgramDates.FirstOfMth, qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM qryNONMembers LEFT JOIN qryUnionProgramDates ON qryNONMembers.PID = 
qryUnionProgramDates.pid
WHERE FirstOfMth BETWEEN [Enter Start Date] AND [Enter End Date]
GROUP BY qryUnionProgramDates.FirstOfMth, qryNONMembers.Mtype
ORDER BY qryUnionProgramDates.FirstOfMth
PIVOT qryNONMembers.LocW;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[topwaya]

I'm sorry,
I didn't mean to imply that I don't care about the month and year anymore.

I need to find out how many nonmembers participated in at least one program (per month) between the start date and end date.

If I use the following union query to figure out how many people participated in at least one program for each month in each year:

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

and then I use the following query to figure out how many nonmembers are in the union query for a start and end date, but it gives me that error message.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT qryUnionProgramDates.MN, qryUnionProgramDates.YR, qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM qryNONMembers LEFT JOIN qryUnionProgramDates ON qryNONMembers.PID = 
qryUnionProgramDates.pid
WHERE DateSerial([YR],[MN],1) BETWEEN [Enter Start Date] AND [Enter End Date]
GROUP BY qryUnionProgramDates.MN, qryUnionProgramDates.YR, qryNONMembers.Mtype
ORDER BY qryUnionProgramDates.MN, qryUnionProgramDates.YR
PIVOT qryNONMembers.LocW;

It seems the WHERE statement is the only thing that's different (except for the parameters) from the original query where I was just (incorrectly) looking for the year.

Any ideas?
Thanks for any help!

 

[topwaya]

After researching "error 3071" it seems that it could be a datatype problem. Anyone know how to make sure the datatypes are correct in a query?

 

[topwaya]

OK, I decided to change my union query to the whole date:

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

And my crosstab query to:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM qryNONMembers LEFT JOIN qryUnionProgramDates ON qryNONMembers.PID = qryUnionProgramDates.pid
WHERE (((qryUnionProgramDates.PDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY qryUnionProgramDates.PDate, qryNONMembers.Mtype
ORDER BY qryUnionProgramDates.PDate
PIVOT qryNONMembers.LocW;

It appears to work, but I don't think the results are correct, so I need to do some research.

 

[dhookom]

I thought you could change your union query to the suggestion in my previous reply. It would create one record per month per PID. Rather than month and year fields, there would be a single field for the first of the month.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

Your union query now creates a record for each date rather than each month. That's why I suggested you use a calculation like:
DateAdd("d",-Day(AssessD)+1,AssessD)
This will return the first day of the month.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[topwaya]

You are a genius! Sorry I didn't try it before, I was brain dead. I needed to eat some food.

IT WORKS!!! YIPPEE!!!!!