Complex Queries

[swambi]

Hi, I have looked for a post that addresses my needs but could not find one. I will try and summarize my problem simply and elaborate from there.

My company have a software application that is no longer supported by the developers (Actually, it hasn't been for about 4 years). Although it is buggy as heck, it offers some useful functions, like the ability to add works orders, track works orders etc. Quite standard but it's all they have and we want to keep using the program. It uses a MS access database, which has almost 10 years of data in it. I want to view this data in Excel but here's my problem.



The data I need is spread over about 10 tables. The main table 'leads' details the highest level of the order. For example order id 1 is for a qty of 100 and this could consist of 10 different product types, the information of which is in 10 different tables. And for each product type there may be multiple rows for different size products of that type. The order id is listed in each table but I can't seem to make a query that will return all the results from all of the tables or summarize the qty of any one type of product for each order id.

I simply want to see in Excel an order for 100 pieces, made up of 20 type 1, 30 type 2, 40 type 3 - and so on. My assumption is that this query must be run in access and then imported in Excel.

I am a beginner so any advice is appreciated.

[aytee111]

It sounds like you have a leads table, join this to each of the 10 product type tables (right-click on the join and select the option that says all records for leads table and only those that match). That will give you a starting query with all data. From that you can make other queries that summarize, etc.

[ranman256]

there should have been 2 tables: the tOrder and tOrderDetails.
If you have 10, then you need a union query to combine into a single entity.
select * form table1
union
select * form table2
union
select * form table3
etc

now you will have 2 'tables', by joining the main order to this qnOrderDetails
make another query off this to filter a single order.
then export to excel via
docmd.transferspreadsheet....

[swambi]

Thank you all. I'll try this tomorrow. I'll let you know how I get on. Can I just check; if this works and I view this data in Excel, by importing the query, does it lock the db? If so, would it suffice to work from a copy of the database that is updated twice daily?