Best way for managing several years of data.

[calvinle]

Hi,

I am thinking of designing a database for the report that I am working in excel to a database. Every month we get a report with cumulated data of members. If there are new members they will be added to the excel report. My question is simply:
What is the best way to manage the data for every year in the database?
Should I create 1 table per year?
Should I create only 1 table with a field of the year's report so I can filter them later via combobox? -> What will happen to the PK id if I accmulate too many records over years?

How do you manage ur data over years?

 

[llkhoutx]

It depends on how much data you get each year, how many years will be involved, and whether, or not, Y2Y reports are to be generated. Remember Access databases, front end and back end, can be 2G each in size. Size and create your table(s) accordingly.

 

[James Deckert]

You should have one table which includes a date field (or an integer field representing the date in the format YYYYMMDD, or YYYYMM, or YYYY depending on your reporting needs). This will allow you to filter based on a combobox. You can at any time remove old records which are no longer useful. Having a different table for each year introduces maintenance issues each year as you create a new table and change the report to look at the new table, etc.

 

[calvinle]

You should have one table which includes a date field (or an integer field representing the date in the format YYYYMMDD, or YYYYMM, or YYYY depending on your reporting needs). This will allow you to filter based on a combobox. You can at any time remove old records which are no longer useful. Having a different table for each year introduces maintenance issues each year as you create a new table and change the report to look at the new table, etc.

Thats what I thought too. Thanks for the suggestion

 

[gemma-the-husky]

James is right.

A database is structured like a multi-dimensional spreadsheet (but more flexible than that). Instead of having either a series of spreadsheets, or a spreadsheet with a series of tabs for different years, access manages your data more by having a single sheet of data, which you slice and dice as you require.

unlike excel, where you typically view all your data, a database is quite different, and you very rarely view all your data in one go. You generally view a subset of the data, featuring the rows and columns in which you are interested.

The data analysis you do need is to separate out repeating groups of data into sub tables - but this is quite different to the way you tend to use excel sheets.

One important difference is that data in a database is not ordered. In a spreadsheet you often calculate a value in a row by reference to a previous row. In a database, you generally do not have this feature - and it is hard work trying to achieve it. Instead you manage the data by analysing the set of data which you filtered.

you need to consider this when designing the funcionality of your database.

 

[calvinle]

If the excel file is imported to access in a tblTemp then you extract all required data to a main table. Will you work on the data directly or have another field for edited data so that raw data will be still kept in the main table for future reference?

 

[gemma-the-husky]

you can do whatever works best.

if you only want to use access, then it's a one time set up, and after that you use access only.

alternatively you may prepare a regular excel sheet, and import that into the access dbs each time.

generally the access table structure is different to the excel sheet. you will almost certainly need more tables. Worth reading articles on normalisation.