How can I setup two new calculated fields in a Union Query?

[countingguru]

Dear Forum



I have a union query (QryProjectCost)that assembles three fields from three different tables:

SELECT Projectnumber,CostType, Amount
FROM TblLaborCost
UNION ALL
SELECT Projectnumber,CostType, Amount
FROM TblMiscCost
UNION ALL
SELECT Projectnumber,CostType, Amount
FROM TblShippingCost

However, I need to setup two additional calculated fields in the Union Query to render the total cost per project and another to render the percentage of each cost record relative to its total project cost.
I believe the DSUM() AS ProjectTotalCost may be appropriate here for total cost, but need help in preparing it.

How do I add these two new fields into the SQL statement without crashing the query?

Thanks !

[June7]

Suggest you build a report with the UNION as RecordSource. Use report group & sorting with aggregate calcs functionality.

Why do you have 3 tables? If the field structure is the same then should be one table.

[countingguru]

Actually I have change order here:

I need to create another UNION ALL SELECT Statement (below) that totals the above costs (from tables: Labor, Misc and Shipping) unique to projectnumber.
We run flash reports in Excel linked to Access.

Thanks again.

[countingguru]

Field structure is not the same. Tables are inherent to the project management/scheduling system. I have to create a Union query to create a single table source.

[June7]

What is '(below)' referring to?

Why do you anticipate the query crashing? Have you already tried DSum? Assuming CostType is text, try:

SELECT Projectnumber, CostType, Amount, DSum("Amount","TblLaborCost","CostType='" & [CostType] & "'") As TotalType, Amount/TotalType As Pct
FROM TblLaborCost
UNION ALL
SELECT Projectnumber, CostType, Amount, DSum("Amount","TblMiscCost","CostType='" & [CostType] & "'") As TotalType, Amount/TotalType As Pct
FROM TblMiscCost
UNION ALL
SELECT Projectnumber, CostType, Amount, DSum("Amount","TblShippingCost","CostType='" & [CostType] & "'") As TotalType, Amount/TotalType As Pct
FROM TblShippingCost;

[countingguru]

Thank you for this, now I get a problem linking to Excel "Query too Complex", do you know what this indicates?

[June7]

What are 'flash' reports?

You want Excel linking to this query?

Does the query run without error?

I never tried to link from Excel to a UNION query. Does the link work without the DSum?

[countingguru]

Thanks again. Yes, Union Query does not link directly to Excel. I created another query to retrieve Union Query and it works now.....however....DSUM() is running an #Error, unfortunately.

[June7]

Don't know, query and DSum look good.

Would have to examine db if you want to provide. Follow instructions at bottom of my post.

[countingguru]

I need the Total to capture the whole dataset unique to projectnumber and costtype.

I am getting #Error on this:

SELECT sq.Projectnumber,sq.CostType, sq.Amount, DSUM("Amount","sq","Projectnumber="&Projectnumber& "AND CostType='"&CostType&"'") AS Total
UNION ALL
SELECT Projectnumber,CostType, Amount
FROM TblMiscCost
UNION ALL
SELECT Projectnumber,CostType, Amount
FROM TblShippingCost) As sq;

[countingguru]

Sorry, syntax error, this is statement that derives an Error:

SELECT sq.Projectnumber,sq.CostType, sq.Amount, DSUM("Amount","sq","Projectnumber="&Projectnumber& "AND CostType='"&CostType&"'") AS Total
FROM
(SELECT Projectnumber,CostType, Amount
FROM TblLaborCost
UNION ALL
SELECT Projectnumber,CostType, Amount
FROM TblMiscCost
UNION ALL
SELECT Projectnumber,CostType, Amount
FROM TblShippingCost) As sq;

[June7]

Think domain aggregate functions have to reference saved Access query, not a nested subquery. So save the UNION then use it in the SELECT query. Also, put spaces on each side of the & characters and in front of the AND operator after the quote mark.

[countingguru]

Thanks June7, but no dice. I still got the #error..

[countingguru]

Also. I am getting "Query too complex" error now

[June7]

Going in circles, will have to analyse db to help.