|
|
|
date: Thu, 9 Oct 2008 08:19:01 -0700,
group: microsoft.public.access.reports
back
Creating a report from a query
I am having problems pulling in the information I want from my query to my
report due to table relationships.
I have created a report from a query that shows the number of animals sold,
died or transferred for the month. This query comes from a disposition
table, every animal that is sold, died or transferred has an individual
disposition number and is entered into the disposition table, all the query
does is sum the number in each category for the month, it also is limited by
1 of 3 different animal units. In a separate table is a list of all animals
born, I have created a query to count the animals born for the month, this
query is also limited to 1 of 3 different animal units.
I created a third query to tie the first two queries together, I linked them
by the Animal Unit each query was orginally limited to. I then created a
report to show all the animals sold, transferred, died, or born for a
specified month, by animal Unit.
My problem is that if a unit has animals that are born but no animals that
had disposition records (died, transferred, sold) no results show up because
of the join properties in the third query. So I changed it so that all
disposition records will show up in the query but only birth records that are
equal, this works except for when a unit only has birth records and no
dispostion records.
Does anyone have any suggestions? Can I set up a report to pull from 2
different queries? Do I need to change the format of my third query?
date: Thu, 9 Oct 2008 08:19:01 -0700
author: Thorson
RE: Creating a report from a query
I would use either a union query or subreports.
--
Duane Hookom
Microsoft Access MVP
"Thorson" wrote:
> I am having problems pulling in the information I want from my query to my
> report due to table relationships.
>
> I have created a report from a query that shows the number of animals sold,
> died or transferred for the month. This query comes from a disposition
> table, every animal that is sold, died or transferred has an individual
> disposition number and is entered into the disposition table, all the query
> does is sum the number in each category for the month, it also is limited by
> 1 of 3 different animal units. In a separate table is a list of all animals
> born, I have created a query to count the animals born for the month, this
> query is also limited to 1 of 3 different animal units.
>
> I created a third query to tie the first two queries together, I linked them
> by the Animal Unit each query was orginally limited to. I then created a
> report to show all the animals sold, transferred, died, or born for a
> specified month, by animal Unit.
>
> My problem is that if a unit has animals that are born but no animals that
> had disposition records (died, transferred, sold) no results show up because
> of the join properties in the third query. So I changed it so that all
> disposition records will show up in the query but only birth records that are
> equal, this works except for when a unit only has birth records and no
> dispostion records.
>
> Does anyone have any suggestions? Can I set up a report to pull from 2
> different queries? Do I need to change the format of my third query?
date: Thu, 9 Oct 2008 11:48:07 -0700
author: Duane Hookom
RE: Creating a report from a query
So I set up a sub report to show the births for the month. However, for the
main report I have the query set up so that the user enters in the month
number and then the year number, but the query I have for the sub-report does
the same thing (this is to limit it to that month and year)... Now since both
the query for the main report and sub report asks for the month and year when
you run the report it asks for each one twice, is there anyway to fix it so
that you only have to enter it once and both reports will run for only that
month and year?
"Duane Hookom" wrote:
> I would use either a union query or subreports.
>
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "Thorson" wrote:
>
> > I am having problems pulling in the information I want from my query to my
> > report due to table relationships.
> >
> > I have created a report from a query that shows the number of animals sold,
> > died or transferred for the month. This query comes from a disposition
> > table, every animal that is sold, died or transferred has an individual
> > disposition number and is entered into the disposition table, all the query
> > does is sum the number in each category for the month, it also is limited by
> > 1 of 3 different animal units. In a separate table is a list of all animals
> > born, I have created a query to count the animals born for the month, this
> > query is also limited to 1 of 3 different animal units.
> >
> > I created a third query to tie the first two queries together, I linked them
> > by the Animal Unit each query was orginally limited to. I then created a
> > report to show all the animals sold, transferred, died, or born for a
> > specified month, by animal Unit.
> >
> > My problem is that if a unit has animals that are born but no animals that
> > had disposition records (died, transferred, sold) no results show up because
> > of the join properties in the third query. So I changed it so that all
> > disposition records will show up in the query but only birth records that are
> > equal, this works except for when a unit only has birth records and no
> > dispostion records.
> >
> > Does anyone have any suggestions? Can I set up a report to pull from 2
> > different queries? Do I need to change the format of my third query?
date: Thu, 9 Oct 2008 13:10:01 -0700
author: Thorson
RE: Creating a report from a query
IMHO, never use parameter prompts http://www.tek-tips.com/faqs.cfm?fid=6763.
Always get user input from controls on forms and occasionally from MsgBox().
--
Duane Hookom
Microsoft Access MVP
"Thorson" wrote:
> So I set up a sub report to show the births for the month. However, for the
> main report I have the query set up so that the user enters in the month
> number and then the year number, but the query I have for the sub-report does
> the same thing (this is to limit it to that month and year)... Now since both
> the query for the main report and sub report asks for the month and year when
> you run the report it asks for each one twice, is there anyway to fix it so
> that you only have to enter it once and both reports will run for only that
> month and year?
>
>
>
> "Duane Hookom" wrote:
>
> > I would use either a union query or subreports.
> >
> > --
> > Duane Hookom
> > Microsoft Access MVP
> >
> >
> > "Thorson" wrote:
> >
> > > I am having problems pulling in the information I want from my query to my
> > > report due to table relationships.
> > >
> > > I have created a report from a query that shows the number of animals sold,
> > > died or transferred for the month. This query comes from a disposition
> > > table, every animal that is sold, died or transferred has an individual
> > > disposition number and is entered into the disposition table, all the query
> > > does is sum the number in each category for the month, it also is limited by
> > > 1 of 3 different animal units. In a separate table is a list of all animals
> > > born, I have created a query to count the animals born for the month, this
> > > query is also limited to 1 of 3 different animal units.
> > >
> > > I created a third query to tie the first two queries together, I linked them
> > > by the Animal Unit each query was orginally limited to. I then created a
> > > report to show all the animals sold, transferred, died, or born for a
> > > specified month, by animal Unit.
> > >
> > > My problem is that if a unit has animals that are born but no animals that
> > > had disposition records (died, transferred, sold) no results show up because
> > > of the join properties in the third query. So I changed it so that all
> > > disposition records will show up in the query but only birth records that are
> > > equal, this works except for when a unit only has birth records and no
> > > dispostion records.
> > >
> > > Does anyone have any suggestions? Can I set up a report to pull from 2
> > > different queries? Do I need to change the format of my third query?
date: Thu, 9 Oct 2008 14:04:01 -0700
author: Duane Hookom
RE: Creating a report from a query
I'm sorry, I'm new at this. How do I limit my report to different months
using only input controls on forms or msgbox()? for the msgbox would I need
to write code asking for the month and year?
thank you
"Duane Hookom" wrote:
> IMHO, never use parameter prompts http://www.tek-tips.com/faqs.cfm?fid=6763.
> Always get user input from controls on forms and occasionally from MsgBox().
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "Thorson" wrote:
>
> > So I set up a sub report to show the births for the month. However, for the
> > main report I have the query set up so that the user enters in the month
> > number and then the year number, but the query I have for the sub-report does
> > the same thing (this is to limit it to that month and year)... Now since both
> > the query for the main report and sub report asks for the month and year when
> > you run the report it asks for each one twice, is there anyway to fix it so
> > that you only have to enter it once and both reports will run for only that
> > month and year?
> >
> >
> >
> > "Duane Hookom" wrote:
> >
> > > I would use either a union query or subreports.
> > >
> > > --
> > > Duane Hookom
> > > Microsoft Access MVP
> > >
> > >
> > > "Thorson" wrote:
> > >
> > > > I am having problems pulling in the information I want from my query to my
> > > > report due to table relationships.
> > > >
> > > > I have created a report from a query that shows the number of animals sold,
> > > > died or transferred for the month. This query comes from a disposition
> > > > table, every animal that is sold, died or transferred has an individual
> > > > disposition number and is entered into the disposition table, all the query
> > > > does is sum the number in each category for the month, it also is limited by
> > > > 1 of 3 different animal units. In a separate table is a list of all animals
> > > > born, I have created a query to count the animals born for the month, this
> > > > query is also limited to 1 of 3 different animal units.
> > > >
> > > > I created a third query to tie the first two queries together, I linked them
> > > > by the Animal Unit each query was orginally limited to. I then created a
> > > > report to show all the animals sold, transferred, died, or born for a
> > > > specified month, by animal Unit.
> > > >
> > > > My problem is that if a unit has animals that are born but no animals that
> > > > had disposition records (died, transferred, sold) no results show up because
> > > > of the join properties in the third query. So I changed it so that all
> > > > disposition records will show up in the query but only birth records that are
> > > > equal, this works except for when a unit only has birth records and no
> > > > dispostion records.
> > > >
> > > > Does anyone have any suggestions? Can I set up a report to pull from 2
> > > > different queries? Do I need to change the format of my third query?
date: Thu, 9 Oct 2008 14:26:01 -0700
author: Thorson
RE: Creating a report from a query
There is at least one solution for using controls on a form at
http://www.fontstuff.com/access/acctut08.htm. You can search google on using
form controls to filter a report.
--
Duane Hookom
Microsoft Access MVP
"Thorson" wrote:
> I'm sorry, I'm new at this. How do I limit my report to different months
> using only input controls on forms or msgbox()? for the msgbox would I need
> to write code asking for the month and year?
>
> thank you
>
> "Duane Hookom" wrote:
>
> > IMHO, never use parameter prompts http://www.tek-tips.com/faqs.cfm?fid=6763.
> > Always get user input from controls on forms and occasionally from MsgBox().
> > --
> > Duane Hookom
> > Microsoft Access MVP
> >
> >
> > "Thorson" wrote:
> >
> > > So I set up a sub report to show the births for the month. However, for the
> > > main report I have the query set up so that the user enters in the month
> > > number and then the year number, but the query I have for the sub-report does
> > > the same thing (this is to limit it to that month and year)... Now since both
> > > the query for the main report and sub report asks for the month and year when
> > > you run the report it asks for each one twice, is there anyway to fix it so
> > > that you only have to enter it once and both reports will run for only that
> > > month and year?
> > >
> > >
> > >
> > > "Duane Hookom" wrote:
> > >
> > > > I would use either a union query or subreports.
> > > >
> > > > --
> > > > Duane Hookom
> > > > Microsoft Access MVP
> > > >
> > > >
> > > > "Thorson" wrote:
> > > >
> > > > > I am having problems pulling in the information I want from my query to my
> > > > > report due to table relationships.
> > > > >
> > > > > I have created a report from a query that shows the number of animals sold,
> > > > > died or transferred for the month. This query comes from a disposition
> > > > > table, every animal that is sold, died or transferred has an individual
> > > > > disposition number and is entered into the disposition table, all the query
> > > > > does is sum the number in each category for the month, it also is limited by
> > > > > 1 of 3 different animal units. In a separate table is a list of all animals
> > > > > born, I have created a query to count the animals born for the month, this
> > > > > query is also limited to 1 of 3 different animal units.
> > > > >
> > > > > I created a third query to tie the first two queries together, I linked them
> > > > > by the Animal Unit each query was orginally limited to. I then created a
> > > > > report to show all the animals sold, transferred, died, or born for a
> > > > > specified month, by animal Unit.
> > > > >
> > > > > My problem is that if a unit has animals that are born but no animals that
> > > > > had disposition records (died, transferred, sold) no results show up because
> > > > > of the join properties in the third query. So I changed it so that all
> > > > > disposition records will show up in the query but only birth records that are
> > > > > equal, this works except for when a unit only has birth records and no
> > > > > dispostion records.
> > > > >
> > > > > Does anyone have any suggestions? Can I set up a report to pull from 2
> > > > > different queries? Do I need to change the format of my third query?
date: Thu, 9 Oct 2008 19:41:01 -0700
author: Duane Hookom
|
|
