Expanding query

  • Thread starter Thread starter TooOldToLearn via AccessMonster.com
  • Start date Start date
T

TooOldToLearn via AccessMonster.com

I have a dispatch program that uses a parent/child query in which the child
records have date fields. The two tables are related by a common field
called ProNum and the child records are differentiated by a field called
StopNum. The child records may or may not have different date values for
each of the stops.

I would like to generate a report by inputing a date range for a selection
criteria. The resulting records would contain not only the "Stops" that
occurred within the date range but also the stops that were related by the
same Pronum.

For example:
Order. Pronum = 1111
Stop.Pronum = 1111 Stop.StopNum = 1 Stop.StopDate = 1/1/05
Stop.Pronum = 1111 Stop.StopNum = 2 Stop.StopDate = 1/2/05
Stop.Pronum = 1111 Stop.StopNum = 3 Stop.StopDate = 1/3/05

I want to see all Orders that have a Stop with the date of 1/2/05 and I want
to see all all of the Stops that were made on those Orders. My report would
show all three records from the example even if the selection range were
limited to 1/2/05. It would show no records that didn't have at least one
stop on 1/2/05.

I think what I need is to run a query that gets all of the ProNums that have
Stops with a matching date and then use the resulting list of ProNums to
fetch the rest of the stops.

Is there a way to use the "IN" clause to look at query results of the first
query and return all matching records?

Thanks in advance.

toooldtolearn
but still trying
 
The SQL statement would look something like the following.

SELECT Order.Pronum, Stop.StopNum, Stop.StopDate
FROM ORDER INNER JOIN STOP
ON Order.ProNum = Stop.ProNum
WHERE Order.Pronum IN
(SELECT Stop.Pronum
FROM Stop
WHERE Stop.StopDate = #1/2/05#)
 
John thanks for the help! That is what I was looking for and the query works
perfectly. Now...

This turns out to be a very, very long query with 6 files all interrelated.
I have it set up in a stored query but I would like to be able to pass the
"In (Select" etc. with the date range to the stored query via the report or
from code. Can this be accomplished with a stored query from vba code or do
I need to code the entire sql statement. I can't pass it to the report in
the docmd.openreport as a parameter because that effects the report and not
the query. I probably need to pass it to the stored query in the open event
of the report but I can't find any documentation on how to address the
existing stored query and set the criteria progamatically. Is this possible
or is there a better way.

Thanks again.

toooldtolearn
The SQL statement would look something like the following.

SELECT Order.Pronum, Stop.StopNum, Stop.StopDate
FROM ORDER INNER JOIN STOP
ON Order.ProNum = Stop.ProNum
WHERE Order.Pronum IN
(SELECT Stop.Pronum
FROM Stop
WHERE Stop.StopDate = #1/2/05#)
I have a dispatch program that uses a parent/child query in which the child
records have date fields. The two tables are related by a common field
Click to expand...
[quoted text clipped - 30 lines]
toooldtolearn
but still trying
Click to expand...
Click to expand...
 
IF the only thing you need to change is the DATE in the in clause, you can do
something simple like a parameter query or better yet, use a form to get the
date and reference the form's control (the form must be open).

Parameters [Forms]![YourFormName]![YourControlName] DateTime;
SELECT Order.Pronum, Stop.StopNum, Stop.StopDate
FROM ORDER INNER JOIN STOP
ON Order.ProNum = Stop.ProNum
WHERE Order.Pronum IN
(SELECT Stop.Pronum
FROM Stop
WHERE Stop.StopDate = [Forms]![YourFormName]![YourControlName])

TooOldToLearn via AccessMonster.com said:
John thanks for the help! That is what I was looking for and the query works
perfectly. Now...

This turns out to be a very, very long query with 6 files all interrelated.
I have it set up in a stored query but I would like to be able to pass the
"In (Select" etc. with the date range to the stored query via the report or
from code. Can this be accomplished with a stored query from vba code or do
I need to code the entire sql statement. I can't pass it to the report in
the docmd.openreport as a parameter because that effects the report and not
the query. I probably need to pass it to the stored query in the open event
of the report but I can't find any documentation on how to address the
existing stored query and set the criteria progamatically. Is this possible
or is there a better way.

Thanks again.

toooldtolearn
The SQL statement would look something like the following.

SELECT Order.Pronum, Stop.StopNum, Stop.StopDate
FROM ORDER INNER JOIN STOP
ON Order.ProNum = Stop.ProNum
WHERE Order.Pronum IN
(SELECT Stop.Pronum
FROM Stop
WHERE Stop.StopDate = #1/2/05#)
I have a dispatch program that uses a parent/child query in which the child
records have date fields. The two tables are related by a common field
Click to expand...
[quoted text clipped - 30 lines]
toooldtolearn
but still trying
Click to expand...
Click to expand...
Click to expand...
 
Back
Top