UNION SQL

C

Craig

hi,
This query is giving me some grief, the first 2 sections are working ok, but
once i use the last section the data gets a bit messed up, (Data in wrong
columns).
Its probaly just a 0 missing somewhere but i cant see the wood for the trees!!
Any help would be appreciated.

CR

SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode,0, 0 As
FinBFWas, QryRawProcessSumed.SumOfRawWasteCost, ProcessQuality.Labour,
ProcessQuality.VarOverHead, ProcessQuality.FixedOverHead,
ProcessQuality.ProcessWaste, [SumOfRawWasteCost]+[TotNormCosts] AS
TotProcessCost,0 As StartTileCost, [VarOverHead]+[Labour]+[FixedOverHead] AS
TotNormCosts, "0" AS BringFor
FROM ProcessQuality INNER JOIN QryRawProcessSumed ON
(ProcessQuality.QualityCode = QryRawProcessSumed.QualityCode) AND
(ProcessQuality.ProcessID = QryRawProcessSumed.ProcessID)
WHERE (((ProcessQuality.ProcessID)=6) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

UNION

SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode,FinStart.FINBF,
[FinBF]*1*(1+[ProcessWaste]/100) AS FINBFWas,
QryRawProcessSumed.SumOfRawWasteCost, ProcessQuality.Labour,
ProcessQuality.VarOverHead, ProcessQuality.FixedOverHead,
ProcessQuality.ProcessWaste, [SumOfRawWasteCost]+[TotNormCosts]+[FinBFWas] AS
TotProcessCost, [TotProcessCost] AS StartCostTile,
[Labour]+[VarOverHead]+[FixedOverHead] AS TotNormCosts, FinStart.FINBF AS
BringFor
FROM FinStart, QryRawProcessSumed INNER JOIN ProcessQuality ON
(QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID) AND
(QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)
WHERE (((ProcessQuality.ProcessID)=3) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

UNION

SELECT ProcessQuality.ProcessID,
ProcessQuality.QualityCode,TilingStart.BFFin,QryRawProcessSumed.SumOfRawWasteCost,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
[SumOfRawWasteCost]+[TotNormCosts] AS TotProcessCostTile,
[BringFor]*1*(1+[ProcessWaste]/100) AS [Total Stock Cost], [Total Stock
Cost]+[TotProcessCostTile] AS Final, [Labour]+[VarOverHead]+[FixedOverHead]
AS TotNormCosts, TilingStart.BFFIN AS BringFor
FROM TilingStart, ProcessQuality INNER JOIN QryRawProcessSumed ON
(ProcessQuality.QualityCode = QryRawProcessSumed.QualityCode) AND
(ProcessQuality.ProcessID = QryRawProcessSumed.ProcessID)
WHERE (((ProcessQuality.ProcessID)=4) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));
 
M

Matthias Klaey

Craig said:
hi,
This query is giving me some grief, the first 2 sections are working ok, but
once i use the last section the data gets a bit messed up, (Data in wrong
columns).
Its probaly just a 0 missing somewhere but i cant see the wood for the trees!!
Any help would be appreciated.

CR
Click to expand...
[...]

Copy the SQL code into a text editor such as notepad and properly line
up field by field and you will find the error.

HTH
Matthias Kläy
 
A

Andy Hull

Hi Craig

There are a number of inconsistencies. You can get an idea of where to
start by listing the columns for each query next to each other as below.
Where the names differ but mean the same thing it doesn't really matter but
it is clearer if you make the names consistent.

The 3rd query seems to be missing FinBFWas and the 3 columns after
ProcessWaste need to be checked - inconsistent names and an extra column.

Query1 Query2 Query3

ProcessID ProcessID
ProcessID
QualityCode QualityCode
QualityCode
0 FinBF {*****}
BFFin {*****}
0 as FinBFWas FinBFWas {*****
MISSING *****}
SumOfRawWasteCost SumOfRawWasteCost SumOfRawWasteCost
Labour Labour
Labour
VarOverhead VarOverhead VarOverhead
FixedOverhead FixedOverhead FixedOverhead
ProcessWaste ProcessWaste ProcessWaste
TotProcessCost TotProcessCost
TotProcessCostTile {*****}
0 as StartTileCost StartCostTile {*****} [Total Stock
Cost] {*****}
TotNormCosts TotNormCosts Final {*****}
"0" as BringFor BringFor
TotNormCosts {*****}

BringFor


If the above table wraps badly try copying and pasting into notepad!

Hope this helps

Andy Hull


Craig said:
hi,
This query is giving me some grief, the first 2 sections are working ok, but
once i use the last section the data gets a bit messed up, (Data in wrong
columns).
Its probaly just a 0 missing somewhere but i cant see the wood for the trees!!
Any help would be appreciated.

CR

SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode,0, 0 As
FinBFWas, QryRawProcessSumed.SumOfRawWasteCost, ProcessQuality.Labour,
ProcessQuality.VarOverHead, ProcessQuality.FixedOverHead,
ProcessQuality.ProcessWaste, [SumOfRawWasteCost]+[TotNormCosts] AS
TotProcessCost,0 As StartTileCost, [VarOverHead]+[Labour]+[FixedOverHead] AS
TotNormCosts, "0" AS BringFor
FROM ProcessQuality INNER JOIN QryRawProcessSumed ON
(ProcessQuality.QualityCode = QryRawProcessSumed.QualityCode) AND
(ProcessQuality.ProcessID = QryRawProcessSumed.ProcessID)
WHERE (((ProcessQuality.ProcessID)=6) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

UNION

SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode,FinStart.FINBF,
[FinBF]*1*(1+[ProcessWaste]/100) AS FINBFWas,
QryRawProcessSumed.SumOfRawWasteCost, ProcessQuality.Labour,
ProcessQuality.VarOverHead, ProcessQuality.FixedOverHead,
ProcessQuality.ProcessWaste, [SumOfRawWasteCost]+[TotNormCosts]+[FinBFWas] AS
TotProcessCost, [TotProcessCost] AS StartCostTile,
[Labour]+[VarOverHead]+[FixedOverHead] AS TotNormCosts, FinStart.FINBF AS
BringFor
FROM FinStart, QryRawProcessSumed INNER JOIN ProcessQuality ON
(QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID) AND
(QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)
WHERE (((ProcessQuality.ProcessID)=3) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

UNION

SELECT ProcessQuality.ProcessID,
ProcessQuality.QualityCode,TilingStart.BFFin,QryRawProcessSumed.SumOfRawWasteCost,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
[SumOfRawWasteCost]+[TotNormCosts] AS TotProcessCostTile,
[BringFor]*1*(1+[ProcessWaste]/100) AS [Total Stock Cost], [Total Stock
Cost]+[TotProcessCostTile] AS Final, [Labour]+[VarOverHead]+[FixedOverHead]
AS TotNormCosts, TilingStart.BFFIN AS BringFor
FROM TilingStart, ProcessQuality INNER JOIN QryRawProcessSumed ON
(ProcessQuality.QualityCode = QryRawProcessSumed.QualityCode) AND
(ProcessQuality.ProcessID = QryRawProcessSumed.ProcessID)
WHERE (((ProcessQuality.ProcessID)=4) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));
Click to expand...
 
B

BruceM

A few things I noticed:

In the first line of the first query you have:
SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode,0, 0 As FinBFWas

In the other queries the solitary 0 is replaced by FINBF, so I wonder if you
need to alias FINBF and apply the 0 to that in the first query:
SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode, 0 As
FINBF_Alias, 0 As FinBFWas

In the second query you are performing a math function that includes FINBF
(I don't see the point to multiplying by 1). Later you alias FINBF as
BringFor. However, in the first query you have a text 0 (in quotes) as
BringFor. I'm not sure what that is going to do.

BFFin (FINBF) are not spelled consistently, as has been observed.

There is no FINBFWas alias in the third query.

These are my observations, but I cannot attest to their validity, nor can I
be sure I noticed everything there is to notice.

I would try each of the SELECT queries on its own, and compare the results
side by side to be sure you are getting the same fields in each one, and
that the calculations are producing the expected results.


Craig said:
hi,
This query is giving me some grief, the first 2 sections are working ok,
but
once i use the last section the data gets a bit messed up, (Data in wrong
columns).
Its probaly just a 0 missing somewhere but i cant see the wood for the
trees!!
Any help would be appreciated.

CR

SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode,0, 0 As
FinBFWas, QryRawProcessSumed.SumOfRawWasteCost, ProcessQuality.Labour,
ProcessQuality.VarOverHead, ProcessQuality.FixedOverHead,
ProcessQuality.ProcessWaste, [SumOfRawWasteCost]+[TotNormCosts] AS
TotProcessCost,0 As StartTileCost, [VarOverHead]+[Labour]+[FixedOverHead]
AS
TotNormCosts, "0" AS BringFor
FROM ProcessQuality INNER JOIN QryRawProcessSumed ON
(ProcessQuality.QualityCode = QryRawProcessSumed.QualityCode) AND
(ProcessQuality.ProcessID = QryRawProcessSumed.ProcessID)
WHERE (((ProcessQuality.ProcessID)=6) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

UNION

SELECT ProcessQuality.ProcessID,
ProcessQuality.QualityCode,FinStart.FINBF,
[FinBF]*1*(1+[ProcessWaste]/100) AS FINBFWas,
QryRawProcessSumed.SumOfRawWasteCost, ProcessQuality.Labour,
ProcessQuality.VarOverHead, ProcessQuality.FixedOverHead,
ProcessQuality.ProcessWaste, [SumOfRawWasteCost]+[TotNormCosts]+[FinBFWas]
AS
TotProcessCost, [TotProcessCost] AS StartCostTile,
[Labour]+[VarOverHead]+[FixedOverHead] AS TotNormCosts, FinStart.FINBF AS
BringFor
FROM FinStart, QryRawProcessSumed INNER JOIN ProcessQuality ON
(QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID) AND
(QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)
WHERE (((ProcessQuality.ProcessID)=3) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

UNION

SELECT ProcessQuality.ProcessID,
ProcessQuality.QualityCode,TilingStart.BFFin,QryRawProcessSumed.SumOfRawWasteCost,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
[SumOfRawWasteCost]+[TotNormCosts] AS TotProcessCostTile,
[BringFor]*1*(1+[ProcessWaste]/100) AS [Total Stock Cost], [Total Stock
Cost]+[TotProcessCostTile] AS Final,
[Labour]+[VarOverHead]+[FixedOverHead]
AS TotNormCosts, TilingStart.BFFIN AS BringFor
FROM TilingStart, ProcessQuality INNER JOIN QryRawProcessSumed ON
(ProcessQuality.QualityCode = QryRawProcessSumed.QualityCode) AND
(ProcessQuality.ProcessID = QryRawProcessSumed.ProcessID)
WHERE (((ProcessQuality.ProcessID)=4) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));
Click to expand...
 
C

Craig

Working Perfectly now, thanks. Comparing them in columns makes things much
easier!


BruceM said:
A few things I noticed:

In the first line of the first query you have:
SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode,0, 0 As FinBFWas

In the other queries the solitary 0 is replaced by FINBF, so I wonder if you
need to alias FINBF and apply the 0 to that in the first query:
SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode, 0 As
FINBF_Alias, 0 As FinBFWas

In the second query you are performing a math function that includes FINBF
(I don't see the point to multiplying by 1). Later you alias FINBF as
BringFor. However, in the first query you have a text 0 (in quotes) as
BringFor. I'm not sure what that is going to do.

BFFin (FINBF) are not spelled consistently, as has been observed.

There is no FINBFWas alias in the third query.

These are my observations, but I cannot attest to their validity, nor can I
be sure I noticed everything there is to notice.

I would try each of the SELECT queries on its own, and compare the results
side by side to be sure you are getting the same fields in each one, and
that the calculations are producing the expected results.


Craig said:
hi,
This query is giving me some grief, the first 2 sections are working ok,
but
once i use the last section the data gets a bit messed up, (Data in wrong
columns).
Its probaly just a 0 missing somewhere but i cant see the wood for the
trees!!
Any help would be appreciated.

CR

SELECT ProcessQuality.ProcessID, ProcessQuality.QualityCode,0, 0 As
FinBFWas, QryRawProcessSumed.SumOfRawWasteCost, ProcessQuality.Labour,
ProcessQuality.VarOverHead, ProcessQuality.FixedOverHead,
ProcessQuality.ProcessWaste, [SumOfRawWasteCost]+[TotNormCosts] AS
TotProcessCost,0 As StartTileCost, [VarOverHead]+[Labour]+[FixedOverHead]
AS
TotNormCosts, "0" AS BringFor
FROM ProcessQuality INNER JOIN QryRawProcessSumed ON
(ProcessQuality.QualityCode = QryRawProcessSumed.QualityCode) AND
(ProcessQuality.ProcessID = QryRawProcessSumed.ProcessID)
WHERE (((ProcessQuality.ProcessID)=6) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

UNION

SELECT ProcessQuality.ProcessID,
ProcessQuality.QualityCode,FinStart.FINBF,
[FinBF]*1*(1+[ProcessWaste]/100) AS FINBFWas,
QryRawProcessSumed.SumOfRawWasteCost, ProcessQuality.Labour,
ProcessQuality.VarOverHead, ProcessQuality.FixedOverHead,
ProcessQuality.ProcessWaste, [SumOfRawWasteCost]+[TotNormCosts]+[FinBFWas]
AS
TotProcessCost, [TotProcessCost] AS StartCostTile,
[Labour]+[VarOverHead]+[FixedOverHead] AS TotNormCosts, FinStart.FINBF AS
BringFor
FROM FinStart, QryRawProcessSumed INNER JOIN ProcessQuality ON
(QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID) AND
(QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)
WHERE (((ProcessQuality.ProcessID)=3) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

UNION

SELECT ProcessQuality.ProcessID,
ProcessQuality.QualityCode,TilingStart.BFFin,QryRawProcessSumed.SumOfRawWasteCost,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
[SumOfRawWasteCost]+[TotNormCosts] AS TotProcessCostTile,
[BringFor]*1*(1+[ProcessWaste]/100) AS [Total Stock Cost], [Total Stock
Cost]+[TotProcessCostTile] AS Final,
[Labour]+[VarOverHead]+[FixedOverHead]
AS TotNormCosts, TilingStart.BFFIN AS BringFor
FROM TilingStart, ProcessQuality INNER JOIN QryRawProcessSumed ON
(ProcessQuality.QualityCode = QryRawProcessSumed.QualityCode) AND
(ProcessQuality.ProcessID = QryRawProcessSumed.ProcessID)
WHERE (((ProcessQuality.ProcessID)=4) AND
((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query Problem 5
UNION Query Parameters 1

Top