multi level query with sum queries etc (slow/repeating)

[InstructionWhich7142]

I've got a number of tables which I'm combining into a report by "Part",

However due to the layout of the tables (for example, Stock or Orders - having the same part multiple times in different places) I have to group and sum these with their own Totals query before I can combine them otherwise I get crazy duplication issues (cartesian product style)

I then have to go through a few other steps and pull in some other information and set some criteria on a few levels before I can pull all the queries together (~20 in total, based off ~10 tables) into the final report,

This ends up taking a lot of time (tables are linked ODBC FoxPro tables) - can't import due to 2gb limit.

However if I output a few key queries to tables before I run the next level the time goes down significantly - it seems some queries get re run multiple times along the way

Is there some way to force JET to be a bit cleverer about this without having a load of faff with "make tables"?

 

[CJ_London]

Are you able to use SELECT DISTINCT rather than group by?

for example instead of

SELECT OrderNo, PartNo, sum(quantity) as TtlQuantity
FROM myTable
Group BY OrderNo, Partno

try

SELECT DISTINCT OrderNo, PartNo, (SELECT sum(quantity) from myTable as tmp WHERE OrderNo=myTable.OrderNo and PartNo=myTable.PartNo) as TtlQuantity
FROM myTable

to see if you get a performance improvement

Also, proper indexing of the foxpro tables will have an improvement if not already done.

Providing a single table does not exceed the 2gb limit, you can import the data into different back end db's - and just import the data you need, not the whole table. So for example you may not need all the fields in the foxpro table nor all the rows.

Using make tables is not that much faff - convert a select query to a make table or better an update query so the destination table is properly indexed and amend your next query to work off the table rather than the select query. Then a bit of vba behind a button to ensure each table is populated in order.