Query with conditional table

[TOPSie]

Hi,
I am not sure how to describe my problem, which makes it very hard to define a search term to do a general search.
I have three tables:
Product; Price
Customer; Product
Customer; Product; Special Price

I want to change the Price so I constructed a query - All customers who buy Product X - So the query returns a nice list of all the relevant customers.
But some customers have a special price so I need a query which lists all customers who buy Product X AND if they have a special price.



But if I add the special price to the query I get only those customers who buy Product X AND have a special price - the non-special price customers are not listed??

[Minty]

Post up a picture of your tables or some sample table data for each table.
I don't know why you would need two tables for customers and products, then customers and products and special price.

Surely just enter a special price or don't? Then you query becomes simple with an IIf() clause.

[CJ_London]

Agree with Minty, you only need the one Customer; Product; Special Price table and leave special price blank if not specified

You have described the problem but given no information on where you get the problem i.e.

But if I add the special price to the query I get only those customers who buy Product X AND have a special price - the non-special price customers are not listed??

So what is the sql to the query?

[TOPSie]

Sorry if I was a bit unclear - but full marks to Minty - an IIf did the trick - many thanks

[ArviLaanemets]

Sorry if I was a bit unclear - but full marks to Minty - an IIf did the trick - many thanks

Or you use a query like

SELECT cust.Customer, cust.Product, prod.Price AS ProductPrice, sp.SpecialPrice FROM (tCustomers.Customer cust LEFT JOIN tProducts prod ON prod.Product = cust.Product) LEFT JOIN tCpecialPrices sp ON sp.Customer = cust.Customer AND sp.Product = cust.Product

The query returns Null when the price or special price is undetermined for given product and customer. (You can add a WHERE clause to limit the query to specific customer.)