Union Query

[pdehner]

I have a table - Reportsdue
with multiple fields such as reportname, fiscialyear, towhom,
also with
reportduedate1
reportduedate2
reportduedate3
reportduedate4
I need to create a query/report that allows the user to input a month
requested
Example: Reports due in the month of May (5)
so I began with a union query that I thought would rename each of the above
4 fields to duedate
then I tried to use that query in another query to get the rest of the
fields in the table.
I received a blank record when I ran the union query and it will not allow
me to query this query to get the information I want.

What am I doing wrong.

Any suggestions on where to look or help with code is appreicated!


SELECT [ReportDueDate1] as [DUEDATE]
FROM [Reportsduecomplete]WHERE [ReportDueDate1]
UNION
SELECT [ReportDueDate2] as [DUEDATE]
FROM [Reportsduecomplete]
UNION
SELECT [ReportDueDate3] as [DUEDATE]
FROM [Reportsduecomplete]
UNION SELECT [ReportDueDate4] as [DUEDATE]
FROM [Reportsduecomplete];

 

[Dale Fye]

Is there any significance to the various due date #'s?

How you do this also depends on how your user will be selecting the month.
Will it be on a form (a combo box maybe) or will it just be a parameter query?

I usually do something like:

SELECT [ReportName], 1 as Sequence, [DueDate1] as DueDate
FROM [ReportsDueComplete]
WHERE NZ(Month([DueDate1]),0) = [What month #]
UNION ALL
SELECT [ReportName], 2 as Sequence, [DueDate2] as DueDate
FROM [ReportsDueComplete]
WHERE NZ(Month([DueDate2]),0) = [What month #]
UNION ALL
SELECT [ReportName], 3 as Sequence, [DueDate3] as DueDate
FROM [ReportsDueComplete]
WHERE NZ(Month([DueDate3]),0) = [What month #]

You could do it like:

SELECT [ReportName], 1 as Sequence, [DueDate1] as DueDate
FROM [ReportsDueComplete]
WHERE [DueDate1] IS NOT NULL
UNION ALL
SELECT [ReportName], 2 as Sequence, [DueDate2] as DueDate
FROM [ReportsDueComplete]
WHERE [DueDate2] IS NOT NULL
UNION ALL
SELECT [ReportName], 3 as Sequence, [DueDate3] as DueDate
FROM [ReportsDueComplete]
WHERE [DueDate3] IS NOT NULL

Then save this query and query it based on the month due.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[pdehner]

I used your first suggestion and it works like a charm!
Thanks so much!

Is there any significance to the various due date #'s?

How you do this also depends on how your user will be selecting the month.
Will it be on a form (a combo box maybe) or will it just be a parameter query?

I usually do something like:

SELECT [ReportName], 1 as Sequence, [DueDate1] as DueDate
FROM [ReportsDueComplete]
WHERE NZ(Month([DueDate1]),0) = [What month #]
UNION ALL
SELECT [ReportName], 2 as Sequence, [DueDate2] as DueDate
FROM [ReportsDueComplete]
WHERE NZ(Month([DueDate2]),0) = [What month #]
UNION ALL
SELECT [ReportName], 3 as Sequence, [DueDate3] as DueDate
FROM [ReportsDueComplete]
WHERE NZ(Month([DueDate3]),0) = [What month #]

You could do it like:

SELECT [ReportName], 1 as Sequence, [DueDate1] as DueDate
FROM [ReportsDueComplete]
WHERE [DueDate1] IS NOT NULL
UNION ALL
SELECT [ReportName], 2 as Sequence, [DueDate2] as DueDate
FROM [ReportsDueComplete]
WHERE [DueDate2] IS NOT NULL
UNION ALL
SELECT [ReportName], 3 as Sequence, [DueDate3] as DueDate
FROM [ReportsDueComplete]
WHERE [DueDate3] IS NOT NULL

Then save this query and query it based on the month due.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

I have a table - Reportsdue
with multiple fields such as reportname, fiscialyear, towhom,
also with
reportduedate1
reportduedate2
reportduedate3
reportduedate4
I need to create a query/report that allows the user to input a month
requested
Example: Reports due in the month of May (5)
so I began with a union query that I thought would rename each of the above
4 fields to duedate
then I tried to use that query in another query to get the rest of the
fields in the table.
I received a blank record when I ran the union query and it will not allow
me to query this query to get the information I want.

What am I doing wrong.

Any suggestions on where to look or help with code is appreicated!


SELECT [ReportDueDate1] as [DUEDATE]
FROM [Reportsduecomplete]WHERE [ReportDueDate1]
UNION
SELECT [ReportDueDate2] as [DUEDATE]
FROM [Reportsduecomplete]
UNION
SELECT [ReportDueDate3] as [DUEDATE]
FROM [Reportsduecomplete]
UNION SELECT [ReportDueDate4] as [DUEDATE]
FROM [Reportsduecomplete];