Combine two tables together and get a total sum with criteria (1 Viewer)

Hi there,

I have 2 access tables, Buyer and shipment.

1. the tables are linked with the fields buyer and buyer number.
2. The shipment table contains the breakdown of buyer number and shipment amount.
3. I need to sum up the shipment amount and add it to the buyer table. however, there is a criteria that the shipment amount should be 60% more than the cost for it to be considered paid.
4. one Buyer number can appear in multiple TR number.
5. once the shipment amount has reached 60% more than the cost, the balance shipment amount will be brought over to the next line that matches the same buyer and buyer number.
5. the shipment date also has to be copied over and link with the corresponding shipment amount.

how do i go about doing this?

i have added the final product table.

thank you

Normally you do not save calculated values in tables.

Unless you have a special reason for it, you'd normally make a query that sums all shipments for a customer. I'd suggest looking at sum queries for this part.

Are you trying to create an aging report for outstanding balances?

I think you need to start over with your tables, they are not set up properly. You have 3 tables: [Final Product], [Buyer] and [Shipment] who share way too many fields.

Every field in [Shipment] is in [Buyer].
Every field in [Shipment] is in [Final Product]
Every field in [Buyer] is in [Final Product]

That's not how databases are to work. I don't know if you are moving data from each table or if you are somehow manipulating data and then moving it. Both cases are incorrect.

My suggestion is you read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), give it a shot with your data, set up your relationship tool and post it back here so we can help you walk through laying out your tables properly.

They share many same fields as they are from different report from different departments that i need to combine together.

i have created a total shipment query group by the buyer number.

for example buyer number AA885, it has a total shipment amount of $48.
In the buyer table, there is a matching buyer number AA885 with the cost of $10. 60% of the cost would be $16. hence, from the shipment table, it will add the $16 into the shipment amount of the buyer table and it will be considered paid.
The remaining $32 will be brought over to the next AA885 line in the buyer table. which the next line has the cost of $20 and 60% more of it will be $32. therefore, the remaining $32 shipment amount will be added to that line and it is considered paid.

it can be seen in the final product table as an example

The shipment date that corresponds to the shipment amount will also be added to the buyer table.

the db is from your first post, haven't take a look at the latest one.
you use query1. BUT...
i am using Static Collection on the function.
means, it stays there until you close the db.
if you have edited some cost, then like the variable
states its static, so it may bring same results.

so i created a datasheet form (query1).
use this form, so that the static variable is reset.
if you are going to use the function in other places,
make sure to reset the function (see Open event of query1 datasheet form).

same logic but much cleaner code.

hi arnelgp,

thanks for the help.

i tried to add in more data in the table and there is some error.

1. i keyed in
Buyer table : A1003, AA885 , cost=$100
Shipment table : AA885,shipment amount = $50
Query results: A1003,AA885, $80, 4/5/2019, paid.

Shouldn't be A1003,AA885,$50, 4/5/2019, Balance?

2. I keyed in
Buyer table:
A1005,CC900,$10
A1006,CC900,$40
A1007,CC900,$50
Shipment table: CC900,$100, 8/8/2019
Quert Results:
A1005,CC900,$16
A1006,CC900,$64
A1007,CC900,$80

A1005 and A1006 is correct but A1007 should be $20, 8/9/2019, balance?
since is has the balance of the shipment amount of 100-16-64 =20

3. i keyed in a line in the buyer table and did not key in a matching buyer number in the shipment table
the query results show:
A1007,DD500,300,-99,-99,paid.

is it possible to show blank for the shipment amount, shipment date and balance for the status ?

thank you so much!

changed the code.
see and check.
same as before, use the form.