D
Darlene
Is it possible to have more than one query supply information to a report?
If so, how can this process be done?
If so, how can this process be done?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Spencer
A report can only have one query as its source.
Some Possible options depending on what is in the two queries:
You can use sub-reports and have each sub-report bound to a query.
Or you might be able to use a UNION query to combine the two queries
Or you might be able to join the two queries together if there is a
relationship between them that you can use.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Some Possible options depending on what is in the two queries:
You can use sub-reports and have each sub-report bound to a query.
Or you might be able to use a UNION query to combine the two queries
Or you might be able to join the two queries together if there is a
relationship between them that you can use.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
J
Jeff Boyce
Darlene
Not clear on your specifics, but if you used one query per report, then
embed some of them (as subreports) in one of them (the "main" report), you
could do something like what you asked.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Not clear on your specifics, but if you used one query per report, then
embed some of them (as subreports) in one of them (the "main" report), you
could do something like what you asked.
Regards
Jeff Boyce
Microsoft Office/Access MVP
S
Steve
2 Yeses!!
1. You can have a query as the recordsource for a report where the query
includes another query.
2. You can have a report/subreport where the main report has a query for
the recordsource and the subreport has a query for the recordsource.
Steve
(e-mail address removed)
1. You can have a query as the recordsource for a report where the query
includes another query.
2. You can have a report/subreport where the main report has a query for
the recordsource and the subreport has a query for the recordsource.
Steve
(e-mail address removed)
J
Jerry Whittle
You could have a report and subreport combination which would pull data from
more than one query.
Also if both queries basically have the same information, you could create a
union query out of them as the source for the report.
more than one query.
Also if both queries basically have the same information, you could create a
union query out of them as the source for the report.
D
Darlene
Thank you to ALLL... We will research "Sub-Reports" and "Union Queries" to
test this process.
test this process.
S
Steve
Darlene,
Before you spend too much time possibly going down the wrong road, show us
your tables and fields and tell us why you asked the question.
Steve
(e-mail address removed)
Before you spend too much time possibly going down the wrong road, show us
your tables and fields and tell us why you asked the question.
Steve
(e-mail address removed)
D
Darlene
We have multiply schools that purchase three types of programs (Dance,
Theatre, or Early Learning), which are all listed under the field “Programsâ€
in the table. Each Dance or Theatre program has a specific type of program
(ex. Dance – Tap, Theater – Poetry) called residencies. For the document we
need to indicate that either a dance or theatre program was purchased as well
as how many residencies for each program per school.
1. How can the information pertaining to that program be programmed to show
up in the appropriate column?
Type of Residency Program (s) Early Learning Through the Arts Arts Academy
in «Program»
Discipline «Residency_Type»
«Residency_Type»
2. How can the number of residencies from a specific school be calculated
for a specific program and appear in the table below?
Arts Academy «Program» -
(# of) Residency(ies)
Early Learning - (# of) Residency(ies)
Theatre, or Early Learning), which are all listed under the field “Programsâ€
in the table. Each Dance or Theatre program has a specific type of program
(ex. Dance – Tap, Theater – Poetry) called residencies. For the document we
need to indicate that either a dance or theatre program was purchased as well
as how many residencies for each program per school.
1. How can the information pertaining to that program be programmed to show
up in the appropriate column?
Type of Residency Program (s) Early Learning Through the Arts Arts Academy
in «Program»
Discipline «Residency_Type»
«Residency_Type»
2. How can the number of residencies from a specific school be calculated
for a specific program and appear in the table below?
Arts Academy «Program» -
(# of) Residency(ies)
Early Learning - (# of) Residency(ies)
S
Steve
As suspected, your report problem stems from incorrect tables. Conside this
design:
TblSchool
SchoolID
<School Attributes>
TblProgram
ProgramID
Program
TblResidency
ResidencyID
Residency
TblProgramResidency
ProgramResidencyID
ProgramID
ResidencyID
TblSchoolProgramResidency
SchoolProgramResidencyID
SchoolID
ProgramResidencyID
Study these tables then give me any questions you have about the tables.
Once we resolve the design of the tables, then you need to reword your two
questions relative to the above tables.
Steve
(e-mail address removed)
design:
TblSchool
SchoolID
<School Attributes>
TblProgram
ProgramID
Program
TblResidency
ResidencyID
Residency
TblProgramResidency
ProgramResidencyID
ProgramID
ResidencyID
TblSchoolProgramResidency
SchoolProgramResidencyID
SchoolID
ProgramResidencyID
Study these tables then give me any questions you have about the tables.
Once we resolve the design of the tables, then you need to reword your two
questions relative to the above tables.
Steve
(e-mail address removed)
D
Darlene
The db is set up with the following tables:
TblSchool
School ID
TblRes
Res ID
School ID
Please advise why you are suggesting that the additional tables (TblProgram
and TblProgramResidency) need to be added to this design?
Tbl
TblSchool
School ID
contains the name, addres, contact information of the school
TblOrg
OrgID
contains contact information of purchasing organization
TblRes
Res ID
School ID
field-Program Type
field-Res Type
also identifies residency information, cost, time
Please advise why you are suggesting that the additional tables (TblProgram
and TblProgramResidency) need to be added to this design?
Tbl
J
Jeff Boyce
Darlene
Remember that the information you receive here is coming from folks with
widely-varying expertise and motivation.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Remember that the information you receive here is coming from folks with
widely-varying expertise and motivation.
Regards
Jeff Boyce
Microsoft Office/Access MVP
S
Steve
Your three tables do not appear to be consistent with your original post. In
your original post you said "...three types of programs .... which are all
listed under the field Programs". I don't see a field Programs in any of
these three tables.
In your original post you said "...multiply (sp?) schools that purchase
three types of programs ...). TblOrg here seems to imply that an
organization purchases programs for the schools.
Steve
your original post you said "...three types of programs .... which are all
listed under the field Programs". I don't see a field Programs in any of
these three tables.
In your original post you said "...multiply (sp?) schools that purchase
three types of programs ...). TblOrg here seems to imply that an
organization purchases programs for the schools.
Steve
S
Steve
Yes! Your and Arno's expertise and motivation is readily apparent as neither
of you offered Darlene any help. That makes both of you merely stalkers. If
you don't have any good to say, don't butt in.
Steve
of you offered Darlene any help. That makes both of you merely stalkers. If
you don't have any good to say, don't butt in.
Steve
J
Jeff Boyce
Can you say "projection"?
Jeff
Jeff
Steve said:Yes! Your and Arno's expertise and motivation is readily apparent as
neither of you offered Darlene any help. That makes both of you merely
stalkers. If you don't have any good to say, don't butt in.
Steve
S
Steve
Still no help for the Op. Again you're just stalking!
Steve
Steve
D
Darlene
Steve,
Yes that is correct, sometimes the organization can purchase for several
schools (ex. School District). Nonetheless, I am not clear on why I would
need to change my database design and add the additional tables as you
suggested. Please explain.
Thanks for all your help!!!
TblSchool
School ID
TblRes
Res ID
School ID
Yes that is correct, sometimes the organization can purchase for several
schools (ex. School District). Nonetheless, I am not clear on why I would
need to change my database design and add the additional tables as you
suggested. Please explain.
Thanks for all your help!!!
TblSchool
School ID
contains the name, addres, contact information of the school
TblRes
Res ID
School ID
K
Keith Wilby
Steve said:If you don't have any good to say, don't butt in.
I thought it was *well* worth saying. And it didn't break forum rules.
K
Keith Wilby
Darlene said:I am not clear on why I would
need to change my database design and add the additional tables as you
suggested. Please explain.
The short answer is that you haven't posted enough information about your
tables and relationships for anyone to assess whether or not your design is
sound. $teve's stock reply in situations like this is to rubbish the design
in the hope that the poster will become confused and ask for his (paid)
help. The link in Arno's post will tell you all about $teve's solicitations
for work.
The chances are that you *don't* need to redesign your app but without more
information no-one can make that assessment. I think that might be what
Jeff was driving at.
Keith.
www.keithwilby.co.uk
D
Darlene
Thank you. Having to design this DB based on my minimal knowledge of all the
features has been a REAL challenge. I will try to express the out come I am
looking for.
In this database there are several tables, however, two of them I would like
to focus on:
TblSchool
School ID (key)
TblRes
Res ID (key)
School ID
Below is the explanation of what I am seeking to accomplish. I have
designed forms to be used as documents (example: invoices and contracts). I
will use the invoice in my explanation:
Example – Washington School purchased 2 dance residencies (1 tap, 1
ballet) and 1 theatre (1 poetry). Each residency is identified by its own
number in the table. so in the tbleRes, Washington school has purchased the
following
Res Id School ID School Program Pro-Type
1 125 Washington School Dance Tap
2 125 Washington School Dance Ballet
3 125 Washington School Theatre Poetry
Thus, the invoice (form) should read as follows:
Quantity Description Per Unit cost
Total Due
2 Dance Tap and Ballet $1.00
$2.00
1 Theater Poetry $1.00
$1.00
Total
$3.00
What I am seeking help on is how to program the individual residency
descriptions so that each residency is identified when more than one of the
same program has been purchased. In this case, the dance program has been
purchased twice, though they are different types.
I need instruction on how to program the form to look like this. By
understaning how this programming works will help resolve the same situation
I have with capturing the correct information need for the contracts.
I hope this explanation makes sense. Thank you for any help that you can
offer me!!!
features has been a REAL challenge. I will try to express the out come I am
looking for.
In this database there are several tables, however, two of them I would like
to focus on:
TblSchool
School ID (key)
contains the name, address, contact information of the schools
TblRes
Res ID (key)
School ID
field-Program Type **(Dance, Theatre, Early Learning)
field-Res Type ** (Tap, Poetry, Ballet, etc)
also identifies residency information, cost, time...
Below is the explanation of what I am seeking to accomplish. I have
designed forms to be used as documents (example: invoices and contracts). I
will use the invoice in my explanation:
Example – Washington School purchased 2 dance residencies (1 tap, 1
ballet) and 1 theatre (1 poetry). Each residency is identified by its own
number in the table. so in the tbleRes, Washington school has purchased the
following
Res Id School ID School Program Pro-Type
1 125 Washington School Dance Tap
2 125 Washington School Dance Ballet
3 125 Washington School Theatre Poetry
Thus, the invoice (form) should read as follows:
Quantity Description Per Unit cost
Total Due
2 Dance Tap and Ballet $1.00
$2.00
1 Theater Poetry $1.00
$1.00
Total
$3.00
What I am seeking help on is how to program the individual residency
descriptions so that each residency is identified when more than one of the
same program has been purchased. In this case, the dance program has been
purchased twice, though they are different types.
I need instruction on how to program the form to look like this. By
understaning how this programming works will help resolve the same situation
I have with capturing the correct information need for the contracts.
I hope this explanation makes sense. Thank you for any help that you can
offer me!!!
S
Steve
You chose to ignore my recommendation and listen to someone who is only bent
on stalking me in the newsgroups. Now you see what kind of help he provides.
Steve
on stalking me in the newsgroups. Now you see what kind of help he provides.
Steve
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.