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

#1 (**permalink**) 28-Jul-2006, 08:26 AM

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12093-stumped-not-sure-if-junction-table.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12093
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)

#2 (**permalink**) 28-Jul-2006, 08:26 AM

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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12093
Select * from query1
Union Select * from query2;
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12093

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;

"KonaAl" wrote in message
news:254AF1D3-45CD-45CF-8EE8-D55FB1F0563E@microsoft.com...
> 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)

#3 (**permalink**) 28-Jul-2006, 08:26 AM

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

"Pat Hartman(MVP)" wrote:

> 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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12093
>
> Select "Hello" As Fld1, fld2, fld3 from YourTable
> Union Select fld1, fld2, "Opps" As fld3 from YourTable;
>
>
> "KonaAl" wrote in message
> news:254AF1D3-45CD-45CF-8EE8-D55FB1F0563E@microsoft.com...
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12093
> > 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)
>
>
>

#4 (**permalink**) 28-Jul-2006, 08:26 AM

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

Allan

"Pat Hartman(MVP)" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12093
> 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;
>
>
> "KonaAl" wrote in message
> news:254AF1D3-45CD-45CF-8EE8-D55FB1F0563E@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12093
> > 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)
>
>
>