Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by KonaAl, Jul 28, 2006.

  1. KonaAl

    KonaAl
    Expand Collapse
    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)
     
  2. Loading...

    Similar Threads Forum Date
    Human torch’ baby tests normal, doctors stumped Business, Lifestyle & Leisure Aug 13, 2013
    Scientists, Aethists, Religions Stumped on Life outside of Earth: Sikhism "Not" Interfaith Dialogues Mar 6, 2011
    Sikh News Jo Bole... stumped by sexy innuendo (Express India) Breaking News May 15, 2005
    Lost Treasures Of The Sikh Kingdom - BBC Documentary History of Sikhism May 28, 2016
    welcome to the pleasuredome-Frankie goes to Hollywood Blogs Oct 17, 2015

  3. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    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;


    "KonaAl" <KonaAl@discussions.microsoft.com> 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)
     
  4. KonaAl

    KonaAl
    Expand Collapse
    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


    "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:
    >
    > Select "Hello" As Fld1, fld2, fld3 from YourTable
    > Union Select fld1, fld2, "Opps" As fld3 from YourTable;
    >
    >
    > "KonaAl" <KonaAl@discussions.microsoft.com> 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)

    >
    >
    >
     
  5. KonaAl

    KonaAl
    Expand Collapse
    Guest

    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
    > 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" <KonaAl@discussions.microsoft.com> 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)

    >
    >
    >
     

Share This Page