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
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

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.



karephreIT Specialist (Server Management)Commented:
can you provide a screen shot of the table.
elinnAuthor Commented:
Sure here you go. Im pretty inexperienced. So keep that in mind.
The new generation of project management tools

With’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.  
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
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.
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.
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.
Patrick MatthewsCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
All Courses

From novice to tech pro — start learning today.