Database design help!

[wisey01]

Hey all,

Am currently planning the re-design of a database I made for work. It was buggy, over-complex and I am hoping that you might be give me some help about how to get around a few of the errors I encountered last time. Be warned I am very new at this.

Firstly, is there a way of getting around mass Union Queries to aggregate data? I have 200 tables which need to be processed by the same query, and output into a single table. First time around I used Union Queries and then they got too big so I had to Union Query the Union Queries together. And then THAT got too big so I had to Append the original Union Query and then Union the table outputs together. (I know, I know.....)

Secondly, (assuming there is no answer to part 1) does anyone know what script I would need to run to automatically run the relevant appends and queries automatically instead of having to run each one individually?

Thanks in advance,

Greg

 

[jdraw]

Database design is centered on having tables and relationships that match your business needs. Getting the tables and relationships set up and tested is critical. You haven't told us about your business or current database.
What is you business? What are the major entities?
Here is a tutorial I highly recommend for creating a meaningful data model.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck.

 

[Pat Hartman]

If you have 200 tables with the same type of data, that's where you need to start. You should have only ONE table. You will probably have to add a new column so you can identify which of the 200 the data came from. Once you are down to a single table, you'll be able to clean up all the forms/reports/code/queries that were needed to process the 200 tables.

To select a subset of data from the one table so you can get back to table #39, just use a query with a where clause. The criteria will probably come from a form. Do NOT hard-code it anywhere.

 

[wisey01]

I think i probably need to explain a little better.

The 200 tables are needed as each record contains a series of steps to complete, and there can be as many as 30 records. I floated the idea of having them altogether but management wanted it seperate, which to be fair makes the testing process a fair bit easier.

Is there a way that you can create a query to apply to all tables with a particular naming convention? I am really trying to steer clear of one giant table with 4000 records.

 

[spikepl]

but management wanted it seperate

I always make a deal with the management. They manage, and I handle the database. They would get upset if I managed, the same way I would get upset if they fiddled with the db.

which to be fair makes the testing process a fair bit easier.

No it doesn't. Having to have 400 queries does absolutely nothing of the sort. Data denormalization, as you have implemented it, has the consequence of huge overheads on ALL work: queries, forms, reports, maintenance, data updates, you name it.

Follow Pat's advice. If management interferes, offer to trade jobs with them