A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query SQL Syntax



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 12:15 AM posted to microsoft.public.access.queries
Rob Hamlin
external usenet poster
 
Posts: 4
Default Query SQL Syntax

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,
  #2  
Old May 28th, 2010, 02:18 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Query SQL Syntax

On Thu, 27 May 2010 16:15:01 -0700, Rob Hamlin
wrote:

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 W. Vinson [MVP]
  #3  
Old May 28th, 2010, 08:19 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Query SQL Syntax

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

Rob Hamlin wrote:
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,

  #4  
Old May 28th, 2010, 10:15 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Query SQL Syntax

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

Rob Hamlin wrote:
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,


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.