Union Query Problem

C

Craig

Hi,
I have a union query which brings together Process/Quality Costs, the
problem im having is that i need the first "Start Cost" to come through into
the second part of the query, so i can apply that cost to the second process
- for a total. this needs to go on for 5 to 6 processes.
Any ideas?

Regars

Craig

''Process1"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

''Process 2"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3
 
J

Jeff Boyce

Post a copy of the SQL you are using for the query. It's a bit tough not
having any points of reference to the domain or the specifics of what you
can see (*but we can't*).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Craig said:
Hi,
I have a union query which brings together Process/Quality Costs, the
problem im having is that i need the first "Start Cost" to come through
into
the second part of the query, so i can apply that cost to the second
process
- for a total. this needs to go on for 5 to 6 processes.
Any ideas?

Regars

Craig

''Process1"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

''Process 2"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3
Click to expand...
 
C

Craig

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3

UNION SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID = ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=4;





Jeff Boyce said:
Post a copy of the SQL you are using for the query. It's a bit tough not
having any points of reference to the domain or the specifics of what you
can see (*but we can't*).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Craig said:
Hi,
I have a union query which brings together Process/Quality Costs, the
problem im having is that i need the first "Start Cost" to come through
into
the second part of the query, so i can apply that cost to the second
process
- for a total. this needs to go on for 5 to 6 processes.
Any ideas?

Regars

Craig

''Process1"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

''Process 2"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3
Click to expand...
Click to expand...
 
J

Jeff Boyce

Craig

I'm not sure I'm following...

Your original post mentioned something about a 'second part of the query'
and so on for 5 or 6 processes.

Your union query seems, at first glance to be doing the same (?very similar)
calculations on the same set of data, for 3 separate ProcessIDs.

It would probably help to get a bit more description of how you've organized
your underlying data, and what you want to do (not "how").

Regards

Jeff Boyce
Microsoft Office/Access MVP

Craig said:
SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3

UNION SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=4;





Jeff Boyce said:
Post a copy of the SQL you are using for the query. It's a bit tough not
having any points of reference to the domain or the specifics of what you
can see (*but we can't*).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Craig said:
Hi,
I have a union query which brings together Process/Quality Costs, the
problem im having is that i need the first "Start Cost" to come through
into
the second part of the query, so i can apply that cost to the second
process
- for a total. this needs to go on for 5 to 6 processes.
Any ideas?

Regars

Craig

''Process1"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

''Process 2"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3
Click to expand...
Click to expand...
Click to expand...
 
C

Craig

Basically im wanting to bring the total cost of process 1 into process 2; as
the start cost, so the cost accumulates as it passes through each process.

Jeff Boyce said:
Craig

I'm not sure I'm following...

Your original post mentioned something about a 'second part of the query'
and so on for 5 or 6 processes.

Your union query seems, at first glance to be doing the same (?very similar)
calculations on the same set of data, for 3 separate ProcessIDs.

It would probably help to get a bit more description of how you've organized
your underlying data, and what you want to do (not "how").

Regards

Jeff Boyce
Microsoft Office/Access MVP

Craig said:
SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3

UNION SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=4;





Jeff Boyce said:
Post a copy of the SQL you are using for the query. It's a bit tough not
having any points of reference to the domain or the specifics of what you
can see (*but we can't*).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi,
I have a union query which brings together Process/Quality Costs, the
problem im having is that i need the first "Start Cost" to come through
into
the second part of the query, so i can apply that cost to the second
process
- for a total. this needs to go on for 5 to 6 processes.
Any ideas?

Regars

Craig

''Process1"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

''Process 2"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Craig

So, the total cost of Process1 is $XXX.

The total cost of Process2 is $YYY.

The total cost of Process3 is $ZZZ.

The total cumulative cost through Process2 is $XXX + $YYY.

....

The total TOTAL cost is $XXX + $YYY + $ZZZ.

Is there a reason you couldn't get a Totals query to give you the
per-Process total cost?

And I still don't have a clear picture of how you've structured your data,
so any "how to" question still 'depends'...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Craig said:
Basically im wanting to bring the total cost of process 1 into process 2;
as
the start cost, so the cost accumulates as it passes through each process.

Jeff Boyce said:
Craig

I'm not sure I'm following...

Your original post mentioned something about a 'second part of the query'
and so on for 5 or 6 processes.

Your union query seems, at first glance to be doing the same (?very
similar)
calculations on the same set of data, for 3 separate ProcessIDs.

It would probably help to get a bit more description of how you've
organized
your underlying data, and what you want to do (not "how").

Regards

Jeff Boyce
Microsoft Office/Access MVP

Craig said:
SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3

UNION SELECT QryRawProcessSumed.ProcessID,
QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode)) ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode =
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=4;





:

Post a copy of the SQL you are using for the query. It's a bit tough
not
having any points of reference to the domain or the specifics of what
you
can see (*but we can't*).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi,
I have a union query which brings together Process/Quality Costs,
the
problem im having is that i need the first "Start Cost" to come
through
into
the second part of the query, so i can apply that cost to the second
process
- for a total. this needs to go on for 5 to 6 processes.
Any ideas?

Regars

Craig

''Process1"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed
INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode))
ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode
=
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID,
QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=6

UNION

''Process 2"

SELECT QryRawProcessSumed.ProcessID, QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100)
AS StartCost, QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead] AS
TotCost,
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
FROM Quality INNER JOIN (Process INNER JOIN (QryRawProcessSumed
INNER
JOIN
ProcessQuality ON (QryRawProcessSumed.ProcessID =
ProcessQuality.ProcessID)
AND (QryRawProcessSumed.QualityCode = ProcessQuality.QualityCode))
ON
Process.ProcessID = ProcessQuality.ProcessID) ON Quality.QualityCode
=
ProcessQuality.QualityCode
GROUP BY QryRawProcessSumed.ProcessID,
QryRawProcessSumed.QualityCode,
ProcessQuality.Labour, ProcessQuality.VarOverHead,
ProcessQuality.FixedOverHead, ProcessQuality.ProcessWaste,
([SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead])*([ProcessInput/Output])*(1+[NextProcessWaste]/100),
QryRawProcessSumed.SumOfRawWasteMatCost,
[SumOfRawWasteMatCost]+[Labour]+[VarOverHead]+[FixedOverHead],
Process.[ProcessInput/Output], ProcessQuality.NextProcessWaste,
Quality.ProdStatus, Quality.ProductType, ProcessQuality.LabourCalc,
ProcessQuality.VarHeadCalc, ProcessQuality.FixedHeadCalc
HAVING (QryRawProcessSumed.ProcessID)=3
Click to expand...
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 SQL 4
UNION Query Parameters 1

Top