Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-06-20
15
Medium Priority
?
526 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 93

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 93

Expert Comment

by:Patrick Matthews
ID: 16944760
Stratocaster,

What is the source table/query for this form?

Regards,

Patrick
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 93

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

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 52

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

609 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