We help IT Professionals succeed at work.

Update Query with Criteria

76 Views
Last Modified: 2019-02-21
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

CERTIFIED EXPERT
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:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.