Need Advice on Queries

[ebarrera]

Forgive me, but this would be my first time ever creating a database from a flat file. The flat file that is being sliced and diced many ways to get values needed for payroll and assignment tracking. The way I am handling it now is that I am creating chain queries and am realizing that the deeper I go with this, the longer it takes to run a query. Do you think I need to start looking into temp tables when I get a system resource exceeded error?

I am guessing that I should create a query, then transfering the data results to a table. Then create a query from that table to later export into another table and so on. Is this possible and what is the correct name for this process? I am also think that a macro can trigger the chain. All help is greatly appreciated.

 

[stopher]

For data analysis and more complex and routine reporting, it is quite usual to export the data from the live database to a "data warehouse" (I use the phrase "data warehouse" in the general sense here). Tables in the data warehouse will usually be optimised for your reporting requirements and generally do not follow the rules of normalisation. They will often be aggregated.

You can certainly take a step by step approach to creating these stand alone tables and macros will do this fine. Just build up your queries one by one then run them sequentially using a macro. Not that you need to be sure that you can re-generate your reporting tables from scratch because if a macro fails half way through then you could end up with duplicate of missing data in your data warehouse.

hth
Chris

 

[ebarrera]

Thanks Stopher, I am exporting the queries as we speak and manually importing them back into a table. It is sort of a data warehouse for this particular part of the database. I hope to run this 2-3 times a month for our office purposes. Later on, it would be something I can run every night and send reports to each department. Still need to research the macro part, but will tackle that when I get there. Thanks.