G
Guest
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?
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?