SUM function in field? 1

[faxof]

i'm newish to access development so this question should be simple (i hope)...

i have (amongst others) three tables:
[ul]
[li]salesReceipt(receiptNo,date,customer,paymentAmount);[/li]
[li]receiptItems(receiptNo,itemNo,prodNo,qty,itemTotal);[/li]
[li]inventory(prodNo,descrip,cost,stockQty,sellingPrice);[/li]
[/ul]

i want to know how i can make:
receiptItems.itemTotal = inventory.sellingPrice * receiptItems.qty

and how i can make:
salesReceipt.paymentAmount = the sum of receiptItems.itemTotal

i've tried using the default value when setting up the table, but i failed.

confused,
fax

 

[PHV]

It's often unwise to store derived/calculated values in your database.
To retrieve the calculated values on the fly in query:
SELECT R.receiptNo, R.itemNo, R.prodNo, R.qty, R.qty * I.sellingPrice As itemTotal
FROM receiptItems R INNER JOIN inventory I ON R.prodNo = I.prodNo

SELECT S.receiptNo, S.date, S.customer, Sum(R.qty * I.sellingPrice) As paymentAmount
FROM salesReceipt S INNER JOIN (
receiptItems R INNER JOIN inventory I ON R.prodNo = I.prodNo
) ON S.receiptNo = R.receiptNo
GROUP BY S.receiptNo, S.date, S.customer

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[faxof]

that's great - thank you very much

i understand how simple [red]Select From Where[/red] works, but i am unsure about how the other bits of sql you used work. am i being cheeky but would you mind explaining how [red]INNER JOIN[/red], [red]ON[/red] and [red]GROUP BY[/red] work?

many thanks

fax

 

[PHV]

Search your local drives for files named JET*.CHM

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[faxof]

ok so all that worked and i learned a bit by looking at the JET help files, so thanks for that.
however, i have a form (and subform) showing the sales receipts and each receipt item. i cant get the two totals that we created earlier onto the form. it says that it cannot link the tables.