UNION Query - 1st attempt

[tikkat3]

To try to get over some of my previous problems, I have tried using a Union Query - I understand this might help me get the results I need. Below is my SQL, which is meant to combine the results of the 10 individual queries. Have I misunderstood something, or not done something right? I am getting error messages.

The data is drawn from 2 tables - PROJECTS and PERSONNEL.


SELECT Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="1"))
GROUP BY Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date
HAVING (((Personnel.[1st Fix Men Plan])=True) AND ((Personnel.[1st Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))

UNION

SELECT Personnel.[C/Q Number], Personnel.[2nd Fix Men Plan], Personnel.[2nd Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="2"))
GROUP BY Personnel.[C/Q Number], Personnel.[2nd Fix Men Plan], Personnel.[2nd Fix Men ACT], Projects.Date
HAVING (((Personnel.[2nd Fix Men Plan])=True) AND ((Personnel.[2nd Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))

UNION

SELECT Personnel.[C/Q Number], Personnel.[3rd Fix Men Plan], Personnel.[3rd Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="3"))
GROUP BY Personnel.[C/Q Number], Personnel.[3rd Fix Men Plan], Personnel.[3rd Fix Men ACT], Projects.Date
HAVING (((Personnel.[3rd Fix Men Plan])=True) AND ((Personnel.[3rd Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))

UNION

SELECT Personnel.[C/Q Number], Personnel.[4th Fix Men Plan], Personnel.[4th Fix Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[4th Fix Men Plan], Personnel.[4th Fix Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[4th Fix Men Plan])=True) AND ((Personnel.[4th Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="4"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[5th Fix Men Plan], Personnel.[5th Fix Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[5th Fix Men Plan], Personnel.[5th Fix Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[5th Fix Men Plan])=True) AND ((Personnel.[5th Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="5"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[Snag 1 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 1 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 1 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[Snag 2 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 2 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 2 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[Snag 3 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 3 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 3 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[Snag 4 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 4 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 4 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))

UNION SELECT Personnel.[C/Q Number], Personnel.[Snag 5 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 5 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 5 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))
ORDER BY Projects.Date

 

[Guus2005]

What are the error messages?
could you post your database (with dummy data)?
Compact and zip please.

Remember that a union query expects all underlying queries to have the same number of output fields. Also the types must be the same.

I expect that 10 subqueries on a single table could be simplified...
Oops there are two tables involved..

 

[Guus2005]

Yes, they could be "simplified" but it doesn't make it better to read though.
Still curious what the error message was...

 

[tikkat3]

Attached is an edited version of the database. Some fields, which are not relevant to this report/query have been deleted.

The union query I had been trying to get set up should consist of the RSub... queries.

My aim at the end of the day is to get to the position of being able to create a grand total in the right hand column of the report footer in report Manning Difference Monthly. This should add up the totals of the fields directly to its left in the sub reports (some subreports are missing in the attached, but all are based on the same basis).

Does this help?

 

[stopher]

With reference to the previous contributor’s comment, there are some obvious problems in your query. For instance you have Projects.Fix as the last field in most of the section but not in the first three (essentially the first three sections have only four fields not 5).

You have two fields between Personnel.[C/Q Number] and Projects.Date in some sections and three fields in other sections.

That’s before you even look at whether the fields are of the same type. As the previous contributor says, you have to match number of fields and field types. And if you want the data to make sense, don’t union a field for say PersonsName with say ProductDescription (even though they may be both strings)

The real issue here is one of normalisation. [1st Fix Men Plan], [2nd Fix Men Plan] etc should be in a separate table not in separate fields. Then the query will be just a few lines.

e.g.

C/Q Number___FixNo__Plan___Act
C2657__________1_____3______3
C2657__________2_____5______5
C2657__________3_____3______0
C3661___etc

I don’t much about your data so this might not be quite right. But hopefully you get the idea.

Chris