Query SQL Syntax
in [Queries]
|
From: Rob Hamlin on 27 May 2010 19:15 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 Phone:(Select [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,
From: John W. Vinson on 27 May 2010 21:18 On Thu, 27 May 2010 16:15:01 -0700, Rob Hamlin <RobHamlin(a)discussions.microsoft.com> 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 Phone:(Select [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]
From: John Spencer on 28 May 2010 15:19 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 Phone:(Select First([Business Phone]) FROM [Contacts Extended] Where [Contacts Extended].[Contact Name]=[Target_Stores_MRRS_Extended].[DPM] AND [Business Phone] Is Not Null) OR DPM Phone:(Select 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 Phone:(Select [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,
From: KenSheridan via AccessMonster.com on 28 May 2010 17:15 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 Phone:(Select [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/Forums.aspx/access-queries/201005/1
|
Pages: 1 Prev: two level make table query and sorting??? Next: Update Query with Parameter |