Base - A total from a query needs to be divided by 10 for the correct answer

clendee · March 18, 2018, 7:05pm

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"

Ratslinger · March 18, 2018, 9:19pm

@clendee In future when modifying a question or answer (especially questions) please make note of what was modified as I did. If you don’t, an answer can look as if the person who answered didn’t even look at the question. It can change the entire meaning of the question.

clendee · March 18, 2018, 9:28pm

Ok, apologies there, I’m new to forum / question etiquette. I understand.

Ratslinger · March 18, 2018, 7:38pm

Hello,

It appears the query name for the field is incorrect. Seems to be:

SELECT SUM("qry2_JobMaterials_UnOrdered_NoDates"."Mat Total" /10) AS "Materials Total", FROM "qry2_JobMaterials_UnOrdered_NoDates"

This is base upon qry2_JobMaterials_UnOrdered_NoDates being the name of the first query and Mat Total is the name of the field totaled in that query.

If this is not the problem please provide the original query this is using.

Also, why not just do all in one query?

clendee · March 18, 2018, 8:00pm

Hi, I have updated the original question to include the information you require. I did in two queries because I thought you couldnt accumulate columns and use the result of that in the same query. Thanks for your help.

Ratslinger · March 18, 2018, 9:02pm

This will take some time without any data to work with. Are the results of the first query correct?

clendee · March 18, 2018, 9:32pm

I appreciate you even taking the time to look at this. It seems the issue was to do with how I was calling the ‘Vat_Rate’ in the 2nd query, the one you reformatted. I removed the links to those tables in my answer below and everything works as expected. Im currently looking into another way to reference those Vat values.

Ratslinger · March 18, 2018, 9:37pm

You’re welcome even though I gave no real result. In future, when dealing with something of that size (meaning multiple SQL statements using multiple tables) it is best to actually post a sample .odb with data. Something of the nature you presented would be a great deal of work to generate data for testing. Never post anything with personal or confidential info.

clendee · March 18, 2018, 9:50pm

Understood.

clendee · March 18, 2018, 8:55pm

Thanks to anyone who has had a look at this.

Here is how I solved the second query for now…

SELECT Sum("Mat Total")                                          AS
       "Materials Total"
       ,Sum("Mat Total Mkup")                                    AS
        "Materials Total Mkup"
       ,Sum("Lab Total")                                         AS
        "Labour Total"
       ,Sum( "Mat Total Mkup" ) + Sum( "Lab Total" )             AS "Gross"
       ,( Sum( "Mat Total Mkup" ) + Sum( "Lab Total" ) ) * 0.135 AS "Vat"
       ,( Sum( "Mat Total Mkup" ) + Sum( "Lab Total" ) ) + (
         Sum( "Mat Total Mkup" ) + Sum( "Lab Total" ) ) * 0.135  AS "Net"
FROM   "qry2_JobMaterials_UnOrdered_NoDates"

I have just hard coded the Vat as 0.135 [which I will eventually apply to a global field in a form]
The error was occuring due to the links to other tables in the previous 2nd query.