Redundant use of same set of data in joined queries

[d2ward]

First of all, let me say that this is my first post on this forum. I've come here seeking help (in hopes of impressing my boss with the results of some analysis I'm trying to perform), so I hope someone can provide some guidance. That said -- I’m not even certain that this can be done but, then again, I'm not exactly an Access Power User. It seems like it should be possible without a great deal of trouble – I’m just not sharp enough to figure it out.



Here’s the situation – I have a set of A to B criteria (there are 18315 pairs). Each pairing has an associated value. After one pairing, the “B” criterion becomes the new “A”, and the evaluation can occur again, using the same 18315 pairs as used the first time. The A to B data values do not change. For example:
A1: B1 = 90 points
B1 (or “new A”): C1 (“new B”) = 45 points

Thus far, I have been able to achieve a running total for A1 to B1 to C1 (in the example above, the cumulative value would be 135 points). I’ve done this by creating queries to make an A to B table, a B to C table, and a C to D table. Using the A to B table, I link the B to C table (using a Level 2 join between the “B” criterions) and create an A to B to C table.


However, if I try to add the 4^th level (i.e., A to B to C to D),


Access returns an error – “The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result.”


I would like to achieve this 4^th level of data extraction. I am far from an Access wizard , and I would wager that it is my inelegant query and table construction that is causing the problem.
Any suggestions? Please let me know if there is any additional info I need to provide to help solve this puzzle.

Thanks!

[ranman256]

Sometimes when linking that deep into queries, Access must build temporary tables and the results exceed the 2 Gig limit.

To get around this I had to take the 1st queries into a table. Then from there do more queries.
Usually I would whittle the table down to where I need it, or as you may... build table1, then from there table 2, etc.
sparing the many many temp tables.