Expanding query

[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

 

[jahoobob]

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

I would like to generate a report by inputing a date range for
selection
criteria. The resulting records would contain not only the "Stops
that
occurred within the date range but also the stops that were related b
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
want
to see all all of the Stops that were made on those Orders. My repor
would
show all three records from the example even if the selection rang
were
limited to 1/2/05. It would show no records that didn't have at leas
one
stop on 1/2/05.

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

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

Thanks in advance.

toooldtolearn
but still trying

Create a report of your "parent" table and a sub-report of your "child
data. Place the sub-report in your report and link the two via th
Pronum by selecting the Poroperties of the sub-form as it appears i
the form in design view.. Your data should look something like this
Pronum 1111
StopNum Date
1 1/1/05
2 1/2/05
3 1/3/05

Pronum 1112
StopNum Date
1 1/1/05
2 1/4/05
3 1/5/05

If you have any questions, don't hesitate to ask.
Bo

 

[John Spencer (MVP)]

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#)

 

[TooOldToLearn via AccessMonster.com]

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

[quoted text clipped - 30 lines]

toooldtolearn
but still trying

 

[John Spencer (MVP)]

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])

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

[quoted text clipped - 30 lines]

toooldtolearn
but still trying