Solved

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 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

21 Experts available now in Live!

Get 1:1 Help Now