 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / February 2008have a subreport get data related to a date on main report
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? Please and thank you. >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? Try using a filter/criteria for your date field like: <= DateAdd("d", -180, Date())  SignatureMarsh 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. . > >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? [quoted text clipped - 6 lines] > Marsh > MVP [MS Access] 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) SignatureMarsh 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 [quoted text clipped - 8 lines] >> >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? 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. > How does the main report get its date? > [quoted text clipped - 21 lines] > > - Show quoted text - >The main report is based on two tables...Hog Inventory and Group >Information....The Group Information keeps track of each [quoted text clipped - 13 lines] >for every Building/Group that closed out the 180 days before this >group. I think you lost me somewhere in there. I don't see which date you you are basing things on (group - dateout??). There doesn't seem to be any relationship between a group and a building, but you have a 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 a date basis. About all I can say now is that you should try to construct a query that joins the group and inventory tables so you can get the date you 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 might get someone to review your table structure by posting to the table design newsgroup. If you don't want to wait till I get back, I suggest that you reorganize your question and start a new thread. SignatureMarsh MVP [MS Access] 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]. Thank you again for any help you can give me to get this solved. > >The mainreportis based on two tables...Hog Inventory and Group > >Information....The Group Information keeps track of each [quoted text clipped - 38 lines] > > - Show quoted text - >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 [quoted text clipped - 40 lines] >depending upon the report. The query I have set up for this subreport >also refers to [DateOut]. I still can not relate all that to your tables and queries. All I can say here is that the first thing any report requires is a query that selects exactly the right records for the report. Your subreport's query may be simple or complex (and the Link Master/Child properties can play a big part), but I can not see how your 180 day interval relates to your tables/queries and their fields. SignatureMarsh MVP [MS Access] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.