SQL Query Help Required

[Greg]

Hello

I have a table 'purchases' as follows:

customerid (Number)
trackid (text)
date (date/time)
available (number)
invoice (number)

A sample of data with fields in the order above may be:

(3, '001M', 01/07/2006, 0, 3)
(8, '002M', 12/07/2006, 1, 5)

The value of "available" will only ever be 0, 1 or -2

I'd like to write a query so I end up with a record set as follows:

If available = 0 for a record I'd like it in the record set only if the
customerid is equal to some specified value.
(ie SELECT * FROM purchases WHERE customerid = 1 AND available = 0)

If available = 1 for a record I'd like it in the record set only if the
customerid is equal to some specified value and the date field for that
record is a date in the last seven days.
(ie SELECT * FROM purchases WHERE customerid = 1 AND date >=
#datesevendaysago#)

I do not want any records in the record set where available = -2.

Passing the date seven days ago into the query is not a problem, but I have
no clue how to "combine" these two queries so the results are in one record
set - any help would be greatly appreciated.

Kind regards,
Greg

 

[Jeff L]

Select *

From Purchases

Where Available = 0
OR (Available = 1 AND PurchaseDate >= Date() - 7)
And CustomerID = 1

I would suggest that you change your date field to a different name
because Date is a reserved word in Access.

Hope that helps!