Cascading Joins

[MikeP_(Guest)]

I am using the SQL version and attempting to join 12 tables inside of an Access database. Six tables contain annual revenue data and six contain annual expense data. All 12 tables have a common field (facilityID). My objective is to develop a time series report which can pull in data from each year to show trends in both revenues & expenses in a single R&R Report.____What is the easiest way to "daisy chain" all of them together using a common field (facilityID). My master table will be Rev2003 (most recent revenue data). Should I sequentially link the 2003 revenue table initially to the 2003 expense table, and in turn, link to all prior years? Should all revenue tables be linked in a chronological order then linked to all chronological expense tables in a continuing sequence? Is there a limit on the number of cascading joins from the master table? ____Is there anything unique about the Access file that now contains all 12 separate tables that I should have specified prior to creating the ODBC connection inside R&R?____Overall, the linking process is quite slow as each table seems to be adding to the "cumulative" overhead in terms of processing time based on the increasing read/write access noises coming from my hard drive as it cumulatively spins its way down the database...____Any suggestions would be appreciated...____Thanks for any insight in terms of strategy or tactics...____Mike P.__Buffalo, NY__

[kfleming]

Have you tried creating a query in Access to perform the joins there and then use R&R against the resulting query table? Not sure that it will be better but might be worth a try.____Kathleen__R&R Support

[MikeP_(Guest)]

Tried to perform all the joins inside Access but when I ran a Make Table query - an error message popped up about too many tables or columns, etc. So I went back into R&R and made all of the joins I needed, it took awhile as the nine other tables were linked to a common field in the master table. The only issue is when I first open the report, there must be more than 3+ minutes of "pre-processing" inside R&R before the report competely loads...I will see how it goes as I develop a series of summary reports using the joined tables...seems to be working O.K. for now....