update query with summary

  • Thread starter Thread starter Newbie101
  • Start date Start date
N

Newbie101

I have a lot of data that has debits and credits. Whenever 2 field
(customer/city) matched and the debits and credits sum up to zero, I want to
close out both lines by having "Closed" in a status column.

sample:

customer1 LA 100.00
customer1 LA -100.00

customer2 LA 100
customer2 SF -100

When i run a summary query, customer1 will come up as 0 and i would like to
use an update query to update the data on the "status" field. But since
customer2 is different in city, i don't want that field to be closed.

Is there a update query that will do that? Thank you!
 
try an update query something like

update tblcustomer set status = "closed"
where customer in (SELECT tblcustomer.customer FROM tblcustomer
GROUP BY tblcustomer.customer, tblcustomer.city, tblcustomer.status
HAVING (((Sum(tblcustomer.credits))=0)))
and city in (SELECT tblcustomer.city
FROM tblcustomer
GROUP BY tblcustomer.customer, tblcustomer.city, tblcustomer.status
HAVING (((Sum(tblcustomer.credits))=0)))

what happens if customer1 in la takes out another loan so this is your
table

customer1 LA 100.00 closed
customer1 LA -100.00 closed
customer2 LA 100
customer2 SF -100
customer1 LA -100.00

what do you want to happen

as a note the above query will leave closed by the first two records
and when another 100 is added it will close the others

ie
customer1 LA 100.00 closed
customer1 LA -100.00 closed
customer2 LA 100
customer2 SF -100
customer1 LA -100.00 closed
customer1 LA 100.00 closed

if this is fine for what you want then it is ok

Regards
Kelvan
 
Use a totals query with criteria of zero on the sum, out put the customer
and city.
Use that query INNER join on both fields in your delete query.
 
Back
Top