How to sum two fields values in different tables.

[Marcelo Rojas O.]

Hi everyone, this is my first post.

I have tree tables:



Sales Note
-Number of the note (num whitout duplicates)
-Client (text)
-Date of Note

Items of note

-Letter (A, B, and so on until Z)
-Item (can be duplicated) (from another table with a combobox)
-Color (from another table with a combobox)
-TotalQuantity (num)
-TotalPartials
-Lack

and this table:

Partials order

-Number of partials order (num whitout duplicates)
-State of process (only four states possible) (from another table with a combobox)
-number of partial items made
-Date of partial made

this tables have relashionship like:

Notes 1-----00 items of note ------ partials order

For example:

One client make a note for three items:

Baker store buy
-2000 draw pencils
-1200 piece of papers
-2 knives for paper.

The warehouse office receive this note and go to supply each item, but only can supply a part of each item every day. for example, baker store buy 2000 pencils, and the warehouse supply this on three rounds (500 pencils one day, 500 pencils more the next day, 1000 pencils the next day)

This note have this apparience:

Baker Store

-2000 draw pencils -500 pencils 03/16/2014
-500 pencils 03/17/2014
-1000 pencils 03/18/2014

the anothers items have the same treatment.

My questios is

how can sum the column total of partials from the table Partials Items, and store it in another field in the table items of note (the field's name is TotalPartials)?

The example table have this apparience:

Totalquantity | Totalpartials | Lack
| (500+500+1000) | =(TotalQuantity - TotalPartials)
----------------------------------------------------------------
2000 | 2000 | 0

Any ideas? Please without VBA as possible.

Thanks...

[June7]

The sum of the 3 records is an aggregate calculation. Saving calculated data is usually a bad idea, especially aggregate data. Calculate the aggregate sum when needed. Can be done with aggregate (GROUP BY) Totals query or with DSum domain aggregate function.

Here is example of a nested subquery to calculate aggregate http://allenbrowne.com/subquery-01.html#Aggregation

I presume there is a field in ItemsOfNote for the Note number?

Example with DSum:

SELECT NoteID, Item, TotalQuantity, DSum("Quantity", "Partials", "Item='" & [Item] & "' And NoteID=" & [NoteID]) AS TotalPartials FROM ItemsOfNote;

Assumes Item is text and NoteID is number.