Using a SELECT query as the rowsoruce for another SELECT

D

dch3

The SUB QUERY below has been saved as 'qry_Loading_Trailers' referenced in
the MAIN QUERY. The MAIN QUERY is destined for the row source of a combo box.
Given the nature of the data being worked with I would prefer NOT to have
either query exposed to any snooping users. How would I alter the main query
to embed the actual SQL Statement of the other.

'MAIN QUERY
SELECT tblTrailerInventory.txtTrailerDOTNumber, qry_Loading_Trailers.[DOT
Number]
FROM tblTrailerInventory LEFT JOIN qry_Loading_Trailers ON
tblTrailerInventory.txtTrailerDOTNumber = qry_Loading_Trailers.[DOT Number]
WHERE (((qry_Loading_Trailers.[DOT Number]) Is Null))
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;

'SUB QUERY
SELECT DISTINCTROW tblTrailerInventory.txtTrailerDOTNumber AS [DOT Number],
tblTrailerInventory.txtTrailerInternalNumber AS [Internal Number]
FROM tblTrailerInventory LEFT JOIN tblTrailerActivityHeaders ON
tblTrailerInventory.txtTrailerDOTNumber =
tblTrailerActivityHeaders.txtTrailerDOTNumber
WHERE (((tblTrailerActivityHeaders.txtTrailerLoadStatus)="Loading")) OR
(((tblTrailerActivityHeaders.txtTrailerLoadStatus)="Ready for Dispatch") AND
((tblTrailerActivityHeaders.txtTrailerDispatchStatus)="Ready for Dispatch"))
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;
 
J

John Spencer

You can try the following. You will need to make a small change to your
naming convention for this to work. IN Access, an embedded subquery in a FROM
clause cannot use any square brackets.

'MAIN QUERY
SELECT tblTrailerInventory.txtTrailerDOTNumber, Q.[DOT_Number]
FROM tblTrailerInventory LEFT JOIN
(SELECT DISTINCTROW I.txtTrailerDOTNumber AS DOT_Number,
I.txtTrailerInternalNumber AS Internal_Number
FROM tblTrailerInventory as I
LEFT JOIN tblTrailerActivityHeaders As H ON
I.txtTrailerDOTNumber = H.txtTrailerDOTNumber
WHERE (H.txtTrailerLoadStatus="Loading") OR
(H.txtTrailerLoadStatus="Ready for Dispatch" AND
H.txtTrailerDispatchStatus="Ready for Dispatch")) as Q
ON tblTrailerInventory.txtTrailerDOTNumber = Q.DOT_Number
WHERE Q.DOT_Number Is Null
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;

I don't know why you are including the Internal Number in the subquery as it
doesn't seem to be needed in this query. Also I see no reason to include
DISTINCT ROW. I left them in, but I think they both could be dropped for the
purposes of this query.

And to really simplify, I see no reason (for this query) to even have
tblTrailerInventory in the subquery. So I think your final query could be as
simple as the following.

SELECT tblTrailerInventory.txtTrailerDOTNumber, Q.[DOT_Number]
FROM tblTrailerInventory LEFT JOIN
(SELECT H.txtTrailerDOTNumber AS DOT_Number
FROM tblTrailerActivityHeaders As H
WHERE (H.txtTrailerLoadStatus="Loading") OR
(H.txtTrailerLoadStatus="Ready for Dispatch" AND
H.txtTrailerDispatchStatus="Ready for Dispatch")) as Q
ON tblTrailerInventory.txtTrailerDOTNumber = Q.DOT_Number
WHERE Q.DOT_Number Is Null
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
The SUB QUERY below has been saved as 'qry_Loading_Trailers' referenced in
the MAIN QUERY. The MAIN QUERY is destined for the row source of a combo box.
Given the nature of the data being worked with I would prefer NOT to have
either query exposed to any snooping users. How would I alter the main query
to embed the actual SQL Statement of the other.

'MAIN QUERY
SELECT tblTrailerInventory.txtTrailerDOTNumber, qry_Loading_Trailers.[DOT
Number]
FROM tblTrailerInventory LEFT JOIN qry_Loading_Trailers ON
tblTrailerInventory.txtTrailerDOTNumber = qry_Loading_Trailers.[DOT Number]
WHERE (((qry_Loading_Trailers.[DOT Number]) Is Null))
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;

'SUB QUERY
SELECT DISTINCTROW tblTrailerInventory.txtTrailerDOTNumber AS [DOT Number],
tblTrailerInventory.txtTrailerInternalNumber AS [Internal Number]
FROM tblTrailerInventory LEFT JOIN tblTrailerActivityHeaders ON
tblTrailerInventory.txtTrailerDOTNumber =
tblTrailerActivityHeaders.txtTrailerDOTNumber
WHERE (((tblTrailerActivityHeaders.txtTrailerLoadStatus)="Loading")) OR
(((tblTrailerActivityHeaders.txtTrailerLoadStatus)="Ready for Dispatch") AND
((tblTrailerActivityHeaders.txtTrailerDispatchStatus)="Ready for Dispatch"))
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;
Click to expand...
 
