Products sold

[linanderson]

I have a Customers, Product Area , Customer Advisor and Product type tables. There are 6 product areas eg: A, B, C, D, E & F. What I would like to show is all Customers who have Product D in isolation ie they have not been sold any other products at the same time.

I cannot get anything thing to work. I have included fields from the above tables (inc a date sold field), but cannot write any expressions to show what is needed.

Would be glad to receive any help.

 

[Alexandre]

Your structure should be like:

TblCustomers
-CustomerID (Autonumber ,Primary Key)
-FirstName
-LastName
Other fields...

TblProducts
-ProductsID (Autonumber, Primary Key)
-Description
-Category
Other fields...

TblProductsPerCustomer
-CustomerID (Long Integer, Foreign Key)
-ProductsID (Long Integer, Foreign Key)
Other fields...
where the primary key s made of two fields: CustomerID and ProductsID

Relationships:
TblCustomers :1___Many: TblProductsPerCustomer : Many____1:TblProducts



If your structure is correct, in the query grid you should just have to:
- Put the Customers, the table that lists products by customers (that I don't see! Is this Products Types? in my example ot os TblProductsPerCustomer) and the one that lists the products and their repective category (Products Area I suppose? TblProducts
in my example)
-Join Customers and the table listing products by customers on the CustomerID field. Join Products Area and the table listing products by customers on the Product ID field
-Use "D" as a criteria for the product category

 

[Jon K]

I would tackle it this way. I would run a total query on the table containing the customer ID, date sold and product (type in a query's SQL View using the correct table name and field names and run it):-

queryDInIsolation:-
SELECT CustomerID, Max(yourTable.Product) AS Product
FROM yourTable
GROUP BY CustomerID
HAVING sum(iif(Product='D',1,2))=1;


This total query will retrieve all those customer ID having "Product D in isolation" i.e. Sum=1. (If you want to take date sold into account, you can add the DateSold field after CustomerID in both the Select clause and the Group By clause, separated with a comma.)

I would then join this queryDInIsolation with the other tables to get info such as customer names and product descriptions.


I could also change this query into a parameter query (i.e. one asking the user to specify a product when it is run):-

SELECT CustomerID, Max(yourTable.Product) AS Product
FROM yourTable
GROUP BY CustomerID
HAVING sum(iif(Product=[Enter a Product],1,2))=1;


Hope this helps.