Always include certain records in query results

  • Thread starter Thread starter Alex
  • Start date Start date
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;
 
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;
 
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...
 
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top