asked on

Update Query with Criteria

Wonder anyone can provide the Access Update Query for the following solution:
Two Tables Table1and Table2 are INNER JOIN on ClientID And PaymentMonthYear, Table1 is used for capturing Client's monthly payment made, Table2 is a reference Table to update the monthly supposed payment amount into one of the field of Table1 to determine if full payment made. Table1's Field [SupposedPayment] is update via query from Table2's [SupposedPayment].
But sometimes Clients made multiple payment within a single month, like to hv an update query to output as following:

TABLE2 (Reference Table)
ClienID PaymentMonthYear SupposedPayment
1149A Jan 2019 $500.00
1523B Jan 2019 $300.00
1662C Jan 2019 $400.00
1888K Jan 2019 $100.00
1212B Jan 2019 $400.00

'Output results after upate

TABLE1
ClientID PaymentMonthYear PaymentMade SupposedPayment
1149A Jan 2019 $250.00 $500.00
1149A Jan 2019 $100.00
1149A Jan 2019 $ 50.00
1523B Jan 2019 $200.00 $300.00
1662C Jan 2019 $1000.00 $400.00
1888K Jan 2019 $100.00 $100.00
1888k Jan 2019 $150.00
1212B Jan 2019 $400.00 $400.00

'End

i like to hv the query to update only once to the [SupposedPayment] in Table1 if there's multiple payments within the same month from a single Client (e.g. ClientID 1149A, 1888K), criteria is to update only to a single record if there's multiple entries by a single Client within the same month thx.

PatHartman

You can't create this logic in a query. Queries are set operations so you can't really isolate one record of a set for action unless there is some criteria you can use to reduce the set to only a single record. To do what you are asking, you would need to use VBA and a DAO update loop so you can use program logic to update only the first record of a set and skip the rest of the reocrds.

That said, I wouldn't use this method at all. I see two different potential requirements. One is a report that shows payment detail and the expected payment amount. The second is a need to identify under/over payments or perhaps a separate query for each non-zero result.

The first is a simple report with a sub report. The main report is based on the billed amounts or expected payments whatever you call them. The subreport would be the payments. It will be quite easy to create a difference on the main report to show a negative or positive variance between the expected and actual payments.

To create the other query which is just the variances can be done by making a totals query to select the current month's payments and summarize them by client. You would then use a left join to join the expected payment table to the totals query and select any row where the two amounts are not equal or where the payment amount is nulll. If you want to distinguish over and under payments, you need two "second" queries. One with criteria that selects rows where the payment sum is < expected amount or the payment amount is 0 which will find under payments. The second query would select rows where the payment sum is > the expected amount.

ASKER CERTIFIED SOLUTION

kay soo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

Changing the table allowed you to do what you asked. However, it is still a bad idea. Storing duplicated data violates 2nd Normal Form. All databases should be in at least 3rd Normal form, meaning that it conforms to 1st, 2nd, and 3rd normal forms.

kay soo

ASKER

noted with thanks