Always include certain records in query results

A

Alex

I have a query populating a combo box. The query contains joins to other
queries. Regardless of the results, I need two particular records to show in
the combo box. I've searched on this site and have created the following
union query. It works, but it's very slow. Is there a faster way that I
could do this? Thanks.

SELECT DISTINCT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
WHERE (((Right([PRODUCTIONORDERID],2))=[forms]![frm_qualitylog]![Work
Order]) AND ((Tm_production_orders.ITEM)=[forms]![frm_qualitylog]![Part #]))

UNION

Select DISTINCT "0", "LAYOUT",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber Order by tbl_Operation_New.OpNumber

UNION Select DISTINCT "1", "HANDLING",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
ORDER BY tbl_Operation_New.OpNumber;
 
D

Duane Hookom

I don't know why you need so many extra tables in your "certain records".

SELECT DISTINCT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
WHERE (((Right([PRODUCTIONORDERID],2))=[forms]![frm_qualitylog]![Work
Order]) AND ((Tm_production_orders.ITEM)=[forms]![frm_qualitylog]![Part #]))

UNION

Select "0", "LAYOUT", Null
FROM Tm_production_orders

UNION

Select "1", "HANDLING", Null
FROM Tm_production_orders;
 
A

Alex

Thanks Duane. Believe me, I tried not to add the extra tables, but I must
have been missing something because I could only get the query to work with
all the extras.

Where do I add ORDER BY tbl_Operation_New.OpNumber to get the correct sort?
I've tried adding it before and after each union and have been unsuccessful.
Thanks.

Duane Hookom said:
I don't know why you need so many extra tables in your "certain records".

SELECT DISTINCT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
WHERE (((Right([PRODUCTIONORDERID],2))=[forms]![frm_qualitylog]![Work
Order]) AND ((Tm_production_orders.ITEM)=[forms]![frm_qualitylog]![Part #]))

UNION

Select "0", "LAYOUT", Null
FROM Tm_production_orders

UNION

Select "1", "HANDLING", Null
FROM Tm_production_orders;

--
Duane Hookom
Microsoft Access MVP


Alex said:
I have a query populating a combo box. The query contains joins to other
queries. Regardless of the results, I need two particular records to show in
the combo box. I've searched on this site and have created the following
union query. It works, but it's very slow. Is there a faster way that I
could do this? Thanks.

SELECT DISTINCT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
WHERE (((Right([PRODUCTIONORDERID],2))=[forms]![frm_qualitylog]![Work
Order]) AND ((Tm_production_orders.ITEM)=[forms]![frm_qualitylog]![Part #]))

UNION

Select DISTINCT "0", "LAYOUT",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber Order by tbl_Operation_New.OpNumber

UNION Select DISTINCT "1", "HANDLING",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
ORDER BY tbl_Operation_New.OpNumber;
Click to expand...
Click to expand...
 
J

John Spencer

Do you have a table that is really small - only a few records in it. If so,
use it in your union query?

Next DROP the Distinct a UNION query will automatically do that with the
results of the subqueries.

Change the where clause to use Like instead of using the right function. It
should be a faster to do the scan with like than calling a vba function for
every record

SELECT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry
ON Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID)
INNER JOIN tbl_Operation_New
ON Tm_Op_Resourceqry.OperationNum = tbl_Operation_New.OpNumber
WHERE [PRODUCTIONORDERID] LIKE "*" & [forms]![frm_qualitylog]![Work Order]
AND Tm_production_orders.ITEM=[forms]![frm_qualitylog]![Part #]

UNION

Select "0", "LAYOUT",""
FROM ReallySmallTable

UNION Select "1", "HANDLING",""
FROM ReallySmallTable
ORDER BY OpNumber

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
A

Alex

Thanks John, it is much faster. I'm still having trouble sorting. The
OpNumber field is definitely a number field in both tables and sorts fine in
a select query, but in the union query it sorts 0, 1, 100, 2, 300, 310, which
would indicate that it's actually sorting text. How can I write it to sort
0, 1, 2, 100, 300, 310, etc. Thanks for your help.

John Spencer said:
Do you have a table that is really small - only a few records in it. If so,
use it in your union query?

Next DROP the Distinct a UNION query will automatically do that with the
results of the subqueries.

Change the where clause to use Like instead of using the right function. It
should be a faster to do the scan with like than calling a vba function for
every record

SELECT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry
ON Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID)
INNER JOIN tbl_Operation_New
ON Tm_Op_Resourceqry.OperationNum = tbl_Operation_New.OpNumber
WHERE [PRODUCTIONORDERID] LIKE "*" & [forms]![frm_qualitylog]![Work Order]
AND Tm_production_orders.ITEM=[forms]![frm_qualitylog]![Part #]

UNION

Select "0", "LAYOUT",""
FROM ReallySmallTable

UNION Select "1", "HANDLING",""
FROM ReallySmallTable
ORDER BY OpNumber

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a query populating a combo box. The query contains joins to other
queries. Regardless of the results, I need two particular records to show in
the combo box. I've searched on this site and have created the following
union query. It works, but it's very slow. Is there a faster way that I
could do this? Thanks.

SELECT DISTINCT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
WHERE (((Right([PRODUCTIONORDERID],2))=[forms]![frm_qualitylog]![Work
Order]) AND ((Tm_production_orders.ITEM)=[forms]![frm_qualitylog]![Part #]))

UNION

Select DISTINCT "0", "LAYOUT",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber Order by tbl_Operation_New.OpNumber

UNION Select DISTINCT "1", "HANDLING",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
ORDER BY tbl_Operation_New.OpNumber;
Click to expand...
Click to expand...
 
J

John Spencer

Change the union query a bit. Access saw you had numbers in the first
subquery and text (those pesky quotes) in the 2nd and 3rd subqueries.
So, it very kindly said - the only way I can handle this is to type all
the results for the column as text. If the third field is numeric ResUD
then you can replace the zero-length string with null to prevent it from
being changed to a string.


....

UNION

Select 0, "LAYOUT",""
FROM ReallySmallTable

UNION Select 1, "HANDLING",""
FROM ReallySmallTable
ORDER BY OpNumber


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks John, it is much faster. I'm still having trouble sorting. The
OpNumber field is definitely a number field in both tables and sorts fine in
a select query, but in the union query it sorts 0, 1, 100, 2, 300, 310, which
would indicate that it's actually sorting text. How can I write it to sort
0, 1, 2, 100, 300, 310, etc. Thanks for your help.

John Spencer said:
Do you have a table that is really small - only a few records in it. If so,
use it in your union query?

Next DROP the Distinct a UNION query will automatically do that with the
results of the subqueries.

Change the where clause to use Like instead of using the right function. It
should be a faster to do the scan with like than calling a vba function for
every record

SELECT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry
ON Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID)
INNER JOIN tbl_Operation_New
ON Tm_Op_Resourceqry.OperationNum = tbl_Operation_New.OpNumber
WHERE [PRODUCTIONORDERID] LIKE "*" & [forms]![frm_qualitylog]![Work Order]
AND Tm_production_orders.ITEM=[forms]![frm_qualitylog]![Part #]

UNION

Select "0", "LAYOUT",""
FROM ReallySmallTable

UNION Select "1", "HANDLING",""
FROM ReallySmallTable
ORDER BY OpNumber

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a query populating a combo box. The query contains joins to other
queries. Regardless of the results, I need two particular records to show in
the combo box. I've searched on this site and have created the following
union query. It works, but it's very slow. Is there a faster way that I
could do this? Thanks.

SELECT DISTINCT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
WHERE (((Right([PRODUCTIONORDERID],2))=[forms]![frm_qualitylog]![Work
Order]) AND ((Tm_production_orders.ITEM)=[forms]![frm_qualitylog]![Part #]))

UNION

Select DISTINCT "0", "LAYOUT",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber Order by tbl_Operation_New.OpNumber

UNION Select DISTINCT "1", "HANDLING",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
ORDER BY tbl_Operation_New.OpNumber;
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If OpNumber is numeric then why would you use:
SELECT "0",...
which creates a text column. Try change to
SELECT 0,"LAYOUT", Null

Don't mess change data types and I would consider using Null rather than "".

--
Duane Hookom
Microsoft Access MVP


Alex said:
Thanks John, it is much faster. I'm still having trouble sorting. The
OpNumber field is definitely a number field in both tables and sorts fine in
a select query, but in the union query it sorts 0, 1, 100, 2, 300, 310, which
would indicate that it's actually sorting text. How can I write it to sort
0, 1, 2, 100, 300, 310, etc. Thanks for your help.

John Spencer said:
Do you have a table that is really small - only a few records in it. If so,
use it in your union query?

Next DROP the Distinct a UNION query will automatically do that with the
results of the subqueries.

Change the where clause to use Like instead of using the right function. It
should be a faster to do the scan with like than calling a vba function for
every record

SELECT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry
ON Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID)
INNER JOIN tbl_Operation_New
ON Tm_Op_Resourceqry.OperationNum = tbl_Operation_New.OpNumber
WHERE [PRODUCTIONORDERID] LIKE "*" & [forms]![frm_qualitylog]![Work Order]
AND Tm_production_orders.ITEM=[forms]![frm_qualitylog]![Part #]

UNION

Select "0", "LAYOUT",""
FROM ReallySmallTable

UNION Select "1", "HANDLING",""
FROM ReallySmallTable
ORDER BY OpNumber

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a query populating a combo box. The query contains joins to other
queries. Regardless of the results, I need two particular records to show in
the combo box. I've searched on this site and have created the following
union query. It works, but it's very slow. Is there a faster way that I
could do this? Thanks.

SELECT DISTINCT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
WHERE (((Right([PRODUCTIONORDERID],2))=[forms]![frm_qualitylog]![Work
Order]) AND ((Tm_production_orders.ITEM)=[forms]![frm_qualitylog]![Part #]))

UNION

Select DISTINCT "0", "LAYOUT",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber Order by tbl_Operation_New.OpNumber

UNION Select DISTINCT "1", "HANDLING",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
ORDER BY tbl_Operation_New.OpNumber;
Click to expand...
Click to expand...
Click to expand...
 
A

Alex

Duh! Of course . . I missed it. Thank you very much Duane. Have a great
evening.

Duane Hookom said:
If OpNumber is numeric then why would you use:
SELECT "0",...
which creates a text column. Try change to
SELECT 0,"LAYOUT", Null

Don't mess change data types and I would consider using Null rather than "".

--
Duane Hookom
Microsoft Access MVP


Alex said:
Thanks John, it is much faster. I'm still having trouble sorting. The
OpNumber field is definitely a number field in both tables and sorts fine in
a select query, but in the union query it sorts 0, 1, 100, 2, 300, 310, which
would indicate that it's actually sorting text. How can I write it to sort
0, 1, 2, 100, 300, 310, etc. Thanks for your help.

John Spencer said:
Do you have a table that is really small - only a few records in it. If so,
use it in your union query?

Next DROP the Distinct a UNION query will automatically do that with the
results of the subqueries.

Change the where clause to use Like instead of using the right function. It
should be a faster to do the scan with like than calling a vba function for
every record

SELECT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry
ON Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID)
INNER JOIN tbl_Operation_New
ON Tm_Op_Resourceqry.OperationNum = tbl_Operation_New.OpNumber
WHERE [PRODUCTIONORDERID] LIKE "*" & [forms]![frm_qualitylog]![Work Order]
AND Tm_production_orders.ITEM=[forms]![frm_qualitylog]![Part #]

UNION

Select "0", "LAYOUT",""
FROM ReallySmallTable

UNION Select "1", "HANDLING",""
FROM ReallySmallTable
ORDER BY OpNumber

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Alex wrote:
I have a query populating a combo box. The query contains joins to other
queries. Regardless of the results, I need two particular records to show in
the combo box. I've searched on this site and have created the following
union query. It works, but it's very slow. Is there a faster way that I
could do this? Thanks.

SELECT DISTINCT tbl_Operation_New.OpNumber, tbl_Operation_New.Operation,
Tm_Op_Resourceqry.ResUD
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
WHERE (((Right([PRODUCTIONORDERID],2))=[forms]![frm_qualitylog]![Work
Order]) AND ((Tm_production_orders.ITEM)=[forms]![frm_qualitylog]![Part #]))

UNION

Select DISTINCT "0", "LAYOUT",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber Order by tbl_Operation_New.OpNumber

UNION Select DISTINCT "1", "HANDLING",""
FROM (Tm_production_orders INNER JOIN Tm_Op_Resourceqry ON
Tm_production_orders.ROUTINGID = Tm_Op_Resourceqry.ROUTINGID) INNER JOIN
tbl_Operation_New ON Tm_Op_Resourceqry.OperationNum =
tbl_Operation_New.OpNumber
ORDER BY tbl_Operation_New.OpNumber;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

perform INNER JOIN on query results 2
SELECT DISTINCT query 8
Another query too complex problem 3
Query not showing certain records 5
How to score matching results 2
Union Query Edit 1
What makes a query updateable? 2
SUM in a UNION query 2

Top