SQL question

[SEbasic]

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 correctly by adding ORDER BY Category, Art_Date.

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.

[daboss]

hi sebasic,

i didn't see your post earlier. if you have not found a solution, you can try the sql statement below:

select * from Article_Data
where Art_Date = #ThisDate#
union
(select A.* from Article_Data A
inner join (select ID, max(Art_Date) as Art_Date from Article_Data
group by ID) B ON
A.Art_Date = B.Art_Date and
A.Art_Date = B.Art_Date and
A.Art_Date <> #ThisDate#
where A.ID not in (select ID from Article_Data
where Art_Date = #ThisDate#))

[SEbasic]

Haha, looks like we both lost this thread ;)

Cheers very much for the help mate :)

[daboss]

yeah, this place is quite like a maze, ain't it :D