Solved

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

Posted on 2006-06-20
15
484 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 49

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 49

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

16 Experts available now in Live!

Get 1:1 Help Now