D

dch3

The purpose of the database is to track trailers (as in 53"
tractor-trailers). Each trailer has a Department of Transportation number as
well as a three-digit alternate number. Depending on the user, the DOT number
may be entered or the alternate number, hence both need to be presented - its
for a listbox.

I have the DISTINCTROW in place because there are two tables invovled
(tblTrailerInventory and tblTrailerActivity) that are in a one-to-many
relationship and need to eliminate duplicates in the results. (Why or why
can't I run away from my past in resort operations? Its a reservation system
all over again.)

Anyways, thx for the help.


John Spencer said:
You can try the following. You will need to make a small change to your
naming convention for this to work. IN Access, an embedded subquery in a FROM
clause cannot use any square brackets.

'MAIN QUERY
SELECT tblTrailerInventory.txtTrailerDOTNumber, Q.[DOT_Number]
FROM tblTrailerInventory LEFT JOIN
(SELECT DISTINCTROW I.txtTrailerDOTNumber AS DOT_Number,
I.txtTrailerInternalNumber AS Internal_Number
FROM tblTrailerInventory as I
LEFT JOIN tblTrailerActivityHeaders As H ON
I.txtTrailerDOTNumber = H.txtTrailerDOTNumber
WHERE (H.txtTrailerLoadStatus="Loading") OR
(H.txtTrailerLoadStatus="Ready for Dispatch" AND
H.txtTrailerDispatchStatus="Ready for Dispatch")) as Q
ON tblTrailerInventory.txtTrailerDOTNumber = Q.DOT_Number
WHERE Q.DOT_Number Is Null
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;

I don't know why you are including the Internal Number in the subquery as it
doesn't seem to be needed in this query. Also I see no reason to include
DISTINCT ROW. I left them in, but I think they both could be dropped for the
purposes of this query.

And to really simplify, I see no reason (for this query) to even have
tblTrailerInventory in the subquery. So I think your final query could be as
simple as the following.

SELECT tblTrailerInventory.txtTrailerDOTNumber, Q.[DOT_Number]
FROM tblTrailerInventory LEFT JOIN
(SELECT H.txtTrailerDOTNumber AS DOT_Number
FROM tblTrailerActivityHeaders As H
WHERE (H.txtTrailerLoadStatus="Loading") OR
(H.txtTrailerLoadStatus="Ready for Dispatch" AND
H.txtTrailerDispatchStatus="Ready for Dispatch")) as Q
ON tblTrailerInventory.txtTrailerDOTNumber = Q.DOT_Number
WHERE Q.DOT_Number Is Null
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
The SUB QUERY below has been saved as 'qry_Loading_Trailers' referenced in
the MAIN QUERY. The MAIN QUERY is destined for the row source of a combo box.
Given the nature of the data being worked with I would prefer NOT to have
either query exposed to any snooping users. How would I alter the main query
to embed the actual SQL Statement of the other.

'MAIN QUERY
SELECT tblTrailerInventory.txtTrailerDOTNumber, qry_Loading_Trailers.[DOT
Number]
FROM tblTrailerInventory LEFT JOIN qry_Loading_Trailers ON
tblTrailerInventory.txtTrailerDOTNumber = qry_Loading_Trailers.[DOT Number]
WHERE (((qry_Loading_Trailers.[DOT Number]) Is Null))
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;

'SUB QUERY
SELECT DISTINCTROW tblTrailerInventory.txtTrailerDOTNumber AS [DOT Number],
tblTrailerInventory.txtTrailerInternalNumber AS [Internal Number]
FROM tblTrailerInventory LEFT JOIN tblTrailerActivityHeaders ON
tblTrailerInventory.txtTrailerDOTNumber =
tblTrailerActivityHeaders.txtTrailerDOTNumber
WHERE (((tblTrailerActivityHeaders.txtTrailerLoadStatus)="Loading")) OR
(((tblTrailerActivityHeaders.txtTrailerLoadStatus)="Ready for Dispatch") AND
((tblTrailerActivityHeaders.txtTrailerDispatchStatus)="Ready for Dispatch"))
ORDER BY tblTrailerInventory.txtTrailerDOTNumber;
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

Lastest 3 dates for each part number, subquery? 7
Report Query Difficulties 11
Field is being truncated after simple query is run, even though nogrouping is taking place 1
Update query not working 2
Can inner join be done on queries? 8
Average in a query after a number of rows 1
Gaps in ordered series 1
Going Crazy! Form Datasheet does not reflect changes to SQL query 4

Top