Solved

Union Query error "Query input must contain at least one table or query"

Posted on 2006-06-20
15
510 Views
Last Modified: 2012-08-14
Hey all,
I am trying to union three queries. Only one of these queries actually has a table behind it. The other two just get data from a form. They all have the same number of fields. This doesn't seem to be working with the UNION statement. Is there any other way to get these three queries  into a single form or report or something?

Thanks,
Stratocaster
0
Comment
Question by:Stratocaster
[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
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16944673
Hi Stratocaster,
> The other two just get data from a form.

Then why not use the RecordSource for that form in the Union construction?

Regards,

Patrick
0
 

Author Comment

by:Stratocaster
ID: 16944702
Hey Patrick,
Thanks for the response. I'm not sure how to do that. Can you explain it a bit?

Thanks,
Strat
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16944760
Stratocaster,

What is the source table/query for this form?

Regards,

Patrick
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:Stratocaster
ID: 16944912
Here are the tables that the form gets it's information from. These are in combo boxes by the way.

tblRouteID_MASTER

tblMaxLoadContainerType

tblMaxLoadUserName

The form combo box names are:

cboManifest

cboUserID

cboContainerType

respectively.

Thanks,
Strat
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16945479
Stratocaster,

Yes, but is there a query that the form draws from, and if so, what is the SQL?

For that matter, what is the SQL for the union query thus far?

Regards,

Patrick
0
 

Author Comment

by:Stratocaster
ID: 16945527
Ok here is the SQL for the combo boxes as well as the SQL for the union query.

cboManifest [SQL] --> SELECT tblRouteID_MASTER.RouteId FROM tblRouteID_MASTER;

cboUserID [SQL] --> SELECT tblMaxLoadUserName.UserName FROM tblMaxLoadUserName;

cboContainerType [SQL] --> SELECT tblMaxLoadContainerType.ContainerType FROM tblMaxLoadContainerType;

And the union query:

qryMaxLoadimport_Final

[SQL] --> SELECT [Forms]![frmMaxLoadImport]![cboManifest]+"_"+Str(Date()) AS Col_A, [Forms]![frmMaxLoadImport]![cboUserID] AS Col_B, "" AS Col_C, "" AS Col_D, "" AS Col_E, "" AS Col_F, "" AS Col_G, "" AS Col_H, 0 AS Col_I, "" AS Col_J, "" AS Col_K, "" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, "" AS Col_P
UNION
SELECT [Forms]![frmMaxLoadImport]![cboManifest]+"_"+Str(Date()) AS Col_A, [Forms]![frmMaxLoadImport]![cboContainerType] AS Col_B, 1 AS Col_C, "" AS Col_D, 0 AS Col_E, "" AS Col_F, "" AS Col_G, "" AS Col_H, "" AS Col_I, "" AS Col_J, "" AS Col_K, "" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, "" AS Col_P
UNION SELECT Replace([qryMaxLoadImport_Picklist-1]!Item," ","") AS Col_A, Forms!frmMaxLoadImport!cboManifest+"_"+Str(Date()) AS Col_B, 1 AS Col_C, 3 AS Col_D, "" AS Col_E, "" AS Col_F, [qryMaxLoadImport_Picklist-1].Qty AS Col_G, "" AS Col_H, tblRouteDetail.StopNumber AS Col_I, "" AS Col_J, "" AS Col_K, "Y" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, 0 AS Col_P
FROM [qryMaxLoadImport_Picklist-1] INNER JOIN tblRouteDetail ON [qryMaxLoadImport_Picklist-1].VendorID = tblRouteDetail.VendorID
GROUP BY Replace([qryMaxLoadImport_Picklist-1]!Item," ",""), Forms!frmMaxLoadImport!cboManifest+"_"+Str(Date()), 1, 3, "", "", [qryMaxLoadImport_Picklist-1].Qty, "", tblRouteDetail.StopNumber, "", "", "Y", "", "", "", 0, tblRouteDetail.RouteID
HAVING (((tblRouteDetail.StopNumber)<>"TTI") AND ((tblRouteDetail.RouteID)=[Forms]![frmMaxLoadImport]![cboManifest]))
ORDER BY tblRouteDetail.StopNumber, tblRouteDetail.RouteID;

I hope this helps.

Strat
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 125 total points
ID: 16947121
So you're trying to just add single rows to a query using a Union?
It's not dissimilar to adding an "All" option.
You can hack it by using a table - any table, that you *know* has records - selecting the top 1 record from that - but using none of its fields.

For example perhaps

SELECT TOP 1 [Forms]![frmMaxLoadImport]![cboManifest]+"_"+Str(Date()) AS Col_A, [Forms]![frmMaxLoadImport]![cboUserID] AS Col_B, "" AS Col_C, "" AS Col_D, "" AS Col_E, "" AS Col_F, "" AS Col_G, "" AS Col_H, 0 AS Col_I, "" AS Col_J, "" AS Col_K, "" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, "" AS Col_P
FROM tblRouteDetail
UNION
SELECT TOP 1 [Forms]![frmMaxLoadImport]![cboManifest]+"_"+Str(Date()) AS Col_A, [Forms]![frmMaxLoadImport]![cboContainerType] AS Col_B, 1 AS Col_C, "" AS Col_D, 0 AS Col_E, "" AS Col_F, "" AS Col_G, "" AS Col_H, "" AS Col_I, "" AS Col_J, "" AS Col_K, "" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, "" AS Col_P
FROM tblRouteDetail
UNION SELECT Replace([qryMaxLoadImport_Picklist-1]!Item," ","") AS Col_A, Forms!frmMaxLoadImport!cboManifest+"_"+Str(Date()) AS Col_B, 1 AS Col_C, 3 AS Col_D, "" AS Col_E, "" AS Col_F, [qryMaxLoadImport_Picklist-1].Qty AS Col_G, "" AS Col_H, tblRouteDetail.StopNumber AS Col_I, "" AS Col_J, "" AS Col_K, "Y" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, 0 AS Col_P
FROM [qryMaxLoadImport_Picklist-1] INNER JOIN tblRouteDetail ON [qryMaxLoadImport_Picklist-1].VendorID = tblRouteDetail.VendorID
GROUP BY Replace([qryMaxLoadImport_Picklist-1]!Item," ",""), Forms!frmMaxLoadImport!cboManifest+"_"+Str(Date()), 1, 3, "", "", [qryMaxLoadImport_Picklist-1].Qty, "", tblRouteDetail.StopNumber, "", "", "Y", "", "", "", 0, tblRouteDetail.RouteID
HAVING (((tblRouteDetail.StopNumber)<>"TTI") AND ((tblRouteDetail.RouteID)=[Forms]![frmMaxLoadImport]![cboManifest]))
ORDER BY tblRouteDetail.StopNumber, tblRouteDetail.RouteID;
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16947130
(And before anyone mentioned ties with a TOP N clause - you need to be sure you can select a top 1 of course lol - any real PK is usually enough though :-p)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16948169
It looks like you are using '+' as the concatination operator instead of '&'.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16948317
That's true - and though not ideal, probably not stopping the Union from running at all.
May return some Nulls you weren't expecting though (once it is running). ;-)
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 16949875
Could be that you need to specify UNION ALL:

UNION ALL
SELECT

/gustav
0
 

Author Comment

by:Stratocaster
ID: 16951124
Great. Slipping in the TOP 1 statement and adding the FROM tblRouteDetail got the UNION to work. One problem from that. I need the unioned queries to be in a certain order, but in order to get the query to run I had to move the last query to the top because of the ORDER BY clause. The other queries do not have that clause. So what I need is the order that I posted earlier.

Any Ideas?

Thanks,
Strat
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 16951181
Add a (fixed) field with the sort order - 1, 2 or 3 - to each query.
Then order by that as the first.

/gustav
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16951206
You can introduce another artifical field to order by... for example

SELECT TOP 1 [Forms]![frmMaxLoadImport]![cboManifest]+"_"+Str(Date()) AS Col_A, [Forms]![frmMaxLoadImport]![cboUserID] AS Col_B, "" AS Col_C, "" AS Col_D, "" AS Col_E, "" AS Col_F, "" AS Col_G, "" AS Col_H, 0 AS Col_I, "" AS Col_J, "" AS Col_K, "" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, "" AS Col_P, 0 AS MyOrder
FROM tblRouteDetail
UNION
SELECT TOP 1 [Forms]![frmMaxLoadImport]![cboManifest]+"_"+Str(Date()) AS Col_A, [Forms]![frmMaxLoadImport]![cboContainerType] AS Col_B, 1 AS Col_C, "" AS Col_D, 0 AS Col_E, "" AS Col_F, "" AS Col_G, "" AS Col_H, "" AS Col_I, "" AS Col_J, "" AS Col_K, "" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, "" AS Col_P, 1 AS MyOrder
FROM tblRouteDetail
UNION SELECT Replace([qryMaxLoadImport_Picklist-1]!Item," ","") AS Col_A, Forms!frmMaxLoadImport!cboManifest+"_"+Str(Date()) AS Col_B, 1 AS Col_C, 3 AS Col_D, "" AS Col_E, "" AS Col_F, [qryMaxLoadImport_Picklist-1].Qty AS Col_G, "" AS Col_H, tblRouteDetail.StopNumber AS Col_I, "" AS Col_J, "" AS Col_K, "Y" AS Col_L, "" AS Col_M, "" AS Col_N, "" AS Col_O, 0 AS Col_P, 2 AS MyOrder
FROM [qryMaxLoadImport_Picklist-1] INNER JOIN tblRouteDetail ON [qryMaxLoadImport_Picklist-1].VendorID = tblRouteDetail.VendorID
GROUP BY Replace([qryMaxLoadImport_Picklist-1]!Item," ",""), Forms!frmMaxLoadImport!cboManifest+"_"+Str(Date()), 1, 3, "", "", [qryMaxLoadImport_Picklist-1].Qty, "", tblRouteDetail.StopNumber, "", "", "Y", "", "", "", 0, tblRouteDetail.RouteID
HAVING (((tblRouteDetail.StopNumber)<>"TTI") AND ((tblRouteDetail.RouteID)=[Forms]![frmMaxLoadImport]![cboManifest]))
ORDER BY MyOrder, tblRouteDetail.StopNumber, tblRouteDetail.RouteID;

Ha - beaten to it slightly I see...
0
 

Author Comment

by:Stratocaster
ID: 16951245
Thanks guys. Have a great day!

--Stratocaster
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

728 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