Stumped ... not sure if this is a junction table issue --

[Guest]

Hi,

Let me preface by saying I'm somewhat new to Access. Ok the problem is that
when I create a query to obtain actual and budgeted revenue by cost center,
some records are missing because some cost centers either have revenue and no
budget or vice versa. (I know, bad budgeting! but it wasn't me!) I've tried
the various join options and can either get actuals to tie-out or budget, but
not both.

The source data are tables linked directly to MAS 500 tables. Any hints or
suggestions are greatly appreciated.

Thank you.

Allan

(In case you're wondering, we're trying to use Access for metrics reporting
that will include other non-financial data not available in MAS 500)

 

[Pat Hartman\(MVP\)]

You need a FULL OUTER JOIN which Access does not directly support. You can
simulate this join by creating a left join and a right join of the two
tables and then unioning the two joins.

UnionQuery:
Select ....
From tblA Left Join tblB On tblA.fld1 = tblB.fld1
Union All
Select ....
From tblA Right Join tblB On tblA.fld1 = tblB.fld1;

Since union queries are SQL specific, the QBE will not build them for you.
Given that I'm lazy and hate typing, my solution is to build the individual
select queries and then reference them in the union query.

UnionQueryFromSavedQueries:
Select * from query1
Union Select * from query2;

When creating a union query you must use the same number of fields and same
field order in all select clauses. The names are not important but the
resulting recordset will take the names of the first query. If you have
missing fields in one query or another, add constants in their places:

Select "Hello" As Fld1, fld2, fld3 from YourTable
Union Select fld1, fld2, "Opps" As fld3 from YourTable;

 

[Guest]

Thank you for the reply, Pat. I had already created individual select
queries for actuals & budget, so now I will attempt to create a union query
from those queries as you suggest.

To make sure that I understand, since I already have the individual select
queries I will not need to create the first union query you describe (joining
tables) -- instead I can skip ahead to the "UnionQueryFromSavedQueries"?

Thanks again!

Allan

 

[Guest]

Pat, please ignore my follow-up question to you. Your suggestions worked
perfectly! Thank you very much!

Allan