troubleshooting Question

Update Query with Criteria

Avatar of kay soo
kay soo asked on
Microsoft AccessSQL
4 Comments1 Solution83 ViewsLast Modified:
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.
kay soo

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros