Web Development

Ask something
Discussion Locked

MS Acess 2000 Simple Query ?

0 helpful
7 replies

by mra2

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a database with 4 fields.
Shop (Integer) Year (Text Len 4)
HireDate (Date) Active (Text Len 1 Choices A or D)

I want to choose records where:
the Year = 2003 and there is a valid date in that row and they are Active = A
AND ALSO
they MUST have the Year = 2004 with NO DATE entered in that row and they are Active = A.

So: In the data below Only Shop 1 is chosen.
Shop 1 2003 02-03-2003 A
Shop 1 2004 A

Shop 2 2004 A

Shop 5 2003 03-01-2003 A

Shop 10 2003 A
Shop 10 2004 A

I've tried numerous queries and none work correctly.

Can anyone out there help me??

Thanks, Alan

7 replies

by techrep

I think you need to use a union query that joins the results of two SQL statements as shown below:

Select * from TableName
Where Year = '2003'
And IsDate(HireDate)
And Active = 'A'
Union
Select * from TableName
Where Year = '2004'
And IsNull(HireDate)
And Active = 'A'

by vladislav.macek

Hi Alan,
what about something like this:
SELECT T1.shop
FROM data_table T1 LEFT JOIN data_table T2 ON T1.shop = T2.shop
WHERE T1.year="2003" AND T1.date Is Not Null AND T1.active="A" AND T2.year="2004" AND T2.date Is Null AND T2.active="A"

This discussion is closed to new replies.