Update Query with Criteria

kay soo
kay soo used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
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.
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))));
Distinguished Expert 2017

Commented:
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.

Author

Commented:
noted with thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial