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.
kay sooAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.
kay sooAuthor Commented:
Thx for the reply,

I Found a solution, a simple update query derived from AllenBrowne's Delete Duplicate Query http://allenbrowne.com/
Table1 must add an Autonum ID then run the following update query.
It works great, sharing it out just in case someone required the same method.

UPDATE Table1 INNER JOIN Table2 ON (Table1.ClientID = Table2.ClientID) AND (Table1.PaymentMonthYear = Table2.PaymentMonthYear) SET Table1.SupposedPayment = Table2.SupposedPayment
WHERE (((Table1.id)=(SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe WHERE (Dupe.ClientID = Table1.ClientID) AND (Dupe.PaymentMonthYear = Table1.PaymentMonthYear))));

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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 sooAuthor Commented:
noted with thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.