Solved

Union query producing parameter dialog with unnamed query in parameter

Posted on 2011-09-16
19
275 Views
Last Modified: 2012-05-12
I have the following union query producing the error:

SELECT [Class List by Teacher].TeacherID, [Class List by Teacher].FamilyID, [ParentFNCombo 4 UNION].FamilyID, [Class List by Teacher].ParentID, [Class List by Teacher].FamilyName, [Class List by Teacher].StudentFirstName, [Class List by Teacher].FirstName, [Class List by Teacher].Address1, [Class List by Teacher].Phone1, [Class List by Teacher].PrimaryInd, [ParentFNCombo 4 UNION].FNCombo
FROM [Class List by Teacher] LEFT JOIN [ParentFNCombo 4 UNION] ON [Class List by Teacher].FamilyID = [ParentFNCombo 4 UNION].FamilyID;

ERROR dialog pops up:
"Enter Parameter Value"
Query1.FamilyID

Where is Query1 coming from?  Why is it asking?  This is the last of several queries, all run successfully until this one.  If I click Cancel, it returns no records.  If I click OK, even if entering no value in the parameter dialog, it returns all the records.
0
Comment
Question by:ErinRad
[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
  • 6
  • 6
  • 4
  • +2
19 Comments
 
LVL 75
ID: 36550432
I wonder if this

ParentFNCombo 4 UNION

is an issue because UNION is a reserved word ?

mx
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36550448
If you don't enter a value at the prompt, are any of the fields showing up blank?  If so, chances are that the query that is providing that value, doesn't really have a [FamilyID] field.

I would check on your queries: [ClassList by Teacher], [ParentFNCombo 4 UNION] to see if either of them uses a Query1.

You might also want to check to see if either of these queries, or this one contains a defined parameter [Query1].[FamilyID]

0
 

Author Comment

by:ErinRad
ID: 36550455
Good point... I changed the name of the UNION query used in this ordinary query but still received the same error.  New SQL as follows:

SELECT [Class List by Teacher].TeacherID, [Class List by Teacher].FamilyID, [Class List by Teacher].ParentID, [Class List by Teacher].FamilyName, [Class List by Teacher].StudentFirstName, [Class List by Teacher].FirstName, [Class List by Teacher].Address1, [Class List by Teacher].Phone1, [Class List by Teacher].PrimaryInd, [ParentFNCombo 4].FNCombo
FROM [Class List by Teacher] LEFT JOIN [ParentFNCombo 4] ON [Class List by Teacher].FamilyID = [ParentFNCombo 4].FamilyID;
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:ErinRad
ID: 36550471
To FYED:
All fields are populated as expected without entering a value at the prompt.
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 36550480
try this

SELECT c.TeacherID, c.FamilyID, c.ParentID, c.FamilyName, c.StudentFirstName, c.FirstName, c.Address1, c.Phone1, c.PrimaryInd, p.FNCombo
  FROM [Class List by Teacher] c
       LEFT JOIN [ParentFNCombo 4] p ON c.FamilyID = p.FamilyID
0
 
LVL 75
ID: 36550486
oK ... what is this:

[ParentFNCombo 4].FNCombo

The 4 ... also in 2 other places ....?

SELECT [Class List by Teacher].TeacherID,
    [Class List by Teacher].FamilyID,
    [Class List by Teacher].ParentID,
    [Class List by Teacher].FamilyName,
    [Class List by Teacher].StudentFirstName,
    [Class List by Teacher].FirstName,
    [Class List by Teacher].Address1,
    [Class List by Teacher].Phone1,
    [Class List by Teacher].PrimaryInd,
    [ParentFNCombo 4].FNCombo


FROM [Class List by Teacher]
    LEFT JOIN [ParentFNCombo 4]
        ON [Class List by Teacher].FamilyID
                = [ParentFNCombo 4].FamilyID;
0
 

Author Comment

by:ErinRad
ID: 36550490
SQL for Class List by Teacher:
SELECT Students.TeacherID, Families.FamilyID, Parents.ParentID, Families.FamilyName, Students.StudentFirstName, Parents.FirstName, Addresses.Address1, [Parent Contact Information].Phone1, Addresses.PrimaryInd
FROM (((Families LEFT JOIN Parents ON Families.FamilyID = Parents.FamilyID) LEFT JOIN Students ON Families.FamilyID = Students.FamilyID) LEFT JOIN Addresses ON Parents.ParentID = Addresses.ParentID) LEFT JOIN [Parent Contact Information] ON Parents.ParentID = [Parent Contact Information].ParentID
WHERE (((Addresses.PrimaryInd)=True))
ORDER BY Families.FamilyID, Addresses.Address1 DESC;


SQL for ParentFNCombo 4:
SELECT [ParentFNCombo 2].FamilyID, [ParentFNCombo 2].ParentID, [ParentFNCombo 2].FNCombo FROM [ParentFNCombo 2]
UNION ALL SELECT [ParentFNCombo 3 Single Parents w FN].FamilyID, [ParentFNCombo 3 Single Parents w FN].ParentID, [ParentFNCombo 3 Single Parents w FN].FirstName AS FNCombo
FROM [ParentFNCombo 3 Single Parents w FN];
0
 
LVL 52

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 36550495
are these tables or queries

[Class List by Teacher]
[ParentFNCombo 4]

if queries post the sql...
0
 

Author Comment

by:ErinRad
ID: 36550500
To DatabaseMX:
the "4" is part of the query name (it's 4th in a sequence of queries)
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 36550520
I don't trust Access with so much uninon/union alls/ left joins... it may give unpredicted results...
if possible write your new query based on tables only...
0
 
LVL 75
ID: 36550522
Get this tool
http://www.rickworld.com/products.html#Find%20and%20Replace%209.0
... best ever ... and Search for Query1

mx
0
 

Author Comment

by:ErinRad
ID: 36550538
to HainKurt:
That worked... can you explain for me why?  

Also, quick story for all you experts...
I was at my kids' school roller skating party, and there was this guy who was a pretty great skater and he was wearing a black experts-exchange.com t-shirt!  I rolled over and said "excuse me, I'm sure people don't usually tell you this but I LOVE your shirt!"  He was pretty shocked (woman approaches techie??), but I told him I thought this was the best tech help forum EVER.  

Thanks always to all of you for being there when we get stuck :)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36550550
I think you are going to have to go back and look at the other 3 queries that look like:

[ParentFNCombo 2]
[ParentFNCombo 3 Single Parents w FN]

and whatever the name is for the [Combo 1] version of this.
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36550553
In your query, you are selecting 2 columns named FamilyID.  Maybe renaming one of them would work?

For example,

 [ParentFNCombo 4 UNION].FamilyID AS FamilyID2

0
 

Author Closing Comment

by:ErinRad
ID: 36550572
Very fast and direct resolution. THANKS
0
 
LVL 75
ID: 36550582
"woman approaches techie?"
I'm waiting for that to happen at LA Fitness, where I where EE shirts every night!

btw ... sounds like you are female.  You might want to change your EE icon to the female version :-)

mx
0
 
LVL 75
ID: 36550605
@HainKurt:

''I don't trust Access with so much uninon/union alls/ left joins... it may give unpredicted results...
if possible write your new query based on tables only..."
Do you have an example where this fails ... as I've never had an issue ...?
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 36552464
I have 63 TShirts and started giving them to my friends as gift :) EE should give us some other choices.

I DO NOT WANT BLACK T-SHIRT ANYMORE!

Can you hear me EE!
0
 
LVL 75
ID: 36552477
I want black, but different artwork ... something new and different.  And MUST include the phrase  

Experts-Exchange.com

I got one recently - must have been an short lived interim - with different artwork, but no clue that it was an EE shirt.  These shirts are ADVERTISING ... hello !!!  I wear them to LA Fitness every night.

mx
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 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