parameter query prompting multiple times

[Dragon]

I'm having a problem with subreports based on parameter query, however, I
don't understand about the master child fields.

My report is set up with one subreport (that will only return one value) in
the page footer section, another 2 subreports (returning multiple values) are
setup in the report footer section. All are run off of parameter queries.
They all have the date (warehouse load or unload) is common. The report
currently prompts for each query 3 times to open the report.

Can I make this work for my situation?
Thanks,
Dragon

------------------------------------------------------------------------------

 

[Allen Browne]

Use the solution that Marshall gave you.
There is no simpler answer.

 

[Duane Hookom]

Don't use parameter prompts in queries. IMHO, they are not good user
interface http://www.tek-tips.com/faqs.cfm?fid=6763. Use controls on forms
for all user interaction.

 

[Clifford Bass]

Hi Dragon,

Are you defining the parameters in all of the queries? In SQL view it
would show on the top line, something like this:

PARAMETERS [Enter Parameter:] Text ( 255 );

Do the three subreports all have some concrete relation to the main data
in the report? Or, phrased another way, what, if any, are the master/child
linking fields between the main report and the subreports? I think there is
a way to cut it down to one prompt without resorting to the creation of a
parameter form. Not that that is a bad idea. How about posting the queries
for the report and the subreports. You can abbreviate them to show only the
pertinent fields if they are long.

Clifford Bass

 

[Dragon]

I guess the Master / child relationship would be the date. For the first
multiple record query below, as long as it can return the matching records
for the date entered and the future it could be the same date for each query.

Basically these queries are used to populate a report that shows the people
on the dock what loads they are going to be loading / unloading and loads
they're to assemble to be loaded on this or a future shift.

