Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

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

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
Stratocaster
Asked:
Stratocaster
  • 5
  • 4
  • 3
  • +2
1 Solution
 
Patrick MatthewsCommented:
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
 
StratocasterAuthor Commented:
Hey Patrick,
Thanks for the response. I'm not sure how to do that. Can you explain it a bit?

Thanks,
Strat
0
 
Patrick MatthewsCommented:
Stratocaster,

What is the source table/query for this form?

Regards,

Patrick
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
StratocasterAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
StratocasterAuthor Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
(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
 
GRayLCommented:
It looks like you are using '+' as the concatination operator instead of '&'.
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
Gustav BrockCIOCommented:
Could be that you need to specify UNION ALL:

UNION ALL
SELECT

/gustav
0
 
StratocasterAuthor Commented:
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
 
Gustav BrockCIOCommented:
Add a (fixed) field with the sort order - 1, 2 or 3 - to each query.
Then order by that as the first.

/gustav
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
StratocasterAuthor Commented:
Thanks guys. Have a great day!

--Stratocaster
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now