Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-03-30
4
Medium Priority
?
215 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 500 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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

722 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