Solved

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

Posted on 2009-03-30
4
198 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

820 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