Query layering - best practice

[Tskutnik]

All,
My database is fairly complicated and requires a lot of data manipulation. Being a novice my approach has been query on top of query on top of query to get the data into the shape I need.
So the question is best practice on eliminating some of these queries. I'm not sure if a stored procedure approach is the answer or I just do the best I can with the query layers.
Please note that I am new to Access so very much need to stay in Access-land for now. Bridging out (into SQL server, etc..) is not yet within my grasp - unless someone can point me to a tutorial.. plus I'm probably limited to stay within Assess for technical setup reasons, at least until I learn how to set myself up otherwise.
I recognize the question is very general and depends on database size, indexing, query efficiency, etc... so please assume the worst and most complicated situation possible when responding.
Thanks for any help

 

[Ranman256]

Unless your dealing with half million records or more, I've never had a problem with local queries.
If you have millions, or very slow connections, then I'd use stored process.

 

[Tskutnik]

Unfortunately there are well over a million records and some of my queries are 4 deep so the database is getting hard to manage. If nothing else there are just so many queries to keep organized.
I've tried consolidating them but it is not so easy considering the data I'm starting with and the complexity of the deliverable.
I added some Create Table steps, but you know how that works.... having to refresh/update the tables is another step and takes out some of the advantages of begin dynamic with the source.

 

[Tskutnik]

Re: Stored procedure - can someone point me to a good how-to Stored Proc for dummies thing? I looked around and all of them take me outside of Access and into SQL server.

 

[jdraw]

See this link for info.

I hope it's useful to you.

 

[plog]

Could you better explain what your doing with Access? I've used Access as a program (allowing users to manager their data via forms and reports, etc.) and as a Data Warehousing tool (extracting data from various sources, manipulating it and creating a finished dataset).

How would you describe yours? And what briefly highlight the process you are doing and how long it takes and its frequency.