how to get this result?

[ariansman]

Table1

Product    quanity
A              50
B              22
D              11
C              31
B              43
D              8
C              13
A              43

1- How can we get the following table/query from the about table. As you see the sum of each item is shown.
Query1

Product         quantity_sum
A                     93
B                     65
C                     44
D                     19

2- Imagine, that because of any reason, the sum of the quantities of each item should be deducted from item D. can we get the following table/query?
Query 2

Product    quantitysum   D-deduction     result
A              93            19             74
B              65            19             46
C              44            19             25

Thank you

 

[stopher]

For the first part just google aggregate queries which should tell you how to sum by group. You might want to include "Access" in your search to see how it is done in the query editor.

For the second part, you can obviously use the results of the aggregate query. There are a couple of ways to go here:

One way is to create a query that only lists record D (qryD). Then create another query that lists all records from the aggregate query except D. Then add qryD but do not join - thus creating a cartesian product qeury where you can do the apprpriate calculation.

Another way is to create another query that lists all records from the aggregate query except D and use DSUM() to get the values from the D record.

hth
Chris