One of the multiple record queries:
SELECT [DC Ship].ShipDate, [DC Ship].[Unit #], [DC Ship].AssemblyEarnHrs,
[DC Ship].AssemblyShift
FROM [DC Ship]
WHERE ((([DC Ship].ShipDate)>[What is yesterdays date]) AND (([DC
Ship].AssemblyShift)=[What is the shift assembly designation]));

The other is:
SELECT Receiving.UnloadWarehouse, Receiving.DateofUnload, Receiving.Shift,
Receiving.[Unit#], Receiving.[Trailer#], Receiving.PlanStart
FROM Receiving
WHERE (((Receiving.UnloadWarehouse)=[What warehouse is this for]) AND
((Receiving.DateofUnload)=[What is the unload date]) AND
((Receiving.Shift)=[What shift]));

The single record query is now 1 of 2 like it. (the final forms got
re-designed)
the first:
SELECT [DC Ship].ShipDate, Sum([DC Ship].PlanEarnedHours) AS
SumOfPlanEarnedHours
FROM [DC Ship]
GROUP BY [DC Ship].ShipDate
HAVING ((([DC Ship].ShipDate)=[What is the date]));

The second:
SELECT [DC Ship].ShipDate, Sum([DC Ship].PlanEarnedHours) AS
SumOfPlanEarnedHours, [DC Ship].Shift
FROM [DC Ship]
GROUP BY [DC Ship].ShipDate, [DC Ship].Shift
HAVING ((([DC Ship].ShipDate)=[What is the date]) AND (([DC
Ship].Shift)=[What shift]));

I hope this helps make everything a little clearer.

Thanks,
Dragon

Hi Dragon,

Are you defining the parameters in all of the queries? In SQL view it
would show on the top line, something like this:

PARAMETERS [Enter Parameter:] Text ( 255 );

Do the three subreports all have some concrete relation to the main data
in the report? Or, phrased another way, what, if any, are the master/child
linking fields between the main report and the subreports? I think there is
a way to cut it down to one prompt without resorting to the creation of a
parameter form. Not that that is a bad idea. How about posting the queries
for the report and the subreports. You can abbreviate them to show only the
pertinent fields if they are long.

Clifford Bass

I'm having a problem with subreports based on parameter query, however, I
don't understand about the master child fields.

My report is set up with one subreport (that will only return one value) in
the page footer section, another 2 subreports (returning multiple values) are
setup in the report footer section. All are run off of parameter queries.
They all have the date (warehouse load or unload) is common. The report
currently prompts for each query 3 times to open the report.

Can I make this work for my situation?
Thanks,
Dragon

 

[Clifford Bass]

Hi Dragon,

If you are always printing for the today's date and into the future,
you could get rid of the prompting entirely. Use the Date() function
instead. In the first query it would become "...ShipDate)>=Date()...". In
the others "...=Date()...". However, if you are sometimes running the
reports one or more days in advance, a quick thing to try would be to make
all of the date parameters phrased the same. So maybe they would all be
"[Enter work date:]". If that does not quite work, then in each query define
the parameter also. This is done from the Show/Hide section of the Query
Tools, Design ribbon in Access 2007 or from Query menu in Access 2003 and
earlier. Also, if the date is usually today, but sometimes another day, you
can make it so the user does not have to enter anything when the date is
today. You would use Nz([Enter work date:], Date()).

Let me know if that does not do it. Question if it does not: Does the
report as a whole have a record source?

Clifford Bass

I guess the Master / child relationship would be the date. For the first
multiple record query below, as long as it can return the matching records
for the date entered and the future it could be the same date for each query.

Basically these queries are used to populate a report that shows the people
on the dock what loads they are going to be loading / unloading and loads
they're to assemble to be loaded on this or a future shift.

One of the multiple record queries:
SELECT [DC Ship].ShipDate, [DC Ship].[Unit #], [DC Ship].AssemblyEarnHrs,
[DC Ship].AssemblyShift
FROM [DC Ship]
WHERE ((([DC Ship].ShipDate)>[What is yesterdays date]) AND (([DC
Ship].AssemblyShift)=[What is the shift assembly designation]));

The other is:
SELECT Receiving.UnloadWarehouse, Receiving.DateofUnload, Receiving.Shift,
Receiving.[Unit#], Receiving.[Trailer#], Receiving.PlanStart
FROM Receiving
WHERE (((Receiving.UnloadWarehouse)=[What warehouse is this for]) AND
((Receiving.DateofUnload)=[What is the unload date]) AND
((Receiving.Shift)=[What shift]));

The single record query is now 1 of 2 like it. (the final forms got
re-designed)
the first:
SELECT [DC Ship].ShipDate, Sum([DC Ship].PlanEarnedHours) AS
SumOfPlanEarnedHours
FROM [DC Ship]
GROUP BY [DC Ship].ShipDate
HAVING ((([DC Ship].ShipDate)=[What is the date]));

The second:
SELECT [DC Ship].ShipDate, Sum([DC Ship].PlanEarnedHours) AS
SumOfPlanEarnedHours, [DC Ship].Shift
FROM [DC Ship]
GROUP BY [DC Ship].ShipDate, [DC Ship].Shift
HAVING ((([DC Ship].ShipDate)=[What is the date]) AND (([DC
Ship].Shift)=[What shift]));

I hope this helps make everything a little clearer.

Thanks,
Dragon

Hi Dragon,

Are you defining the parameters in all of the queries? In SQL view it
would show on the top line, something like this:

PARAMETERS [Enter Parameter:] Text ( 255 );

Do the three subreports all have some concrete relation to the main data
in the report? Or, phrased another way, what, if any, are the master/child
linking fields between the main report and the subreports? I think there is
a way to cut it down to one prompt without resorting to the creation of a
parameter form. Not that that is a bad idea. How about posting the queries
for the report and the subreports. You can abbreviate them to show only the
pertinent fields if they are long.

Clifford Bass

I'm having a problem with subreports based on parameter query, however, I
don't understand about the master child fields.

My report is set up with one subreport (that will only return one value) in
the page footer section, another 2 subreports (returning multiple values) are
setup in the report footer section. All are run off of parameter queries.
They all have the date (warehouse load or unload) is common. The report
currently prompts for each query 3 times to open the report.

Can I make this work for my situation?
Thanks,
Dragon