Query SQL Syntax

[Rob Hamlin]

I have 2 queries that I am building another query off of.

I have on query with the name of project contacts in it.

I have another query with all the associated contact info in it.

I made a cutom colum in the query and used this syntax.
DPM PhoneSelect [Business Phone] FROM [Contacts Extended] Where [Contacts
Extended]![Contact Name]=[Target_Stores_MRRS_Extended]![DPM])

This is not working. Can anyone help or point me to a good resource for how
to use the syntax correctly.

Thanks,

 

[John W. Vinson]

I have 2 queries that I am building another query off of.

I have on query with the name of project contacts in it.

I have another query with all the associated contact info in it.

I made a cutom colum in the query and used this syntax.
DPM PhoneSelect [Business Phone] FROM [Contacts Extended] Where [Contacts
Extended]![Contact Name]=[Target_Stores_MRRS_Extended]![DPM])

This is not working. Can anyone help or point me to a good resource for how
to use the syntax correctly.

Thanks,

Try using . instead of ! as a delimiter. Fieldnames in tables/queries use a
dot delimiter, not a bang.

It would also help to know the context in which you're using this, and what
[Target_Stores_MRRS_Extended] might be. You should also be very, very careful
about trying to link tables using [Contact Name] - names are NOT unique (I
know three guys named Fred Brown), are not stable (is Al Wilson the same
person as Alan Wilson, or is he Albert Wilson...?), and are inappropriate for
joins, unless you're using external data and have no choice in the matter!

 

[John Spencer]

Since you are using that as a field, you must return only on record and Access
must know that only one record is being returned.

Try modifying that to
DPM PhoneSelect First([Business Phone]) FROM [Contacts Extended] Where
[Contacts Extended].[Contact Name]=[Target_Stores_MRRS_Extended].[DPM]
AND [Business Phone] Is Not Null)

OR
DPM PhoneSelect Max([Business Phone]) FROM [Contacts Extended] Where
[Contacts Extended].[Contact Name]=[Target_Stores_MRRS_Extended].[DPM])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KenSheridan via AccessMonster.com]

Do you really need to use a subquery here? As you are simply returning the
Business Phone value from Contacts Extended where the Contact Name value
matches the DPM value in Target_Stores_MRRS_Extended I'd have thought you
could have joined Contacts Extended to Target_Stores_MRRS_Extended on these
columns and return the Business Phone value in a column.

If there might be rows in Contacts Extended with no matches in
Target_Stores_MRRS_Extended the join would need to be a LEFT OUTER JOIN.

A join will also generally be a lot faster than a correlated subquery.

Ken Sheridan
Stafford, England

I have 2 queries that I am building another query off of.

I have on query with the name of project contacts in it.

I have another query with all the associated contact info in it.

I made a cutom colum in the query and used this syntax.
DPM PhoneSelect [Business Phone] FROM [Contacts Extended] Where [Contacts
Extended]![Contact Name]=[Target_Stores_MRRS_Extended]![DPM])

This is not working. Can anyone help or point me to a good resource for how
to use the syntax correctly.

Thanks,