Question DATABASE table/report problem!!!!

[kidrobot]

I'm looking for the best solution for this problem, any suggestions on how to tackle this would be great. My database runs reports off of two tables,'dataPY' and 'dataCY' (PY = previous year 2007, CY = current year 2008). When the year changes say it's 2009, the Database Admin will take the CY table and rename it to data2007 (according to the year) then the CY = 2009 and PY = 2008. My problem is my manager may need to run reports for previous years, but the table names have changed and the reports will only run for CY and PY. I was thinking of making the report for the data2007 table, but then for the future I'd have to make it for data2008, data2009, and so on. and by that time I won't even work here anymore. What should I do, I may have left things out so feel free to ask questions.

 

[twoplustwo]

Just use one table and query it accordingly???

 

[boblarson]

Your design is flawed. You should not be renaming tables based on the year. You should just be capturing data with a date field that you can query upon. This should never have gotten this way.

 

[boblarson]

As an addendum - If a database is properly designed then changes to the structure should almost never need to occur.

 

[twoplustwo]

In fact I'd write append queries today and get all of that data into one table before I spent another second working with this structure. Pretty amazing that this has gone on for any amount of time...

 

[kidrobot]

bob, I know the design is flawed. The data in the tables for each year consisted of MILLIONS of records, so this was the only way to separate the data accordingly. The tables are updated from teradata every night, so these are solid tables and I can't change the tables at all. Is there any programming solution I can do or am I just SOL and stuck with creating multiple reports for each year?

 

[twoplustwo]

Hi KidRobot,

Fudging temporary fixes and producing work that will have to be replicated over and over is a poor way to go about things.

If this data is business critical wouldn't it be worth upgrading to a dedicated server-type format that can manage millions of rows?

 

[kidrobot]

well how many rows can one Access table handle without it becoming to large to handle?

 

[twoplustwo]

File size is limited to 2GB is ACC03 I think rather than number of rows. If you only have two columns in a table god only knows how many.Obviously this won't be the case but you get my point.

 

[kidrobot]

Thanks for all the help so far. I have one more question. Say I'm querying or making a report on a table that has 100,000 records. This query/report has equations, conversions, etc (things that slow down a query), would this query run at the same speed if the table had 1 million records? I'm thinking the 1million records query would be a lot slower right? I believe that was the main reason to separate the tables into years. I agree with all of you, this is a bad design. But is what I am saying correct?

 

[georgedwilkinson]

Not if it uses an index. The speed should be comparable.

Also, you should really consider using Oracle and the partitioning option if your data set is this large. You can still keep your data in one table but can store the table in several partitions, based on the date.

I was using Oracle with multiple tables well in excess of 30 million rows without even bothering with partitioning but was able to access my data rapidly because my design was normalized and my indexes were optimized (and I ran statistics daily). You should be able to get similar results in SQL or Access, if you can manage to keep your files to the right size(s).

 

[kidrobot]

How do you Index in Access? Good tutorial or article?

 

[georgedwilkinson]

In design view of the table, click on the lightning bolt on the button bar.

 

[kidrobot]

cool thanks, I took a db class last year and totally forgot about indexes. thanks.

 

[kidrobot]

mispost....

 

[sportsguy]

How many rows can access handle?

With one index, duplicates allowed, i have had access page locking issues over 7 million rows, 500 Megs in database size. . .

there are limits. .

sportsguy