Solved

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

Posted on 2009-03-30
4
189 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
  • 2
4 Comments
 
LVL 92

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 92

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Turn off MS Access Default=0 for Numerics 6 23
Sql Server group by 10 23
Mysql Left Join Case 10 41
Access: Retrieving Current Month's Orders for Invoice 6 25
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now