Query to remove duplicates, and amend some other fields...

A

Aaron Howe

I have a couple of linked tables - one contains a list of customers and their
balances, and the other contains product information for every customer. In
theory a customer can have many products against one balance, so there would
be approx five times more detail in the product list than in the balance list.

I have made a query to insert the balance data into each product line
linking on the customer's unique ID number. That's fine but it means where a
customer has say three products, the query pulls in three balances
(effectively multiplying what they owe me). I have run a query to delete
duplicate entries and that has removed most of the problem items. I am still
left with the legitimate entries though, and there are 14,000 to go through
by hand which is a little too much work for me on a Monday morning.

What I would like to do is to have a query which says:
Customer 1, 1 balance found, 3 products. Applied balance information to the
first product and zero balance to the other two.
Customer 2, 2 balances found, 6 products. Applied total balance information
to the first product and zero balance to the other two.

I'd be happy to do this in stages if I needed to, but I can't figure out the
SQL required. Any pointers?
 
S

Sheila D

Maybe I'm missing something but it sounds as if you need a fairly straight
forward Totals query which groups on customer, counts balance and counts
products. If so you should be able to do this in Query Design View by
clicking Totals button on the toolbar and it will write the SQL for you. HTH

Sheila
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top