Union Queries

This question is one that I'm not quite sure of what and how to ask, so please indulge me. I'm sure its almost impossible to answer, but any thoughts would be appreciated.

I've got a database using Access 2000 which is somewhat complicated.

I need to return or report data as such:

Code Activity Total
001 Name 5
002 Name 2
003 Name 4
004 Name 6
thru
152 Name 15

The codes are broken down into 5 different categories and a number of tables. A report in numerical order is required.

For each category, there are approx 5 tables linked to one table. I am returning a count/sum/total of records from each of these tables using a parameter query which also contains 3 where clauses. The parameters in each query is referenced to [Forms]![ReportDialog]![Field].

Using a Union Query, the parameter queries in a category is then combined into one.

A second union query is then used to combined the 5 category union queries in one.

On the surface, all of this seems to work. My question centers around performance and load. Can you potentialy see a problem with this senario down the road. What I don't want to happen is this work fine for 6 or 8 months, and then all of a sudden, performance suffers or it cease to operate. The main linked table in each category will have maybe 8000 records each per year, with the linked tables to each main table containing that number or less.

Again, I know the question leaves a lot of what ifs, but any thoughts would be appreciated.

Thanks

Ernest L. Kendricks

Performance and load has a lot to do with how often this report is being run. Do you have an estimate on that?

Complex queries like that tend to be a bit slow in Access in general though. If you're using a programming language to access this report you might consider combining your data at that level rather than having the db union it together.

Thanks for the reply based on such a vague question:

I'm calling the report from Access. The report will be run each day, filtered by say 12 employees. Therefore it will be run 12 to 13 times per day, then weekly, then monthly.

I'm not awfully concerned about absolute speed (a couple seconds here and there), but rather serious operating problems/crashes down the road when the database is pouplated with thousands of records.

Thanks again for trying to give a reasonable answer to such an open ended question.

Ernest

From what I remember when I looked into different database performace levels Access seems to cap out at around 100,000 records per table.

I don't think you'll run into problems for a while with it, though you should periodically compact the db to keep the file size down and performance up. And of course back it up from time to time.

Access is very easy to scale up from so if you outgrow it you can move your data to FoxPro or SQL Server without much trouble.

Thanks for the info... I think it will be years before the tables reach a 100,000 max.

Ernest L. Kendricks