Creating a report from a query

[Thorson]

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?

 

[Duane Hookom]

I would use either a union query or subreports.

 

[Thorson]

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]

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().

 

[Thorson]

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]

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.