Small problem with query 3

[ts568]

Hello,

I want to make a query which will list all customers who have ordered something on a specific date but who have not paid yet)

This is what I have got so far

SELECT CUSTOMER.*, ORDERS.*
FROM CUSTOMER, ORDERS
WHERE (((CUSTOMER.customerNumber)= 'paid.yes'));

I am unsure of what to do with the WHERE statement because in the CUSTOMER table, I have a 'paid' field which has a yes/no data type.
How can I get my query to work with this?

Any help would be super.

 

[Remou]

You could try:

[tt]SELECT CUSTOMER.*, ORDERS.*
FROM CUSTOMER, ORDERS
WHERE Not CUSTOMER.Paid[/tt]

However, this is not likely to do what you want in that there is no join between the customer table and the orders table. Lets say that each table has a field called CustomerNumber, you could then say:

[tt]SELECT CUSTOMER.*, ORDERS.*
FROM CUSTOMER INNER JOIN ORDERS ON CUSTOMER.CustomerNumber=ORDERS.CustomerNumber
WHERE NOT CUSTOMER.Paid[/tt]

You may then wish to add a date criterion and change the * to a list of fields.

 

[ts568]

Thanks for your help,

I have used your first example
and have now got

SELECT CUSTOMER.*, ORDERS.*
FROM CUSTOMER, ORDERS
WHERE Not CUSTOMER.Paid
AND ORDERS. orderDate BETWEEN "01/05/2007" AND "31/05/2007";

Its very close now but it wont execute ever since I have put in the part about the date.

 

[Remou]

Dates need different delimiters and it is best to use yyy/mm/dd format:

[tt]SELECT CUSTOMER.*, ORDERS.*
FROM CUSTOMER, ORDERS
WHERE Not CUSTOMER.Paid
AND ORDERS.OrderDate BETWEEN #2007/05/01# AND #2007/05/31#;[/tt]

By the way, why are you not using the query design grid, which can make it easier to build queries?

 

[lespaul]

dates are delimited with #, not "

BETWEEN #01/05/2007# AND #31/05/2007#;

and there are issue with using non-standard US date formats, but I can't tell you what they all are (I don't really use Access anymore)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[JerryKlmns]

list customers

1 Customer - Many Orders

Maybe a group by on customers or a subquery selecting their CustomerNumber, and leaving out orders info,

 

[ts568]

Thanks for your help guys, I have sloved this problem now