Is there a quicker way to update multiple fields? (1 Viewer)

[Sara Overton]

Hi

I have a main table holidng my customers. Then another table with purchases. In this table multiple product can be entered and quantites for a customer. The product details are held in another table and details which product is in which product category. Exactly as Northwind.

I need to produce a list showing all my customers and total of items in each product category they have purchased. These need to appear against the record as separate fields. Therefore only displaying customer details once.

I also need to show each product even if no one has ever ordered one. So I think that eliminates the cross tab query.

I have approximately 60 products!

Is there anyway I can get round having to set up 60 update queries and run each one?

Thanks

 

[Chris RR]

OK, you aren't going to create any update queries. What you are trying to do (if I read you write) is simply report the information that is already stored in your table.

From you description, you really are looking for two separate reports. One will show products by customer. It doesn't really make sense to put products that haven't been ordered by anyone on a report like this. In your report, you would control whether the customer prints only once, or on each product line.

The other report is orders by product. This is tricky only in that you want to use the right type of join (a "left" join). Here is where you look to find all products, even the unpopular ones.

The number of products does not matter at all, 60 or 60,000...