Dates & Events Report

[stlryan82]

Hi everyone. I was wondering if you all can tell me if there's a way to accomplish what I'm trying to do:

I have a database that contains several projects. Each project has different events associated with it. For the sake of simplicity, let's say each project has the following three events. The table would like this:

Project Report Due Date Project End Date __Closeout Date
Project 1 05/31/11 11/31/11 01/31/12
Project 2 01/15/11 07/15/11 09/15/11
Project 3 11/10/10 05/10/11 07/10/11


The dates for each of the above events are different for each project. What I want to do is to create a report that orders all of the dates (regardless of event) so I that I know the events that are soon to happen. So ultimately, I want to end up with a report that looks like this:

Date Project Event_______________
11/10/10 Project 3 Report Due Date
01/15/11 Project 2 Report Due Date
05/10/11 Project 3 Project End Date
05/31/11 Project 1 Report Due Date
07/10/11 Project 3 Closeout Date

Is there a way to accomplish this? Please keep in my that my knowledge of VB is extremely limited.

Thank you!

-Ryan

 

[spikepl]

1. Make query using the query designer, just listing the projects and dates and events picked up from Report due. Add a colum named EventType:"Report due"
2. Make a new query, this time using the event dates picked up from Project End Date. Add this time a column EventType:"Project End Date"
3. Repeat for Closeout Date.

when done, you have three queries.

Now make a UNION query. You can only do that in SQL view in the designer.

• From each of the queries, paste the SQL in the SQL view of the union query, each on new line, but keep the end ";" only for the last one
• After each query, apart from the first one, insert UNION ALL beefore the SELECT keyword.
• Save.

Now you have a query that will give you what you want.

http://office.microsoft.com/en-us/a...eries-by-using-a-union-query-HA010206109.aspx

 

[stlryan82]

spikepl,

Thank you so much! This is exactly what I was looking for!

Also, by adding the following: "Event Name" as EVENT to the end of the SQL statements for each select query, I was able to create a new column in the Union Query that identifies the event associated with each date.

Thanks again.

-Ryan