Pat said:I have a subreport that I want to pull up data 180 days before the
date on the report. Any ideas on if and how this can be accomplished?
How does the main report get its date?
ideally it would come from a text box on the form that's
used to open the report. If that's what you have then use
the criteria:
<= DateAdd("d", -180, Forms!theform.thetextbox)
--
Marsh
MVP [MS Access]
I tried that previously but my boss didn't want 180 days from the day
they run the report he wants 180 days from the closing date on the
main report. I want the subreport to lookup the quarterly averages of
the hog groups that closed 180 days before the closing date of the
main report which shows info for one group that has been sold off.
- Show quoted text -
Pat said:The main report is based on two tables...Hog Inventory and Group
Information....The Group Information keeps track of each
group....Building, GroupID, DateIn, DateOut, Type. The Hog Inventory
keeps track of each bunch of hogs as they are moved-into the building
and then, after about four months, when they are sold....Building,
GroupID, Date (date moved in, transferred out, sold, etc.), Activity
(moved-in, transferred, sold), etc.
The main report (a closeout report for one group) lists the hog
inventory (comings and goings) for that group. Then there are
subreports that show relevant information regarding the one group
that's closing out (Production, Feed, Rations fed per head, Days per
ration, production averages for the different groups in the building,
and then the subreport that I'm try to figure out how to make it look
at the DateOut on the main form and show me the production averages
for every Building/Group that closed out the 180 days before this
group.
Pat said:The mainreportis based on two tables...Hog Inventory and Group
Information....The Group Information keeps track of each
group....Building, GroupID, DateIn, DateOut, Type. The Hog Inventory
keeps track of each bunch of hogs as they are moved-into the building
and then, after about four months, when they are sold....Building,\
GroupID,Date(datemoved in, transferred out, sold, etc.), Activity
(moved-in, transferred, sold), etc.The mainreport(a closeoutreportfor one group) lists the hog
inventory (comings and goings) for that group. Then there are
subreports that show relevant information regarding the one group
that's closing out (Production, Feed, Rations fed per head, Days per
ration, production averages for the different groups in the building,
and then thesubreportthat I'm try to figure out how to make it look
at the DateOut on the main form and show me the production averages
for every Building/Group that closed out the 180 daysbeforethis
group.
I think you lost me somewhere in there. I don't see whichdateyou you are basing things on (group - dateout??).
There doesn't seem to be any relationship between a group
and a building, but youhavea building field in the group
table. Seems like there should be a separate table for
buildings and a junction table to connect buildings to
groups on adatebasis.
About all I can say now is that you should try to construct
a query that joins the group and inventory tables so you cangetthedateyou want into the subrepot's record souce.
I am going to be away for the next week and a half. In the
meantime, you should review you table designs to make sure
each table represents a single entity (buildings, groups,
movements, diets, etc. You mightgetsomeone to review your
table structure by posting to the table design newsgroup.
If you don't want to wait till Igetback, I suggest that
you reorganize your question and start a new thread.
--
Marsh
MVP [MS Access]- Hide quoted text -
- Show quoted text -
Pat said:I want to base the data that is averaged in the subreport on the Group
Information field [DateOut]. These would be bldg/groups that closed
180 days before the report bldg/group and are the same [Stage] (grower
or nursery).
Each building is identified by where it is located: 3 is a nursery, 6
is on the farm, 5 is off the farm, 7 is contracted, etc. Then each
building starts getting pigs ([DateIn] the date when the first pigs
arrive), those pigs are referred to as group ## and are fed for
approximately 4-5 months, then they are sold. When the last pig
leaves the building that is the [DateOut].
Building GroupID In Date Out Date Stage Type Closeout
5R3 02 8/3/07 12/5/07 Grower Raised Yes
3JN 34 10/16/07 12/6/07 Nursery Raised Yes
3H 04 10/1/07 12/7/07 Nursery Raised Yes
5W 20 8/22/07 12/13/07 Grower Raised Yes
3C 49 11/2/07 12/18/07 Nursery Raised Yes
3S 53 11/9/07 12/18/07 Nursery Raised Yes
5S5 01 8/7/07 12/20/07 Grower Raised Yes
5S6 01 8/7/07 12/21/07 Grower Raised Yes
5F2 01 8/3/07 12/29/07 Grower Raised Yes
5F1 01 8/3/07 12/31/07 Grower Raised Yes
5B 24 9/11/07 1/8/08 Grower Raised Yes
5M 24 9/4/07 1/10/08 Grower Raised Yes
5N 27 9/5/07 1/11/08 Grower Raised Yes
The main report (a closeout report for bldg/group) lists the hog
inventory (comings and goings) for that bldg/group. Then there are
subreports that show relevant information regarding the bldg/group
that's closing out (Production Info, Feed, Rations fed per head, Days
per ration, production averages for the different groups that have
been in the building previously, and then the subreport that I'm try
to figure out how to make it look at the [DateOut] on the main form
and show me the production averages for every Building/Group that
closed out in the 180 days before this bldg/group's [DateOut].
I don't use a form to open this report. The main report uses a
parameter query where we enter the [Building], then the [GroupID].
The subreports link master fields and link child fields are based on
[Building] and [GroupID] except for the subreport that is giving me
the fit. It is based on [Stage] which could be Grower or Nursery
depending upon the report. The query I have set up for this subreport
also refers to [DateOut].
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.