Avatar of kay soo
kay soo
 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

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

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.
Microsoft AccessSQL

Avatar of undefined
Last Comment
kay soo

8/22/2022 - Mon

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 soo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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

noted with thanks
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes