SeamusO
06-23-2011, 12:00 PM
This is my first time posting on this site but visited previously and have found information helpful for other projects. I think my topic is a "Theroy & Practice" and not a "Table" topic. If not, I apologize.
I inherited a table that was originally an Excel file and someone else just dumped the Excel worksheet into Access. I have been asked to clean it up and make some parts more automated and dynamic. I am going through the process of breaking up the origninal table into multiple tables to 'normalize' the database. I have been successful with some parts but I am stuck on one section.
I have created a ProjectID so that every record has only one ProjectID. There are 4,000 records (projects) in this table. The project data is collected monthly starting in 1/2007 thru 12/2015 (or 108 fields). Not every record has a value in the field (one project might start in 1/2007 but another might not start until 10/2010). New projects will be coming on line all the time and eventually we are going to add more months to collect data on. The concept that "records are free but fields cost" makes sense to me but how do I do it when both can increase. I don't know what is the best practice for setting up this type of table. I want to make sure I keep the current data associated with the related ProjectID (it is static data and not derived data so I can not do it by calculations). I attempted Crosstab Queries and transposing the data but neither seem to work the way I expected.
Any suggestions would be greatly appreciated.
I inherited a table that was originally an Excel file and someone else just dumped the Excel worksheet into Access. I have been asked to clean it up and make some parts more automated and dynamic. I am going through the process of breaking up the origninal table into multiple tables to 'normalize' the database. I have been successful with some parts but I am stuck on one section.
I have created a ProjectID so that every record has only one ProjectID. There are 4,000 records (projects) in this table. The project data is collected monthly starting in 1/2007 thru 12/2015 (or 108 fields). Not every record has a value in the field (one project might start in 1/2007 but another might not start until 10/2010). New projects will be coming on line all the time and eventually we are going to add more months to collect data on. The concept that "records are free but fields cost" makes sense to me but how do I do it when both can increase. I don't know what is the best practice for setting up this type of table. I want to make sure I keep the current data associated with the related ProjectID (it is static data and not derived data so I can not do it by calculations). I attempted Crosstab Queries and transposing the data but neither seem to work the way I expected.
Any suggestions would be greatly appreciated.