Customers Who Have Not Ordered During The Last Week Query

[james_88]

Hello all, I'm new to this forum.

I'm having problems creating a query for a database (which I am building as part of my university assignment).

The database is a simple ordering system style database, and contains the tables customer, order, order/product and product. I am trying to build a query that can identify customers who have not made an order during the previous week.

I have tried a criterion " <(now()) - 7 " but all this does is show old orders. I am completely stuck. Any help would be appriciated.

In case it is needed here is a list of fields in each of my tables:

Customer:
Customer ID (PK)
Company Name
Company Street Address
Company Town
Company Region
Company Postcode

Order:
Order ID (PK)
Order Taken By
Date (DD/MM/YYYY) ( =now() )
Customer ID (FK)
Delivery Street Address
Delivery Town
Delivery Region
Delivery Postcode

Order/Product:
Order ID (CK)
Product ID (CK)
Quantity Ordered

Product:
Product ID (PK)
Product Description
Product Cost


Any help would be appriciated. Thanks for reading.

 

[neileg]

I'd do this with a nested query. Create a query that returns the last order placed by each customer and then filter out anything more than a week old. Join this in another query to the customer table using a left join. Anything with a null value in the results from th first query hasn't made an order in the last week.

As this is an assignment I think you need to do the work yourself but I'm happy to give you this hint.

 

[GaryPanic]

good for you- normally we don't give any help - but as your presentation shows that you have applied logic and you have itemised excatly what you have and what you need - this is the reason why you ahve had a response - if you fail over this - ping back and one of the guys will give you some more pointers...