Multiple requests for parameter value

  • Thread starter Thread starter howie
  • Start date Start date
H

howie

I have a report that has a couple of subreports. The main report and
subreports both need a formID value asked for from the user. I have linked
the child and master fields in the subreports, but I'm still getting requests
for the value. How do I fix that?
 
If each of those reports (main, each of the subreports) use underlying
queries, and each of those underlying queries need the FormID, then each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Ok yeah, that's the way that I was doing it. How can I make it where the user
would only get prompted once and still get all the required information?
 
Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter], [txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice touch
would be to have the form also go invisible as it needs to stay open for the
report to run.

Now the magic part! Put something like below in the query criteria as needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters, it
will be on the form. If you need to run it again, the stuff will stay there
as the form is closed. You could even get fancy with things like a date
picker, combo box, or list of possible criteria from the tables in question.
 
Thanks Jerry! That sounds great. But I want to get real fancy with it and
make that form so that the user can choose from a combo box, fields like
name, date or formid and then provide that data and the report will be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for someone else
at my job, and they don't like that all the information is tied to the formID
(they don't understand how relational databases work) so they want to be able
to search based on the name, the date, the job task, the division, or the job
task. Is there anyway to do that? Because there is the possibility that they
can get multiple sets of results from searching for something besides the
formID. So I didn't know if I would be able to allow them to look at the
report without knowing the formID of the set of data they want to fill the
report.
--
howie


Jerry Whittle said:
Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter], [txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice touch
would be to have the form also go invisible as it needs to stay open for the
report to run.

Now the magic part! Put something like below in the query criteria as needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters, it
will be on the form. If you need to run it again, the stuff will stay there
as the form is closed. You could even get fancy with things like a date
picker, combo box, or list of possible criteria from the tables in question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


howie said:
Ok yeah, that's the way that I was doing it. How can I make it where the user
would only get prompted once and still get all the required information?
Click to expand...
Click to expand...
 
Howie

Another option would be to create a "flattening" query that joins all that
related information together, then give the user a way to look up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn "relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Thanks Jerry! That sounds great. But I want to get real fancy with it and
make that form so that the user can choose from a combo box, fields like
name, date or formid and then provide that data and the report will be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for someone
else
at my job, and they don't like that all the information is tied to the
formID
(they don't understand how relational databases work) so they want to be
able
to search based on the name, the date, the job task, the division, or the
job
task. Is there anyway to do that? Because there is the possibility that
they
can get multiple sets of results from searching for something besides the
formID. So I didn't know if I would be able to allow them to look at the
report without knowing the formID of the set of data they want to fill the
report.
--
howie


Jerry Whittle said:
Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice touch
would be to have the form also go invisible as it needs to stay open for
the
report to run.

Now the magic part! Put something like below in the query criteria as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters, it
will be on the form. If you need to run it again, the stuff will stay
there
as the form is closed. You could even get fancy with things like a date
picker, combo box, or list of possible criteria from the tables in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


howie said:
Ok yeah, that's the way that I was doing it. How can I make it where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports) use
underlying
queries, and each of those underlying queries need the FormID, then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that has a couple of subreports. The main report and
subreports both need a formID value asked for from the user. I have
linked
the child and master fields in the subreports, but I'm still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
Jeff,

Can you explain a little bit what you mean? Especially a "flattening" query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up say the
formID using some other piece of data?
--
howie


Jeff Boyce said:
Howie

Another option would be to create a "flattening" query that joins all that
related information together, then give the user a way to look up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn "relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Thanks Jerry! That sounds great. But I want to get real fancy with it and
make that form so that the user can choose from a combo box, fields like
name, date or formid and then provide that data and the report will be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for someone
else
at my job, and they don't like that all the information is tied to the
formID
(they don't understand how relational databases work) so they want to be
able
to search based on the name, the date, the job task, the division, or the
job
task. Is there anyway to do that? Because there is the possibility that
they
can get multiple sets of results from searching for something besides the
formID. So I didn't know if I would be able to allow them to look at the
report without knowing the formID of the set of data they want to fill the
report.
--
howie


Jerry Whittle said:
Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice touch
would be to have the form also go invisible as it needs to stay open for
the
report to run.

Now the magic part! Put something like below in the query criteria as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters, it
will be on the form. If you need to run it again, the stuff will stay
there
as the form is closed. You could even get fancy with things like a date
picker, combo box, or list of possible criteria from the tables in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Ok yeah, that's the way that I was doing it. How can I make it where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports) use
underlying
queries, and each of those underlying queries need the FormID, then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that has a couple of subreports. The main report and
subreports both need a formID value asked for from the user. I have
linked
the child and master fields in the subreports, but I'm still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
I'm suggesting not requiring the user to ever know or see a FormID.

If you join together all the tables that have related information (in a
query), then give the user a way to search against that query, s/he should
be able to find the information without ever touching a FormID.

(a "flattening" query ... that may not be a real term ... joins all the
tables together, so the user sees something like what you'd have if you
lined up all the data in a wide spreadsheet. The term "flat" is a
distinction from what you have with a relational design (deep, not wide))

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Jeff,

Can you explain a little bit what you mean? Especially a "flattening"
query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up say the
formID using some other piece of data?
--
howie


Jeff Boyce said:
Howie

Another option would be to create a "flattening" query that joins all
that
related information together, then give the user a way to look up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn "relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Thanks Jerry! That sounds great. But I want to get real fancy with it
and
make that form so that the user can choose from a combo box, fields
like
name, date or formid and then provide that data and the report will be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for someone
else
at my job, and they don't like that all the information is tied to the
formID
(they don't understand how relational databases work) so they want to
be
able
to search based on the name, the date, the job task, the division, or
the
job
task. Is there anyway to do that? Because there is the possibility that
they
can get multiple sets of results from searching for something besides
the
formID. So I didn't know if I would be able to allow them to look at
the
report without knowing the formID of the set of data they want to fill
the
report.
--
howie


:

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice
touch
would be to have the form also go invisible as it needs to stay open
for
the
report to run.

Now the magic part! Put something like below in the query criteria as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters,
it
will be on the form. If you need to run it again, the stuff will stay
there
as the form is closed. You could even get fancy with things like a
date
picker, combo box, or list of possible criteria from the tables in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Ok yeah, that's the way that I was doing it. How can I make it where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports) use
underlying
queries, and each of those underlying queries need the FormID,
then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that has a couple of subreports. The main report
and
subreports both need a formID value asked for from the user. I
have
linked
the child and master fields in the subreports, but I'm still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
I see what you mean now.

How would I deal with the one-to-many relationships though? I think i've
written a query like this before, maybe it wasn't flattening as much as
selecting everything but it turned out like below. Every time I had more than
one entry in a table, the whole set of data for that form id would be printed
for each different hazard, and so on for each of the other tables.

Example:
FormID Name Job Task Hazard Sub-Task
1 Bob something falling objects Climbing
Ladder
1 Bob something flying objects Climbing
Ladder
1 Bob something falling objects manual
lifting
1 Bob something flying objects manual
lifting

The cross product of all the tables would be huge even with a relatively
small number entries.
--
howie


Jeff Boyce said:
I'm suggesting not requiring the user to ever know or see a FormID.

If you join together all the tables that have related information (in a
query), then give the user a way to search against that query, s/he should
be able to find the information without ever touching a FormID.

(a "flattening" query ... that may not be a real term ... joins all the
tables together, so the user sees something like what you'd have if you
lined up all the data in a wide spreadsheet. The term "flat" is a
distinction from what you have with a relational design (deep, not wide))

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Jeff,

Can you explain a little bit what you mean? Especially a "flattening"
query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up say the
formID using some other piece of data?
--
howie


Jeff Boyce said:
Howie

Another option would be to create a "flattening" query that joins all
that
related information together, then give the user a way to look up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn "relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jerry! That sounds great. But I want to get real fancy with it
and
make that form so that the user can choose from a combo box, fields
like
name, date or formid and then provide that data and the report will be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for someone
else
at my job, and they don't like that all the information is tied to the
formID
(they don't understand how relational databases work) so they want to
be
able
to search based on the name, the date, the job task, the division, or
the
job
task. Is there anyway to do that? Because there is the possibility that
they
can get multiple sets of results from searching for something besides
the
formID. So I didn't know if I would be able to allow them to look at
the
report without knowing the formID of the set of data they want to fill
the
report.
--
howie


:

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice
touch
would be to have the form also go invisible as it needs to stay open
for
the
report to run.

Now the magic part! Put something like below in the query criteria as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters,
it
will be on the form. If you need to run it again, the stuff will stay
there
as the form is closed. You could even get fancy with things like a
date
picker, combo box, or list of possible criteria from the tables in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Ok yeah, that's the way that I was doing it. How can I make it where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports) use
underlying
queries, and each of those underlying queries need the FormID,
then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that has a couple of subreports. The main report
and
subreports both need a formID value asked for from the user. I
have
linked
the child and master fields in the subreports, but I'm still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
Because it would "flatten" the data, a one-to-many relationship would
'repeat' the one information for each of the many rows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
I see what you mean now.

How would I deal with the one-to-many relationships though? I think i've
written a query like this before, maybe it wasn't flattening as much as
selecting everything but it turned out like below. Every time I had more
than
one entry in a table, the whole set of data for that form id would be
printed
for each different hazard, and so on for each of the other tables.

Example:
FormID Name Job Task Hazard Sub-Task
1 Bob something falling objects
Climbing
Ladder
1 Bob something flying objects
Climbing
Ladder
1 Bob something falling objects manual
lifting
1 Bob something flying objects manual
lifting

The cross product of all the tables would be huge even with a relatively
small number entries.
--
howie


Jeff Boyce said:
I'm suggesting not requiring the user to ever know or see a FormID.

If you join together all the tables that have related information (in a
query), then give the user a way to search against that query, s/he
should
be able to find the information without ever touching a FormID.

(a "flattening" query ... that may not be a real term ... joins all the
tables together, so the user sees something like what you'd have if you
lined up all the data in a wide spreadsheet. The term "flat" is a
distinction from what you have with a relational design (deep, not wide))

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Jeff,

Can you explain a little bit what you mean? Especially a "flattening"
query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up say
the
formID using some other piece of data?
--
howie


:

Howie

Another option would be to create a "flattening" query that joins all
that
related information together, then give the user a way to look up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn
"relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jerry! That sounds great. But I want to get real fancy with
it
and
make that form so that the user can choose from a combo box, fields
like
name, date or formid and then provide that data and the report will
be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for
someone
else
at my job, and they don't like that all the information is tied to
the
formID
(they don't understand how relational databases work) so they want
to
be
able
to search based on the name, the date, the job task, the division,
or
the
job
task. Is there anyway to do that? Because there is the possibility
that
they
can get multiple sets of results from searching for something
besides
the
formID. So I didn't know if I would be able to allow them to look at
the
report without knowing the formID of the set of data they want to
fill
the
report.
--
howie


:

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice
touch
would be to have the form also go invisible as it needs to stay
open
for
the
report to run.

Now the magic part! Put something like below in the query criteria
as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for
parameters,
it
will be on the form. If you need to run it again, the stuff will
stay
there
as the form is closed. You could even get fancy with things like a
date
picker, combo box, or list of possible criteria from the tables in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Ok yeah, that's the way that I was doing it. How can I make it
where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports) use
underlying
queries, and each of those underlying queries need the FormID,
then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that has a couple of subreports. The main
report
and
subreports both need a formID value asked for from the user.
I
have
linked
the child and master fields in the subreports, but I'm still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
Can you give me an example of a "flattening" query?
--
howie


Jeff Boyce said:
Because it would "flatten" the data, a one-to-many relationship would
'repeat' the one information for each of the many rows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
I see what you mean now.

How would I deal with the one-to-many relationships though? I think i've
written a query like this before, maybe it wasn't flattening as much as
selecting everything but it turned out like below. Every time I had more
than
one entry in a table, the whole set of data for that form id would be
printed
for each different hazard, and so on for each of the other tables.

Example:
FormID Name Job Task Hazard Sub-Task
1 Bob something falling objects
Climbing
Ladder
1 Bob something flying objects
Climbing
Ladder
1 Bob something falling objects manual
lifting
1 Bob something flying objects manual
lifting

The cross product of all the tables would be huge even with a relatively
small number entries.
--
howie


Jeff Boyce said:
I'm suggesting not requiring the user to ever know or see a FormID.

If you join together all the tables that have related information (in a
query), then give the user a way to search against that query, s/he
should
be able to find the information without ever touching a FormID.

(a "flattening" query ... that may not be a real term ... joins all the
tables together, so the user sees something like what you'd have if you
lined up all the data in a wide spreadsheet. The term "flat" is a
distinction from what you have with a relational design (deep, not wide))

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Can you explain a little bit what you mean? Especially a "flattening"
query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up say
the
formID using some other piece of data?
--
howie


:

Howie

Another option would be to create a "flattening" query that joins all
that
related information together, then give the user a way to look up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn
"relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jerry! That sounds great. But I want to get real fancy with
it
and
make that form so that the user can choose from a combo box, fields
like
name, date or formid and then provide that data and the report will
be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for
someone
else
at my job, and they don't like that all the information is tied to
the
formID
(they don't understand how relational databases work) so they want
to
be
able
to search based on the name, the date, the job task, the division,
or
the
job
task. Is there anyway to do that? Because there is the possibility
that
they
can get multiple sets of results from searching for something
besides
the
formID. So I didn't know if I would be able to allow them to look at
the
report without knowing the formID of the set of data they want to
fill
the
report.
--
howie


:

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice
touch
would be to have the form also go invisible as it needs to stay
open
for
the
report to run.

Now the magic part! Put something like below in the query criteria
as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for
parameters,
it
will be on the form. If you need to run it again, the stuff will
stay
there
as the form is closed. You could even get fancy with things like a
date
picker, combo box, or list of possible criteria from the tables in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Ok yeah, that's the way that I was doing it. How can I make it
where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports) use
underlying
queries, and each of those underlying queries need the FormID,
then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that has a couple of subreports. The main
report
and
subreports both need a formID value asked for from the user.
I
have
linked
the child and master fields in the subreports, but I'm still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
Let's trade ...

You give me a description of a couple of tables related one-to-many from
your domain, I'll take a stab at a query that provides the user all
information from both tables.

For example, a school registration db might have:

tblStudent
StudentID
FName
LName

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Can you give me an example of a "flattening" query?
--
howie


Jeff Boyce said:
Because it would "flatten" the data, a one-to-many relationship would
'repeat' the one information for each of the many rows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
I see what you mean now.

How would I deal with the one-to-many relationships though? I think
i've
written a query like this before, maybe it wasn't flattening as much as
selecting everything but it turned out like below. Every time I had
more
than
one entry in a table, the whole set of data for that form id would be
printed
for each different hazard, and so on for each of the other tables.

Example:
FormID Name Job Task Hazard Sub-Task
1 Bob something falling objects
Climbing
Ladder
1 Bob something flying objects
Climbing
Ladder
1 Bob something falling objects
manual
lifting
1 Bob something flying objects
manual
lifting

The cross product of all the tables would be huge even with a
relatively
small number entries.
--
howie


:

I'm suggesting not requiring the user to ever know or see a FormID.

If you join together all the tables that have related information (in
a
query), then give the user a way to search against that query, s/he
should
be able to find the information without ever touching a FormID.

(a "flattening" query ... that may not be a real term ... joins all
the
tables together, so the user sees something like what you'd have if
you
lined up all the data in a wide spreadsheet. The term "flat" is a
distinction from what you have with a relational design (deep, not
wide))

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Can you explain a little bit what you mean? Especially a
"flattening"
query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up say
the
formID using some other piece of data?
--
howie


:

Howie

Another option would be to create a "flattening" query that joins
all
that
related information together, then give the user a way to look up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn
"relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jerry! That sounds great. But I want to get real fancy
with
it
and
make that form so that the user can choose from a combo box,
fields
like
name, date or formid and then provide that data and the report
will
be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for
someone
else
at my job, and they don't like that all the information is tied
to
the
formID
(they don't understand how relational databases work) so they
want
to
be
able
to search based on the name, the date, the job task, the
division,
or
the
job
task. Is there anyway to do that? Because there is the
possibility
that
they
can get multiple sets of results from searching for something
besides
the
formID. So I didn't know if I would be able to allow them to look
at
the
report without knowing the formID of the set of data they want to
fill
the
report.
--
howie


:

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A
nice
touch
would be to have the form also go invisible as it needs to stay
open
for
the
report to run.

Now the magic part! Put something like below in the query
criteria
as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for
parameters,
it
will be on the form. If you need to run it again, the stuff will
stay
there
as the form is closed. You could even get fancy with things like
a
date
picker, combo box, or list of possible criteria from the tables
in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle
Builder.


:

Ok yeah, that's the way that I was doing it. How can I make it
where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports) use
underlying
queries, and each of those underlying queries need the
FormID,
then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that has a couple of subreports. The main
report
and
subreports both need a formID value asked for from the
user.
I
have
linked
the child and master fields in the subreports, but I'm
still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
Fair enough...

jobTbl
formID
Date
Name
jobDescript

subTasksTbl
formID
taskID

hazardsTbl
formID
hazardID
Description

hazmatsTbl
formID
hazmatID
productList
MSDS

ok, here's a sample of the database that I am creating. For every one row in
jobTbl there can be many rows in subTasksTbl, hazardsTbl, and hazmatsTbl.

Thanks Jeff
--
howie


Jeff Boyce said:
Let's trade ...

You give me a description of a couple of tables related one-to-many from
your domain, I'll take a stab at a query that provides the user all
information from both tables.

For example, a school registration db might have:

tblStudent
StudentID
FName
LName

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Can you give me an example of a "flattening" query?
--
howie


Jeff Boyce said:
Because it would "flatten" the data, a one-to-many relationship would
'repeat' the one information for each of the many rows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I see what you mean now.

How would I deal with the one-to-many relationships though? I think
i've
written a query like this before, maybe it wasn't flattening as much as
selecting everything but it turned out like below. Every time I had
more
than
one entry in a table, the whole set of data for that form id would be
printed
for each different hazard, and so on for each of the other tables.

Example:
FormID Name Job Task Hazard Sub-Task
1 Bob something falling objects
Climbing
Ladder
1 Bob something flying objects
Climbing
Ladder
1 Bob something falling objects
manual
lifting
1 Bob something flying objects
manual
lifting

The cross product of all the tables would be huge even with a
relatively
small number entries.
--
howie


:

I'm suggesting not requiring the user to ever know or see a FormID.

If you join together all the tables that have related information (in
a
query), then give the user a way to search against that query, s/he
should
be able to find the information without ever touching a FormID.

(a "flattening" query ... that may not be a real term ... joins all
the
tables together, so the user sees something like what you'd have if
you
lined up all the data in a wide spreadsheet. The term "flat" is a
distinction from what you have with a relational design (deep, not
wide))

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Can you explain a little bit what you mean? Especially a
"flattening"
query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up say
the
formID using some other piece of data?
--
howie


:

Howie

Another option would be to create a "flattening" query that joins
all
that
related information together, then give the user a way to look up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn
"relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jerry! That sounds great. But I want to get real fancy
with
it
and
make that form so that the user can choose from a combo box,
fields
like
name, date or formid and then provide that data and the report
will
be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for
someone
else
at my job, and they don't like that all the information is tied
to
the
formID
(they don't understand how relational databases work) so they
want
to
be
able
to search based on the name, the date, the job task, the
division,
or
the
job
task. Is there anyway to do that? Because there is the
possibility
that
they
can get multiple sets of results from searching for something
besides
the
formID. So I didn't know if I would be able to allow them to look
at
the
report without knowing the formID of the set of data they want to
fill
the
report.
--
howie


:

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A
nice
touch
would be to have the form also go invisible as it needs to stay
open
for
the
report to run.

Now the magic part! Put something like below in the query
criteria
as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for
parameters,
it
will be on the form. If you need to run it again, the stuff will
stay
there
as the form is closed. You could even get fancy with things like
a
date
picker, combo box, or list of possible criteria from the tables
in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle
Builder.


:

Ok yeah, that's the way that I was doing it. How can I make it
where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports) use
underlying
queries, and each of those underlying queries need the
FormID,
then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that has a couple of subreports. The main
report
and
subreports both need a formID value asked for from the
user.
I
have
linked
the child and master fields in the subreports, but I'm
still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
Do those 'many' tables have primary key fields of their own? Is that the
[taskID], [hazardID], and [hazmatID]?

What data type is the jobTbl!formID? What data type(s) are the [formID]
fields in the 'many' tables?

Here are some issues/questions you'll want to address before using the SQL
statement (adapted by you for your specific situation):

* The field names "Date" and "Name" in your [jobTbl] are reserved words in
Access. What YOU mean and what Access believes they mean may not match.
Please note that I've changed these fieldnames in the SQL below.

* the [subTasksTbl) appears to only have ID fields ... where's the
'beef'?!

If I wanted to present all of the information (which will, remember, be
"duplicated" for the "one" table), and bearing in mind that the "many"
tables may or may NOT have related records, I would use something like
(untested aircode - adapt as needed):

SELECT jobTbl.formID, jobTbl.FormDate, jobTbl.FormName, jobTbl.jobDescript,
subTasksTbl.taskID, hazardsTbl.hazardID, hazardsTbl.Description,
hazmatsTbl.hazmatID, hazmatsTbl.productlist, hazmatsTbl.MSDS
FROM ((jobTbl LEFT JOIN subTasksTbl ON jobTbl.formID = subTasksTbl.formID)
LEFT JOIN hazardsTbl ON jobTbl.formID = hazardsTbl.formID) LEFT JOIN
hazmatsTbl ON jobTbl.formID = hazmatsTbl.formID;

Regards

Jeff Boyce
Microsoft Office/Access MVP



howie said:
Fair enough...

jobTbl
formID
Date
Name
jobDescript

subTasksTbl
formID
taskID

hazardsTbl
formID
hazardID
Description

hazmatsTbl
formID
hazmatID
productList
MSDS

ok, here's a sample of the database that I am creating. For every one row
in
jobTbl there can be many rows in subTasksTbl, hazardsTbl, and hazmatsTbl.

Thanks Jeff
--
howie


Jeff Boyce said:
Let's trade ...

You give me a description of a couple of tables related one-to-many from
your domain, I'll take a stab at a query that provides the user all
information from both tables.

For example, a school registration db might have:

tblStudent
StudentID
FName
LName

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Regards

Jeff Boyce
Microsoft Office/Access MVP

howie said:
Can you give me an example of a "flattening" query?
--
howie


:

Because it would "flatten" the data, a one-to-many relationship would
'repeat' the one information for each of the many rows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I see what you mean now.

How would I deal with the one-to-many relationships though? I think
i've
written a query like this before, maybe it wasn't flattening as much
as
selecting everything but it turned out like below. Every time I had
more
than
one entry in a table, the whole set of data for that form id would
be
printed
for each different hazard, and so on for each of the other tables.

Example:
FormID Name Job Task Hazard
Sub-Task
1 Bob something falling objects
Climbing
Ladder
1 Bob something flying objects
Climbing
Ladder
1 Bob something falling objects
manual
lifting
1 Bob something flying objects
manual
lifting

The cross product of all the tables would be huge even with a
relatively
small number entries.
--
howie


:

I'm suggesting not requiring the user to ever know or see a FormID.

If you join together all the tables that have related information
(in
a
query), then give the user a way to search against that query, s/he
should
be able to find the information without ever touching a FormID.

(a "flattening" query ... that may not be a real term ... joins all
the
tables together, so the user sees something like what you'd have if
you
lined up all the data in a wide spreadsheet. The term "flat" is a
distinction from what you have with a relational design (deep, not
wide))

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Can you explain a little bit what you mean? Especially a
"flattening"
query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up
say
the
formID using some other piece of data?
--
howie


:

Howie

Another option would be to create a "flattening" query that
joins
all
that
related information together, then give the user a way to look
up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn
"relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jerry! That sounds great. But I want to get real fancy
with
it
and
make that form so that the user can choose from a combo box,
fields
like
name, date or formid and then provide that data and the report
will
be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for
someone
else
at my job, and they don't like that all the information is
tied
to
the
formID
(they don't understand how relational databases work) so they
want
to
be
able
to search based on the name, the date, the job task, the
division,
or
the
job
task. Is there anyway to do that? Because there is the
possibility
that
they
can get multiple sets of results from searching for something
besides
the
formID. So I didn't know if I would be able to allow them to
look
at
the
report without knowing the formID of the set of data they want
to
fill
the
report.
--
howie


:

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A
nice
touch
would be to have the form also go invisible as it needs to
stay
open
for
the
report to run.

Now the magic part! Put something like below in the query
criteria
as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for
parameters,
it
will be on the form. If you need to run it again, the stuff
will
stay
there
as the form is closed. You could even get fancy with things
like
a
date
picker, combo box, or list of possible criteria from the
tables
in
question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle
Builder.


:

Ok yeah, that's the way that I was doing it. How can I make
it
where
the user
would only get prompted once and still get all the required
information?
--
howie


:

If each of those reports (main, each of the subreports)
use
underlying
queries, and each of those underlying queries need the
FormID,
then
each
would call for it.

How are you "filling" the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I have a report that has a couple of subreports. The
main
report
and
subreports both need a formID value asked for from the
user.
I
have
linked
the child and master fields in the subreports, but I'm
still
getting
requests
for the value. How do I fix that?
Click to expand...
Click to expand...
Click to expand...
 
Hey Jeff,

Ok yes the primary key fields of the "many" tables are the IDs.

The formIDs in the "one" table and the "many" tables are just numbers.

Sorry about the name thing, I gave different names that what I actually have
because they are a little longer, so I thought that it would be easier for
you to write it like that and then I could just adapt it as needed.

Let me go look at this query you have written, and I will get back to you.

Thanks,
--
howie


Jeff Boyce said:
Do those 'many' tables have primary key fields of their own? Is that the
[taskID], [hazardID], and [hazmatID]?

What data type is the jobTbl!formID? What data type(s) are the [formID]
fields in the 'many' tables?

Here are some issues/questions you'll want to address before using the SQL
statement (adapted by you for your specific situation):

* The field names "Date" and "Name" in your [jobTbl] are reserved words in
Access. What YOU mean and what Access believes they mean may not match.
Please note that I've changed these fieldnames in the SQL below.

* the [subTasksTbl) appears to only have ID fields ... where's the
'beef'?!

If I wanted to present all of the information (which will, remember, be
"duplicated" for the "one" table), and bearing in mind that the "many"
tables may or may NOT have related records, I would use something like
(untested aircode - adapt as needed):

SELECT jobTbl.formID, jobTbl.FormDate, jobTbl.FormName, jobTbl.jobDescript,
subTasksTbl.taskID, hazardsTbl.hazardID, hazardsTbl.Description,
hazmatsTbl.hazmatID, hazmatsTbl.productlist, hazmatsTbl.MSDS
FROM ((jobTbl LEFT JOIN subTasksTbl ON jobTbl.formID = subTasksTbl.formID)
LEFT JOIN hazardsTbl ON jobTbl.formID = hazardsTbl.formID) LEFT JOIN
hazmatsTbl ON jobTbl.formID = hazmatsTbl.formID;

Regards

Jeff Boyce
Microsoft Office/Access MVP



howie said:
Fair enough...

jobTbl
formID
Date
Name
jobDescript

subTasksTbl
formID
taskID

hazardsTbl
formID
hazardID
Description

hazmatsTbl
formID
hazmatID
productList
MSDS

ok, here's a sample of the database that I am creating. For every one row
in
jobTbl there can be many rows in subTasksTbl, hazardsTbl, and hazmatsTbl.

Thanks Jeff
--
howie


Jeff Boyce said:
Let's trade ...

You give me a description of a couple of tables related one-to-many from
your domain, I'll take a stab at a query that provides the user all
information from both tables.

For example, a school registration db might have:

tblStudent
StudentID
FName
LName

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Regards

Jeff Boyce
Microsoft Office/Access MVP

Can you give me an example of a "flattening" query?
--
howie


:

Because it would "flatten" the data, a one-to-many relationship would
'repeat' the one information for each of the many rows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I see what you mean now.

How would I deal with the one-to-many relationships though? I think
i've
written a query like this before, maybe it wasn't flattening as much
as
selecting everything but it turned out like below. Every time I had
more
than
one entry in a table, the whole set of data for that form id would
be
printed
for each different hazard, and so on for each of the other tables.

Example:
FormID Name Job Task Hazard
Sub-Task
1 Bob something falling objects
Climbing
Ladder
1 Bob something flying objects
Climbing
Ladder
1 Bob something falling objects
manual
lifting
1 Bob something flying objects
manual
lifting

The cross product of all the tables would be huge even with a
relatively
small number entries.
--
howie


:

I'm suggesting not requiring the user to ever know or see a FormID.

If you join together all the tables that have related information
(in
a
query), then give the user a way to search against that query, s/he
should
be able to find the information without ever touching a FormID.

(a "flattening" query ... that may not be a real term ... joins all
the
tables together, so the user sees something like what you'd have if
you
lined up all the data in a wide spreadsheet. The term "flat" is a
distinction from what you have with a relational design (deep, not
wide))

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Can you explain a little bit what you mean? Especially a
"flattening"
query.
The term sounds kind of familar, but it's not ringing any bells.

Are you talking about giving the user a way to basically look up
say
the
formID using some other piece of data?
--
howie


:

Howie

Another option would be to create a "flattening" query that
joins
all
that
related information together, then give the user a way to look
up
information from THAT query.

This way, the user isn't exposed to and doesn't have to learn
"relational
database design".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jerry! That sounds great. But I want to get real fancy
with
it
and
make that form so that the user can choose from a combo box,
fields
like
name, date or formid and then provide that data and the report
will
be
generated. Is there any way to do that?

The reason that I ask is because I am making this database for
someone
else
at my job, and they don't like that all the information is
tied
to
the
formID
(they don't understand how relational databases work) so they
want
to
be
able
to search based on the name, the date, the job task, the
division,
or
the
job
task. Is there anyway to do that? Because there is the
possibility
that
they
can get multiple sets of results from searching for something
besides
the
formID. So I didn't know if I would be able to allow them to
look
at
the
report without knowing the formID of the set of data they want
to
fill
the
report.
--
howie


:

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter],
[txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A
nice
touch
would be to have the form also go invisible as it needs to
stay
open
for
the
report to run.

Now the magic part! Put something like below in the query
criteria
as
needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for
parameters,
it
will be on the form. If you need to run it again, the stuff
Click to expand...
Click to expand...
Click to expand...
 
Jerry

I really thinking about making the form that runs the report. But I was
looking to make it with a combo box and allowing the user to choose the value
that they want to use to create the report. For instance, using the date, or
the name of the person who filed the job. But that could bring up multiple
results, is there a way that I could allow them to search by what field they
wanted and then if it returns multiple rows to bring up another form or
something that would allow them to pick the report that they actually wanted
to view or print?
--
howie


Jerry Whittle said:
Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter], [txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the report. A nice touch
would be to have the form also go invisible as it needs to stay open for the
report to run.

Now the magic part! Put something like below in the query criteria as needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters, it
will be on the form. If you need to run it again, the stuff will stay there
as the form is closed. You could even get fancy with things like a date
picker, combo box, or list of possible criteria from the tables in question.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


howie said:
Ok yeah, that's the way that I was doing it. How can I make it where the user
would only get prompted once and still get all the required information?
Click to expand...
Click to expand...
 
Back
Top