Joining four queries

[dash76]

I have four queries that compile the following info:

Querie 1 Querie 2 Querie 3 Querie 4
Date Date Date Date
Shift Shift Shift Shift
Machine Machine Machine Machine
Operator Downtime Planned Down Setup Time
Production Hrs

The info is split up into four groups because the data is collected from different sources and saves time entering it separately instead of compiling it manually before entry.

What I need to do is compile these four queries so I get the resulting info for a report:

Date
Shift
Machine
Production Hours
Downtime
Planned Downtime
Setup Time

I will need to group this by machine and subgrouped by date, but some of the data areas will be blank. For instance, if I have time listed as downtime or planned down, there might not be a number for production for that date. So a typical day might have 5 hours under production, three hours under down time and nothing under planned down or setup. I haven't figured out a way to pull in dissimilar information like this into a single table. A typical query will either give me every known multiple of the data or nothing at all.

 

[dynamictiger]

This is a join thing. I think you have a table machine, this is the master table for the query. You would then select the option to return all from machine and only records that match from query1. This would give you machine shift and production hours. Then add the next query and so on, until you have what you need.

 

[scriverb]

I think what you are looking for is a UNION QUERY. Below is an untested UNION query SQL that can get you started. I have not been able to test this for you but I can help as you post the results with your tables and queries. This query groups by Machine, Date, and Shift. You never mentioned the Shift field so I included it. If it is not necessary then just remove all reference to it in the entire query.

Select A.Machine, A.Date, A.Shift, Max(A.Operator) as Operator, Max(A.ProductionHours) as ProductionHours, Null as Downtime, Null as PlannedDown, Null as SetupTime
FROM [Querie 1] as A
Group By A.Machine, A.Date, A.Shift
Order By A.Machine, A.Date, A.Shift
UNION
Select B.Machine, B.Date, B.Shift, Null as Operator, Null as ProductionHours, Max(B.Downtime) as Downtime, Null as PlannedDown, Null as SetupTime
FROM [Querie 2] as B
UNION
Select C.Machine, C.Date, C.Shift, Null as Operator, Null as ProductionHours, Null as Downtime, Max(C.PlannedDown) as PlannedDown, Null as SetupTime
FROM [Querie 3] as C
UNION
Select D.Machine, D.Date, D.Shift, Null as Operator, Null as ProductionHours, Null as Downtime, Null as PlannedDown, Max(D.SetupTime) as SetupTime
FROM [Querie 4] as D

Let me know how this looks to you and if there are any problems just post back and I can help resolve them.



Bob Scriver