Hi,
I am referencing a total from a column in another query in order to use the values in a totals query…
In my totals query…
SELECT SUM( “qry2”.“Mat Total” /10 ) AS “Materials Total”,
FROM “qry2_JobMaterials_UnOrdered_NoDates” AS “qry2”
Please notice above that I have added ‘/10’ to the result of
SUM( “qry2”.“Mat Total”). If I do not put in the /10 the decimal
place is in the wrong place in the totals query results.
Here is the original query…
SELECT "tblClients"."Client_Name" AS "Name"
,"tblJob"."JobDate" AS "Job Date"
,"tblJob"."JobDesc" AS "Description"
,"tblItems"."ItemQty" AS "Quantity"
,"tblItems"."ItemDsc" AS "Item Description"
,"tblLegend"."LgdName" AS "Legend"
,"tblItems"."UnitCost" AS "Unit Cost"
,CASE
WHEN "tblItems"."Lgd_ID" = 1 THEN Sum("tblItems"."ItemQty" *
"tblItems"."UnitCost")
ELSE NULL
END AS "Mat Total"
,CASE
WHEN "tblItems"."Lgd_ID" = 1 THEN Sum(
"tblItems"."ItemQty" * "tblItems"."UnitCost" +
"tblItems"."ItemQty" *
"tblItems"."UnitCost" *
"tblMarkup"."Percent")
ELSE NULL
END AS "Mat Total Mkup"
,CASE
WHEN "tblItems"."Lgd_ID" = 2 THEN Sum("tblItems"."ItemQty" *
"tblItems"."UnitCost")
ELSE NULL
END AS "Lab Total"
FROM "tblJob","tblClients","tblItems","tblLegend","tblMarkup"
WHERE "tblJob"."Client_ID" = "tblClients"."Client_ID"
AND "tblItems"."Job_ID" = "tblJob"."Job_ID"
AND "tblItems"."Lgd_ID" = "tblLegend"."Legend_ID"
AND "tblJob"."Markup_ID" = "tblMarkup"."Markup_ID"
GROUP BY
tblClients"."Client_Name","tblJob"."JobDate","tblJob"."JobDesc","tblItems"."ItemQty","tblItems"."ItemDsc" ,"tblLegend"."LgdName","tblItems"."UnitCost"
Apologies, all this is a bit of a mouthful.
Can anyone help with what might be the issue here?
Thanks for your time.
Edited above for clarity by Ratslinger. Below (2nd SQL statement) is even easier to read:
SELECT SUM("qry2"."Mat Total" / 10) AS "Materials Total",
SUM("qry2"."Mat Total Mkup" / 10) AS "Materials (inc Markup)",
SUM("qry2"."Lab Total" / 10) AS "Labour Total",
SUM("qry2"."Mat Total Mkup" / 10) + SUM("qry2"."Lab Total" / 10) AS "Gross Total",
(SUM("Mat Total Mkup" / 10) + SUM("Lab Total" / 10))*"tblVat"."Vat_Rate" AS "Vat Total",
(SUM("Mat Total Mkup" / 10) + SUM("Lab Total" / 10)) +((SUM("Mat Total Mkup" / 10) + SUM("Lab Total" / 10))*"tblVat"."Vat_Rate") AS "Net Total"
FROM "tblJob",
"tblVat",
"qry2_JobMaterials_UnOrdered_NoDates" AS "qry2"
WHERE "tblJob"."Vat_ID" = "tblVat"."Vat_ID"