Solved

Access Record with Date Range of Multiple Months to Single Month

Posted on 2010-08-31
10
317 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:elinn
10 Comments
 
LVL 3

Expert Comment

by:karephre
ID: 33569652
can you provide a screen shot of the table.
0
 

Author Comment

by:elinn
ID: 33569742
Sure here you go. Im pretty inexperienced. So keep that in mind.
screenshot.bmp
0
 
LVL 3

Expert Comment

by:karephre
ID: 33569950
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
 

Author Comment

by:elinn
ID: 33570005
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 3

Expert Comment

by:karephre
ID: 33570009
if you just want to view it in that format. you can just build a select query in that date range.
0
 

Author Comment

by:elinn
ID: 33570738
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
 
LVL 3

Expert Comment

by:karephre
ID: 33571452
You may have to export it to Excel to do the changes with the functions then import it back in.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 50 total points
ID: 33571637
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36157664
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now