UNION SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig - 24 Jul 2007 09:24 GMT

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]));

Reply to this Message

Matthias Klaey - 24 Jul 2007 09:43 GMT

> hi,
> This query is giving me some grief, the first 2 sections are working ok, but
[quoted text clipped - 4 lines]
>
> CR

[...]

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

Signature

www.kcc.ch

Reply to this Message

Andy Hull - 24 Jul 2007 10:06 GMT

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

> hi,
> This query is giving me some grief, the first 2 sections are working ok, but
[quoted text clipped - 48 lines]
> WHERE (((ProcessQuality.ProcessID)=4) AND
> ((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

Reply to this Message

BruceM - 24 Jul 2007 13:58 GMT

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.

> hi,
> This query is giving me some grief, the first 2 sections are working ok,
[quoted text clipped - 54 lines]
> WHERE (((ProcessQuality.ProcessID)=4) AND
> ((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

Reply to this Message

Craig - 24 Jul 2007 16:26 GMT

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

> A few things I noticed:
>
[quoted text clipped - 80 lines]
> > WHERE (((ProcessQuality.ProcessID)=4) AND
> > ((ProcessQuality.QualityCode)=[Forms]![Switchboard]![Text0]));

Reply to this Message