Query Problem

[Guest]

I have a table which looks kind of like this:

[field1] [field2]
601 start
601 finish
602 start

What i want to do is to return all entries in [field1] that have both
"start" and "finish" in [field2]. eg.

[Field1]
601

I know it should be quite simple but cant get my head round it!

TIA

Rico

 

[Guest]

Create a query and put field1 in column 1 and in column 2. Click on the
totals button on the menu. Leave column 1 as group by and change column 2 to
count.
Set the criteria for column 2 to >1
(you could also uncheck the show on column 2 so that only column 1 will
display)

 

[James McNellis]

SELECT startTable.field1
FROM [tablename] startTable, [tablename] finishTable
WHERE startTable.field2='start' AND finishTable.field2='finish' AND
startTable.field1=finishTable.field1

 

[Douglas J Steele]

SELECT startTable.field1
FROM [tablename] startTable
INNER JOIN [tablename] finishTable
ON startTable.field1 = finishTable.field1
WHERE startTable.field2='start'
AND finishTable.field2='finish'

may be more efficient.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT startTable.field1
FROM [tablename] startTable, [tablename] finishTable
WHERE startTable.field2='start' AND finishTable.field2='finish' AND
startTable.field1=finishTable.field1

I have a table which looks kind of like this:

[field1] [field2]
601 start
601 finish
602 start

What i want to do is to return all entries in [field1] that have both
"start" and "finish" in [field2]. eg.

[Field1]
601

I know it should be quite simple but cant get my head round it!

TIA

Rico