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

 

[kaspi]

Hi Sara

I believe that you could give a cross tab qry a try. With the products as col haeading Customer as your row and Sum qty as your value (if I understand well you have 3 tables tbl Customers joint to tbl Purchases on Customer_id and Purchases joint to Products on Product_id).

In this scenario you will get all products that were sold at least once (you can use Nz function to display 0 for products with no sale).

You could experiment with outer joint products outer joint purchases to get all the products (you may have to save it as a qry and then run cross tab on Customer joint
saved qry.

Hope it helps

kaspi