Parameter for query which compares against other records in same query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi ther

I would like to design a query where one field is a parameter. However, instead of comparing the field value to a control on a form or to a user prompt, I would like the field value to be compared to another field value in ALL the records of the query

Example
Say the query has two fields, [Name]; and [Don't Include If This Name Is Present

Example results might be
[Name]: [Don't Include If This Name Is Present]
1 John Andre
2 Sid Adria
3 Barry Joh

I would like to put a parameter in the [Don't Include If This Name Is Present] field to prevent the 3rd record being returned, because its value (John) matches the [Name] field in another (the first) record

If you can offer any advice on this, I would be most grateful

Thank yo

David
 
Select [Name]
FROM tblA
WHERE [Name] Not In (SELECT [DontIncludeIfThisIsPresent] from tblA);

BTW: Name is not a good name for a field since every object in Access has a
name property.

--
Duane Hookom
MS Access MVP


David Cleave said:
Hi there

I would like to design a query where one field is a parameter. However,
Click to expand...
instead of comparing the field value to a control on a form or to a user
prompt, I would like the field value to be compared to another field value
in ALL the records of the query.
Example:
Say the query has two fields, [Name]; and [Don't Include If This Name Is Present]

Example results might be:
[Name]: [Don't Include If This Name Is Present]:
1 John Andrew
2 Sid Adrian
3 Barry John

I would like to put a parameter in the [Don't Include If This Name Is
Click to expand...
Present] field to prevent the 3rd record being returned, because its value
(John) matches the [Name] field in another (the first) record.
 
Duan

Thank you for your reply

Just another quick question. I notice this query won't return a record where the [DontIncludeIfThisIsPresent] field is null. None of the values in the [Name] field are null, and therefore I would have expected the record to be returned. Do you know a way around this

Thanks agai

David
 
Back
Top