computed query help?

[masqazi]

i have a table A with columns ( A_ID, Main_Quantity, Sub_Quantity) and another table B with columns (B_ID, B_Name, B_ConversionRate)

now i want a query, so that the result i get is as follow

A_ID, sum(Main_quantity) + sum(Sub_quantity) / B_ConversionRate, sum(Sub_Quantity) - sum(Sub_Quantity) mod ( B_ConversionRate)

how to write or design the query in MS Access 2002

Example scenario: Here main_Quantity is in Kg and Sub_Quantity in grams... The user can enter quantity in terms of Main_Quantity or Sub

 

[Pat Hartman]

Select A_ID, (sum(Main_quantity) + sum(Sub_quantity)) / B_ConversionRate as AnswerA, (sum(Sub_Quantity) - sum(Sub_Quantity)) mod ( B_ConversionRate) As AnswerB
From tblA Inner Join tblB on A_ID = B_ID
Group By A_ID;

I made an assumption about the second calculation. I assumed it was:
(a-b) mod c
rather than
a - (b mod c)
Change the parentheses if necessary.

 

[masqazi]

error in your solution

when i try to write a similar query i got error

"You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"

my query is:

SELECT barcode.itemno, Sum(barcode.qty)+(Sum(barcode.subqty)/(itemmaster.qtycon)) AS Bulk, Sum(barcode.subqty)-(Sum(barcode.subqty) Mod (itemmaster.qtycon)) AS Loose
FROM itemmaster INNER JOIN barcode ON itemmaster.itemno = barcode.itemno
GROUP BY barcode.itemno;

mytables are

itemmaster = itemno, itemname, qtycon ( this is conversion rate from bulk quantity to loose quantity)

barcode = barcode, itemno, qty, subqty, inventoryno

 

[Pat Hartman]

Separate the totals query from the calculation.

SELECT barcode.itemno, Sum(barcode.qty) as SumQty, (Sum(barcode.subqty) as SumSubQty
FROM barcode
GROUP BY barcode.itemno;

Then join this query into the other query and use SumQty and SumSubQty in the calculations.

 

[masqazi]

can u give example

i cant get it...can u give the query sql statement

 

[Pat Hartman]

Query1:
SELECT barcode.itemno, Sum(barcode.qty) as SumQty, (Sum(barcode.subqty) as SumSubQty
FROM barcode
GROUP BY barcode.itemno;

Query2:
SELECT Query1.itemno, ((Query1.SumQty + Query1.Sumsubqty)/itemmaster.qtycon) AS Bulk, Query1.Sumsubqty -(Query1.Sumsubqty Mod itemmaster.qtycon) AS Loose
FROM itemmaster INNER JOIN Query1 ON itemmaster.itemno = Query1.itemno
GROUP BY Query1.itemno;

Assuming the parentheses are all fixed, that should do it. Certain mathematical operations take precedence over others so be sure to use parentheses when creating a calculation that needs them. Multiplication and division are performed before addition and subtraction unless you use parentheses to change the order in which the calculations are performed. Check the calculation for Loose especially because I changed the placement of the parentheses. Obviously - give the queries better names.