Solved

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

Posted on 2006-06-20
15
492 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add records to a form to a table 11 40
Search for text in a .txt file 14 44
Calculation in Access 5 26
MS Access from Delphi 31 29
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

773 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