SQL Query

struggling to return required rows

[Stretch]

I have an Access db table with the following fields

ID, PR, Art_Date, Cat, Title, Content

ID is primary Key. PR is yes/no. Art_Date is date time. Cat is a Category ID number. Title and content are text.

I'm trying to write a SQL script that will pull out the latest PR YES rows based on category ID number and any PR YES rows with todays date.

For example, matching todays date I have four Category 2 rows and one Category 1 row. One of the Category 2 rows is PR NO. The latest Category 3 row is a week old.

I'm trying to write a query that returns all these rows except todays Category 2 PR NO.

I also need a query to return the PR YES's but NOT the latest rows by category or anything with todays date (basically the opposite query but still PR YES's).

To make it more interesting I need the query to return the rows grouped by Category and Ordered by Date / time.

I've managed to return everything with todays date and PR YES using:

SELECT *
FROM Article_Data
WHERE
YEAR(#ThisDate#) = YEAR(Art_Date) AND
MONTH(#ThisDate#) = MONTH(Art_Date) AND
DAY(#ThisDate#) = DAY(Art_Date) AND
PR = YES

I can group them by adding ORDER BY Category, but this doesn't keep them in date / time order. I've tried to use GROUP BY Category then ORDER BY ART_Date but obviously haven't quite got the hang of it because I keep getting errors.

I've sort of managed to return unique Categories using DISTINCT but I've failed to return just the latest Category entries and it looks like trying to combine the two approaches is beyond me.

I've gone through a number of tutorials including the w3schools stuff. I need help :-(

Any assistance would be most appreciated.

[aspdaddy]

Have you tried using two order by's:

GROUP BY Category
ORDER BY Category, Art_Date

[Stretch]

Two Orders helps with the ordering (surprise!) thanks. Any ideas on the other stuff?

[tomasz]

for todays date you can do
datediff('d',now(),#Your Date Here#)=0

[Stretch]

LOL, thanks tomasz, that's a lot cleaner.