Union Query Formatting

[ccondran08]

Hi, I've searched a few forums and websites and still can't find exactly what I'm looking for so hoping someone will be able to help me out here. I need to format the fields [QCF Weight] and [AQ] as they are used in the calculation below (Sum([QCF Weight]*[AQ])) and the result is giving me problems due to the rounding.


SELECT tbl_PMS_Data.RES, Max(tbl_PMS_Data.[QCF Weight]) AS [MaxOfQCF Weight], Sum(tbl_PMS_Data.[Total Cost]) AS [SumOfTotal Cost], Sum(tbl_PMS_Data.BQ) AS SumOfBQ, Sum(tbl_PMS_Data.AQ) AS SumOfAQ, Sum([QCF Weight]*[AQ]) AS Man_Hours
FROM tbl_PMS_Data RIGHT JOIN tbl_PMS_Codes ON tbl_PMS_Data.RES = tbl_PMS_Codes.Code
GROUP BY tbl_PMS_Data.RES, tbl_PMS_Codes.Category, tbl_PMS_Codes.Code, tbl_PMS_Data.Resp, tbl_PMS_Codes.Co
HAVING (((tbl_PMS_Codes.Category)="Electrical") AND ((tbl_PMS_Data.Resp)="SH") AND ((tbl_PMS_Codes.Co)="SH"))

UNION SELECT tbl_PMS_Data.RES, Max(tbl_PMS_Data.[QCF Weight]) AS [MaxOfQCF Weight], Sum(tbl_PMS_Data.[Total Cost]) AS [SumOfTotal Cost], Sum(tbl_PMS_Data.BQ) AS SumOfBQ, Sum(tbl_PMS_Data.AQ) AS SumOfAQ, Sum([QCF Weight]*[AQ]) AS Man_Hours
FROM tbl_PMS_Data RIGHT JOIN tbl_PMS_Codes ON tbl_PMS_Data.RES = tbl_PMS_Codes.Code
GROUP BY tbl_PMS_Data.RES, tbl_PMS_Codes.Category, tbl_PMS_Codes.Code, tbl_PMS_Data.Resp, tbl_PMS_Codes.Co
HAVING (((tbl_PMS_Codes.Category)="Electrical") AND ((tbl_PMS_Data.Resp)="TP") AND ((tbl_PMS_Codes.Co)="TP"));

 

[arnelgp]

why dont you calculate it using Decimal.
create a public function, say, Cast2Dec:

public function Cast2Dec(v as Variant)
Cast2Dec = CDec(v)
end function

then on your query:

Sum(Cast2Dec([QCF Weight]*[AQ])) AS Man_Hours

 

[plog]

There's no need for a UNION query. A UNION query brings together otherwise disjoint sets of data. This data comes from the same source, with virtually the same criteria:

HAVING (((tbl_PMS_Codes.Category)="Electrical") AND ((tbl_PMS_Data.Resp)="SH") AND ((tbl_PMS_Codes.Co)="SH"))

HAVING (((tbl_PMS_Codes.Category)="Electrical") AND ((tbl_PMS_Data.Resp)="TP") AND ((tbl_PMS_Codes.Co)="TP"));

The second half of the HAVING clauses are the only difference. And you even GROUP BY the fields that you are applying different criteria to. This can be accomplished in 1 SELECT by using OR between the two different sets of criteria.

 

[ccondran08]

I needed the union query for ((tbl_PMS_Data.Resp)="SH") and ((tbl_PMS_Data.Resp)="TP") as I was getting conflicting results if I had selected "SH" and/or "TP" as the criteria.

I have just changed my data type in the table from Long Integer to Double with 2 decimals and it works fine now and I am getting the results I need so thanks for those who replied.