CODEWITHSUNDEEP
sqlms-access
csg
csg

Reputation: 121

MS Access SQL updating in sequence

I have a table that provides a point-in-time snapshot with the following headings:

| Cust# | Last Trans. | Charge | Quantity |

Every month, I will receive a file from a third party with transactions that will add new cust# or change existing customer information. I am having problems when there are multiple updates to the same Cust# in one month.

For example:

processing the following transaction file:

transactions

should yield the following snapshot table:

snapshot

It may not be the best method, but now I have 3 separate queries to handle NEW, CHANGE and CANCEL. There are no problems with NEW and CANCEL.

Here's how my CHANGE query is set up:

UPDATE snp
INNER JOIN tr
ON snp.[Cust#] = tr.[Cust#]

SET
    snp.[Last Trans] = tr.Transaction,
    snp.Charge = snp.Charge + tr.Charge,
    snp.Quantity = tr.Quantity

WHERE tr.Trans='CHANGE'

Note that Charge is incremental and Quantity is not. Updating Charge is working as expected, but Quantity is not. I do not necessarily get the latest quantity.

How do I ensure that if there are any changes to one customer, that the last Quantity field taken is from the latest CHANGE row (ie. max ID of that cust#)?

Upvotes: 2

Views: 418

Answers (1)

Xion
Xion

Reputation: 318

SELECT * FROM snp
WHERE ID IN (SELECT MAX(ID)
             FROM tr
             GROUP BY CUST#)

The inner query would give you all customers' max ID. You can filter the cust# based on your change criteria. The outer query would give you all the details of that row. You can then use those values in your queries.

Upvotes: 1

Related Questions