Solved

Why is my union query asking for parameter values? (MS Access)

Posted on 2009-03-30
4
210 Views
Last Modified: 2012-05-06
When I run my union query, it asks for parameter values when it shouldn't.  Here is the query:

SELECT Employees.FirstName, Employees.LastName, Employees.UniqueID, Employees.Address, Employees.Address2, Employees.Address3, Employees.City, Employees.StateProvince, Employees.PostalCode, Employees.EmailAddress, Employees.EmailAddress2, Employees.EmailAddress3, Employees.MobilePhone, Employees.MobilePhoneCountryCallingCode, Employees.MobilePhone2, Employees.MobilePhone2CountryCallingCode, Employees.BusinessPhoneNumber, Employees.BusinessPhoneExt, Employees.BusinessPhoneCountryCallingCode, Employees.HomePhone, Employees.HomePhoneCountryCallingCode, Employees.TextDeviceUnlimited, Employees.TextDeviceLimited, Employees.SMSDevice1, Employees.SMSDevice1CountryCallingCode, Employees.Role,  Employees.MCTFields
FROM Employees
UNION
SELECT Spouses.FirstName, Spouses.LastName, Spouses.UniqueID, Spouses.Address, Spouses.Address2, Spouses.Address3, Spouses.City, Spouses.StateProvince, Spouses.PostalCode, Spouses.EmailAddress, Spouses.EmailAddress2, Spouses.EmailAddress3, Spouses.MobilePhone, Spouses.MobilePhoneCountryCallingCode, Spouses.MobilePhone2, Spouses.MobilePhone2CountryCallingCode, Spouses.BusinessPhoneNumber, Spouses.BusinessPhoneExt, Spouses.BusinessPhoneCountryCallingCode, Spouses.HomePhone, Spouses.HomePhoneCountryCallingCode, Spouses.TextDeviceUnlimited, Spouses.TextDeviceLimited, Spouses.SMSDevice1, Spouses.SMSDevice1CountryCallingCode, Spouses.Role,  Spouses.MCTFields
FROM Spouses
UNION
SELECT AuthorizedContacts.FirstName, AuthorizedContacts.LastName, AuthorizedContacts.UniqueID, AuthorizedContacts.Address, AuthorizedContacts.Address2, AuthorizedContacts.Address3, AuthorizedContacts.City, AuthorizedContacts.StateProvince, AuthorizedContacts.PostalCode, AuthorizedContacts.EmailAddress, AuthorizedContacts.EmailAddress2, AuthorizedContacts.EmailAddress3, AuthorizedContacts.MobilePhone, AuthorizedContacts.MobilePhoneCountryCallingCode, AuthorizedContacts.MobilePhone2, AuthorizedContacts.MobilePhone2CountryCallingCode, AuthorizedContacts.BusinessPhoneNumber, AuthorizedContacts.BusinessPhoneExt, AuthorizedContacts.BusinessPhoneCountryCallingCode, AuthorizedContacts.HomePhone, AuthorizedContacts.HomePhoneCountryCallingCode, AuthorizedContacts.TextDeviceUnlimited, AuthorizedContacts.TextDeviceLimited, AuthorizedContacts.SMSDevice1, AuthorizedContacts.SMSDevice1CountryCallingCode, AuthorizedContacts.Role,  AuthorizedContacts.MCTFields
FROM AuthorizedContacts;

When I run the query, it asks for:

Employees.MobilePhone2
Employees.MobilePhone2CountryCallingCode
Spouses.BusinessPhoneExt
AuthorizedContacts.BusinessPhoneExt

All the fields match.  Any ideas?
0
Comment
Question by:DonStary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 125 total points
ID: 24020491
DonStary said:
>>All the fields match.  Any ideas?

I would suggest that you do not have the field names quite right.  Are there perhaps underscores, punctuation,
or maybe spaces in the field names?

If you try running each of the "component SELECT queries" on its own, does it ask for parameters?
0
 

Author Comment

by:DonStary
ID: 24020710
Right on the money, matthewspatrick.

In the Employees table, the "MobilePhone2" field and "MobilePhone2CountryCallingCode" field each contained a space.  In the Spouses and AuthorizedContacts tables, the "BusinessPhoneExt" fields were both labeled "BusinessPhoneExtension."

I was making that much harder than it should have been.  Thank you!!!
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24020808
As matthewspatrick has said can you try each SELECT statement, I would paste each SELECT into the SQL view of a new query and run it, You will then see the problem fields being prefixed with Expr1, Expr2 etc, this is the only reason for them not being picked up.

Also I would alias your table, it keeps the SQL shorter see the example below. Strictly speaking you do not need to reference the table if the field names are unique but it is best practice to and slightly quicker.

Cheers, Andrew
SELECT E.FirstName, E.LastName, E.UniqueID, E.Address, E.Address2, E.Address3, E.City, E.StateProvince, E.PostalCode, E.EmailAddress, E.EmailAddress2, E.EmailAddress3, E.MobilePhone, E.MobilePhoneCountryCallingCode, E.MobilePhone2, E.MobilePhone2CountryCallingCode, E.BusinessPhoneNumber, E.BusinessPhoneExt, E.BusinessPhoneCountryCallingCode, E.HomePhone, E.HomePhoneCountryCallingCode, E.TextDeviceUnlimited, E.TextDeviceLimited, E.SMSDevice1, E.SMSDevice1CountryCallingCode, E.Role,  E.MCTFields
FROM Employees E
UNION
SELECT S.FirstName, S.LastName, S.UniqueID, S.Address, S.Address2, S.Address3, S.City, S.StateProvince, S.PostalCode, S.EmailAddress, S.EmailAddress2, S.EmailAddress3, S.MobilePhone, S.MobilePhoneCountryCallingCode, S.MobilePhone2, S.MobilePhone2CountryCallingCode, S.BusinessPhoneNumber, S.BusinessPhoneExt, S.BusinessPhoneCountryCallingCode, S.HomePhone, S.HomePhoneCountryCallingCode, S.TextDeviceUnlimited, S.TextDeviceLimited, S.SMSDevice1, S.SMSDevice1CountryCallingCode, S.Role,  S.MCTFields
FROM Spouses S
UNION
SELECT AC.FirstName, AC.LastName, AC.UniqueID, AC.Address, AC.Address2, AC.Address3, AC.City, AC.StateProvince, AC.PostalCode, AC.EmailAddress, AC.EmailAddress2, AC.EmailAddress3, AC.MobilePhone, AC.MobilePhoneCountryCallingCode, AC.MobilePhone2, AC.MobilePhone2CountryCallingCode, AC.BusinessPhoneNumber, AC.BusinessPhoneExt, AC.BusinessPhoneCountryCallingCode, AC.HomePhone, AC.HomePhoneCountryCallingCode, AC.TextDeviceUnlimited, AC.TextDeviceLimited, AC.SMSDevice1, AC.SMSDevice1CountryCallingCode, AC.Role,  AC.MCTFields
FROM AuthorizedContacts AC;

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24020994
DonStary said:
>>I was making that much harder than it should have been.  Thank you!!!

We've all been in that place before.  Glad to help :)
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question