Query to merge various date fields?

[NXMold]

I need to consolidate some data for a calendar report, is it possible to build a union query like this?

Query is used by calendar report, and sub-report (42 occurrences per page, ~6pgs). I am thinking about using a temporary table, but as it stands its quick enough.

Input - Current query, based on three tables, Column Names:
Job#
Meeting1 Date
Meeting2 Date
Meeting3 Date
Meeting4 Date
Delivery Date

Desired output - Query or Table with three columns:
Job#
Event Date (union several date fields)
Event Title (Meeting1, Delivery, etc derived from old field name)

ms-access 2000

 

[dhookom]

You can normalize your data with a union query like below. What happens if you have more than 4 meetings? Do you have to add a field, controls, modify queries, forms, and reports?

SELECT [Job#], "Meeting1" as EventTitle, [Meeting1 Date] as EventDate
FROM [CurrentQuery]
UNION ALL
SELECT [Job#], "Meeting2", [Meeting2 Date]
FROM [CurrentQuery]
UNION ALL
SELECT [Job#], "Meeting3", [Meeting3 Date]
FROM [CurrentQuery]
UNION ALL
SELECT [Job#], "Meeting4", [Meeting4 Date]
FROM [CurrentQuery]
UNION ALL
SELECT [Job#], "Delivery", [Delivery Date]
FROM [CurrentQuery];

Duane
Hook'D on Access
MS Access MVP

 

[NXMold]

Its actually different types of meetings, such as a preliminary design review stored in the engineering table, and a ship date stored in the shop table. I don't actually have "Meeting1" "Meeting2" etc fields.

I had no idea that you could insert data like that into a query, thats exactly what I needed. Thanks for the specific example.

 

[dhookom]

IMHO, it doesn't matter what you name your fields. I generally try to normalize multiple dates into multiple records with a value stored in a field that identifies the date type. Maybe you have a good reason for your multiple tables.

Duane
Hook'D on Access
MS Access MVP

 

[NXMold]

Ah, I see. I'm self-taught so often do things quite oddly (as in, whatever works). I put a LOT of fields in my tables that could be split into sub-records, it just seems counter-intuitive up front.

 

[NXMold]

Oh, now I'm having another problem and I think its due to the complexity of this... and how many times things are repeated.

My calendar report has 42 sub reports (one for each day of the month), each sub-report is based on my new query. I get an error "Cannot open any more databases" when I run my calendar report. The error pops up about a dozen times. I'm thinking I should send all this to a temporary table, would that make sense?

SELECT CMEJobList.CMEJobNumber AS JobNumber, Engineering.InitialPrelim AS EventDate, "1st Prelim" as EventTitle
FROM (CMEJobList INNER JOIN Engineering ON CMEJobList.CMEJobNumber = Engineering.CMEJobNumber)
WHERE (((CMEJobList.Active)=True) and ((Engineering.InitialPrelim)Is Not Null));

UNION ALL
SELECT CMEJobList.CMEJobNumber, Engineering.NextPrelim, "Next Prelim"
FROM (CMEJobList INNER JOIN Engineering ON CMEJobList.CMEJobNumber = Engineering.CMEJobNumber) 
WHERE (((CMEJobList.Active)=True) and ((Engineering.NextPrelim)Is Not Null));

UNION ALL
SELECT CMEJobList.CMEJobNumber, Engineering.KickOffMfg, "Kick Off"
FROM (CMEJobList INNER JOIN Engineering ON CMEJobList.CMEJobNumber = Engineering.CMEJobNumber) 
WHERE (((CMEJobList.Active)=True) and ((Engineering.KickOffMfg)Is Not Null));

UNION ALL
SELECT CMEJobList.CMEJobNumber, Engineering.MfgMtg, "Mfg. Mtg."
FROM (CMEJobList INNER JOIN Engineering ON CMEJobList.CMEJobNumber = Engineering.CMEJobNumber) 
WHERE (((CMEJobList.Active)=True) and ((Engineering.MfgMtg)Is Not Null));

UNION ALL
SELECT CMEJobList.CMEJobNumber, Engineering.PostBuild, "Post Build"
FROM (CMEJobList INNER JOIN Engineering ON CMEJobList.CMEJobNumber = Engineering.CMEJobNumber) 
WHERE (((CMEJobList.Active)=True) and ((Engineering.PostBuild)Is Not Null));

UNION ALL
SELECT CMEJobList.CMEJobNumber, Shop.DeliveryDate, "Delivery"
FROM (CMEJobList INNER JOIN Shop ON CMEJobList.CMEJobNumber = Shop.CMEJobNumber)
WHERE (((CMEJobList.Active)=True) and ((Shop.DeliveryDate)Is Not Null));

 

[PHV]

My calendar report has 42 sub reports (one for each day of the month)
Which calendar has month with 42 days ?

 

[dhookom]

Take a look at the calendar report sample found at

http://www.access.hookom.net/Samples.htm.

I have not gotten error messages from this report.

Duane
Hook'D on Access
MS Access MVP

 

[NXMold]

I have used this report several times, its solid. I think that page is where I got the idea. But I have never used it with a complex data source such as this!

May 2009 has 42 days!

 

[lespaul]

I see 42 potential boxes in this month calendar, but there are only 31 days filled in!




Leslie

Have you met Hardy Heron?

 

[NXMold]

It has 42 sub-reports, for 42 possible days, the visible property does not remove the extras from existence nor do I programmaticaly move 31 boxes into 42 spaces. Therefore, all months have 42 days.

There are several items per day in my report, all items for all days appear on the sub-report when its opened by itself (4-pages long). That 4pg long subreport is filtered to just a few items by nature of being a sub-report (linked fields).

This is a lot of data! When I use this report based on a simple query it works fine, but based on the above union query it gives me the error Cannot open any more databases and fails to fill-in the last two months.

 

[dhookom]

Use my solution. An alternative is to create a table based on the union query. Delete all records and then append from the union.

Duane
Hook'D on Access
MS Access MVP