Query Problem - extracting data from two similar queries

[Skyshadow5]

I'll try to keep this short with as much data as poss.
I have two queries based on a budgets table. One extracts the actual spend by period based upon the unique identifier "A" for actual and the other extracts the budget based upon the unique identifier "B" for budget. I may therefore have entries in the actual query and not in the budget if no budget is necessary but I may also have entries in the budget query if there is a budget but there has been no spend for that period. The criteria specifies cost centres to query between a certain range and overhead codes between a certain range. There are 122 overhead codes and 56 cost centre codes. There may or may not be entries some of the combinations. I need to combine my two queries, "Actual by peiod" and "Budget by period" but when I do I get multiple repetitions of the entries. How do I correctly specify that I want all unique entries from Actual, all unique entries form Budget and only one instance of the entries that have the same cost centre / overhead combination?

 

[r937]

sounds like a full outer join

for syntax purposes, i'll assume the join condition is to match both costcentre and overhead

   select ... 
     from A 
   full outer 
     join B 
       on A.costcentre = B.costcentre
      and A.overhead   = B.overhead

since access doesn't support full outer joins, you have to use an equivalent

one way to do it is

   select ... 
     from A 
   left outer 
     join B 
       on A.costcentre = B.costcentre
      and A.overhead   = B.overhead 
   union all
   select ... 
     from B 
    where not exists
          ( select 1 
              from A
             where costcentre = B.costcentre
               and overhead   = B.overhead )

the first query (left outer join) gets all rows that match, plus any rows of A that don't have a match

the second query (not exists) gets any rows of B that don't have a match

combined with a UNION ALL, it's a full outer join

note: UNION ALL, not UNION, because there cannot be any duplication of rows across the two subqueries

be careful in composing the second SELECT list -- in the first subquery, you use fields from both A and B, but in the second, where you are only selecting from B, you have to use "placeholder nulls" in the A columns

for example,

   select A.one, A.two, B.three, B.four 
     ...
   union all
   select null, null, B.three, B.four  
     ...

rudy