Hello,
I have an Access DB which I use to create an Excel cost report. There are different queries in Access which lead to the creation of this report. There are different subcosts, like for services, parts and others. The person who worked on this previously, summed up the costs per department by doing a Left join between the subcosts. So it'd be like FROM services LEFT JOIN parts ON ... This is clearly not ok, because for a total cost I want to include the combinations in parts as well, even if some combination doesn't exist in services. So what I did was a full outer join by using a left join then a union with a right join. All good until now.
The problem is that I need to do a full outer join for 4 of these subcost queries and Access only allows me to do 2. When I try to add a third one, it says "Cannot open any more databases". I assume this is because there are many queries being done in the background. I tried to save the queries in steps of joining, but it seems they are not cached and I have the same problem. Is there any way to cache the results of these queries stepwise?
Or any other solution/blindspot I am missing out on?
Would appreciate some responses so much!