Access Record with Date Range of Multiple Months to Single Month

I have trade data that includes multiple months on one record. For example

9/1/2010 to 3/31/2011
I need to split the data into singular months and copy all other dataon record.

9/1/2010 to 9/30/2010
10/1/2010 to 10/31/2010
ect.
elinnAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Then this really needs to be a One to many design, with a "One" table (The Parent, Trades) and a "Many" table (the Child, TradeDates)

Here is a sample
I could have used a loop but I left it this way so that you could see the Date range formula progression throughout the months.

Examine it carefully, I am sure you will be able to adapt it to work in your database.

;-)

JeffCoachman


Access-EEQ-26441869--Generate-Bl.mdb
0
 
karephreIT Specialist (Server Management)Commented:
can you provide a screen shot of the table.
0
 
elinnAuthor Commented:
Sure here you go. Im pretty inexperienced. So keep that in mind.
screenshot.bmp
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
karephreIT Specialist (Server Management)Commented:
so you want each enddate  to have its on table? If this is the case all you have to do is make a copy of the table structure. then build a append query to copy all data thats between 1st and the last day of that month.  
0
 
elinnAuthor Commented:
I need to take one trade, for example ID #4446, and add duplicate lines with the date ranges for that trade by month. So that one trade would be expanded to 7 lines within the table.

4446          9/1/2010       9/30/2010
4446          10/1/2010      10/31/2010
4446          11/1/2010      11/30/2010
4446          12/1/2010      12/31/2010
4446          1/1/2011         1/31/2011
4446          2/1/2011         2/28/2011
4446          3/1/2011        3/31/2011
0
 
karephreIT Specialist (Server Management)Commented:
if you just want to view it in that format. you can just build a select query in that date range.
0
 
elinnAuthor Commented:
I really want something that will systematically copy the record and break the record into 7 identical records with different month ranges. If I can make a select query like this let me know how and I can easily append into the table.
0
 
karephreIT Specialist (Server Management)Commented:
You may have to export it to Excel to do the changes with the functions then import it back in.
0
 
Patrick MatthewsCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
All Courses

From novice to tech pro — start learning today.