Here is what I need to do, any help is appreciated
I have 2 tables
Table 1. Budget Table that is limited to department level - 4000 rows
Table 2. An Actual table that goes all the way down to line level 350,000 rows
I can't create a relationship since it gives me an error "missing intermediate data to connect two columns"
I would like to either aggregate my line level table so that it sums up to department level and join the Budget Table using merged queries, the problem is any join I use takes ages and I dont really have the patience to wait and see if that works especially if the wait time is a recurring thing, am i doing something wrong here?
The other thing I was thinking about was creaating a new table (table 3) that aggregates Table 2 at department level and I create a relationship or a merged query between Table 1 and Table 3 - Any suggestions on how do I create that third table? I would I have thought it to fairly simple to create a pivot table using pivot columns in a new table. Just can't seem to get my head around it.
Thanks
