Welcome to SPN

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

Sign Up Now!

Two tables query not pulling all information

Discussion in 'Information Technology' started by lpdc, Nov 2, 2005.

  1. lpdc

    lpdc
    Expand Collapse
    Guest

    I have two tables, one holds Actual data by cost center and account and the
    other holds Budget data by cost center and account. Some of the accounts have
    budget data but no actual data. And the reverse is true where they can have
    actual data but not budget data. How do I get all data to show up in one
    query? Currently the query I have set up excludes accounts that do not have
    data in both tables.

    Thanks,
    Larry
     
  2. Loading...

    Similar Threads Forum Date
    India 11 Constables Get Pregnant at Training School Breaking News Apr 26, 2011
    Warm Up with Winter Vegetables (Vegetarian Casseroles) Cooking & Recipies Feb 7, 2011
    India Two constables arrested for beating, extortion Breaking News Jul 27, 2010
    History Cleaning Out the Augean Stables Punjab, Punjabi, Punjabiyat Aug 25, 2009
    Sikh News Cold weather damages sugarcane, vegetables in Punjab (New Kerala) Breaking News Feb 1, 2008

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Why not build a table that contains a complete listing of cost center and
    accounts. Use it in a left join query to the other tables.

    "lpdc" wrote:

    > I have two tables, one holds Actual data by cost center and account and the
    > other holds Budget data by cost center and account. Some of the accounts have
    > budget data but no actual data. And the reverse is true where they can have
    > actual data but not budget data. How do I get all data to show up in one
    > query? Currently the query I have set up excludes accounts that do not have
    > data in both tables.
    >
    > Thanks,
    > Larry
     
  4. lpdc

    lpdc
    Expand Collapse
    Guest

    I added the table with all cost centers and accounts. I can now pull all of
    the budget data but the query does not pull in data in the actual table that
    does not have a budgeted line item associated with it. So I have all of my
    budgeted accounts but not all of my actual accounts

    "KARL DEWEY" wrote:

    > Why not build a table that contains a complete listing of cost center and
    > accounts. Use it in a left join query to the other tables.
    >
    > "lpdc" wrote:
    >
    > > I have two tables, one holds Actual data by cost center and account and the
    > > other holds Budget data by cost center and account. Some of the accounts have
    > > budget data but no actual data. And the reverse is true where they can have
    > > actual data but not budget data. How do I get all data to show up in one
    > > query? Currently the query I have set up excludes accounts that do not have
    > > data in both tables.
    > >
    > > Thanks,
    > > Larry
     
  5. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Post your SQL by opening the query in design view and click on menu VIEW -
    SQL View. Copy and paste as a reply.

    "lpdc" wrote:

    > I added the table with all cost centers and accounts. I can now pull all of
    > the budget data but the query does not pull in data in the actual table that
    > does not have a budgeted line item associated with it. So I have all of my
    > budgeted accounts but not all of my actual accounts
    >
    > "KARL DEWEY" wrote:
    >
    > > Why not build a table that contains a complete listing of cost center and
    > > accounts. Use it in a left join query to the other tables.
    > >
    > > "lpdc" wrote:
    > >
    > > > I have two tables, one holds Actual data by cost center and account and the
    > > > other holds Budget data by cost center and account. Some of the accounts have
    > > > budget data but no actual data. And the reverse is true where they can have
    > > > actual data but not budget data. How do I get all data to show up in one
    > > > query? Currently the query I have set up excludes accounts that do not have
    > > > data in both tables.
    > > >
    > > > Thanks,
    > > > Larry
     
  6. lpdc

    lpdc
    Expand Collapse
    Guest

    SELECT CCandCE.CostCenter, TGL2005Budget.Description, TGL2005Budget.Period,
    CCandCE.[Cost Element], TGL2005Budget.Amount, TGLRollup.SumOfSumOfAmount
    FROM (CCandCE LEFT JOIN TGL2005Budget ON (CCandCE.[Cost Element] =
    TGL2005Budget.[Cost Element]) AND (CCandCE.CostCenter =
    TGL2005Budget.CostCenter)) LEFT JOIN TGLRollup ON (CCandCE.[Cost Element] =
    TGLRollup.[Cost Element]) AND (CCandCE.CostCenter = TGLRollup.CostCenter)
    WHERE (((CCandCE.CostCenter)=598004) AND ((TGL2005Budget.Period)=1))
    ORDER BY CCandCE.[Cost Element];


    "KARL DEWEY" wrote:

    > Post your SQL by opening the query in design view and click on menu VIEW -
    > SQL View. Copy and paste as a reply.
    >
    > "lpdc" wrote:
    >
    > > I added the table with all cost centers and accounts. I can now pull all of
    > > the budget data but the query does not pull in data in the actual table that
    > > does not have a budgeted line item associated with it. So I have all of my
    > > budgeted accounts but not all of my actual accounts
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > Why not build a table that contains a complete listing of cost center and
    > > > accounts. Use it in a left join query to the other tables.
    > > >
    > > > "lpdc" wrote:
    > > >
    > > > > I have two tables, one holds Actual data by cost center and account and the
    > > > > other holds Budget data by cost center and account. Some of the accounts have
    > > > > budget data but no actual data. And the reverse is true where they can have
    > > > > actual data but not budget data. How do I get all data to show up in one
    > > > > query? Currently the query I have set up excludes accounts that do not have
    > > > > data in both tables.
    > > > >
    > > > > Thanks,
    > > > > Larry
     
  7. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    You have criteria --- TGL2005Budget.Period)=1
    Without a budget there is no entry for the period to match.

    Use the following criteria --- "1" Or Is Null

    "lpdc" wrote:

    > SELECT CCandCE.CostCenter, TGL2005Budget.Description, TGL2005Budget.Period,
    > CCandCE.[Cost Element], TGL2005Budget.Amount, TGLRollup.SumOfSumOfAmount
    > FROM (CCandCE LEFT JOIN TGL2005Budget ON (CCandCE.[Cost Element] =
    > TGL2005Budget.[Cost Element]) AND (CCandCE.CostCenter =
    > TGL2005Budget.CostCenter)) LEFT JOIN TGLRollup ON (CCandCE.[Cost Element] =
    > TGLRollup.[Cost Element]) AND (CCandCE.CostCenter = TGLRollup.CostCenter)
    > WHERE (((CCandCE.CostCenter)=598004) AND ((TGL2005Budget.Period)=1))
    > ORDER BY CCandCE.[Cost Element];
    >
    >
    > "KARL DEWEY" wrote:
    >
    > > Post your SQL by opening the query in design view and click on menu VIEW -
    > > SQL View. Copy and paste as a reply.
    > >
    > > "lpdc" wrote:
    > >
    > > > I added the table with all cost centers and accounts. I can now pull all of
    > > > the budget data but the query does not pull in data in the actual table that
    > > > does not have a budgeted line item associated with it. So I have all of my
    > > > budgeted accounts but not all of my actual accounts
    > > >
    > > > "KARL DEWEY" wrote:
    > > >
    > > > > Why not build a table that contains a complete listing of cost center and
    > > > > accounts. Use it in a left join query to the other tables.
    > > > >
    > > > > "lpdc" wrote:
    > > > >
    > > > > > I have two tables, one holds Actual data by cost center and account and the
    > > > > > other holds Budget data by cost center and account. Some of the accounts have
    > > > > > budget data but no actual data. And the reverse is true where they can have
    > > > > > actual data but not budget data. How do I get all data to show up in one
    > > > > > query? Currently the query I have set up excludes accounts that do not have
    > > > > > data in both tables.
    > > > > >
    > > > > > Thanks,
    > > > > > Larry
     
  8. lpdc

    lpdc
    Expand Collapse
    Guest

    When I used the criteria you provided it pulls the data I needed, but it is
    also pulling every account tied to this cost center from the CCandCE table
    even thought there are no values associated with them in the other two tables.

    "KARL DEWEY" wrote:

    > You have criteria --- TGL2005Budget.Period)=1
    > Without a budget there is no entry for the period to match.
    >
    > Use the following criteria --- "1" Or Is Null
    >
    > "lpdc" wrote:
    >
    > > SELECT CCandCE.CostCenter, TGL2005Budget.Description, TGL2005Budget.Period,
    > > CCandCE.[Cost Element], TGL2005Budget.Amount, TGLRollup.SumOfSumOfAmount
    > > FROM (CCandCE LEFT JOIN TGL2005Budget ON (CCandCE.[Cost Element] =
    > > TGL2005Budget.[Cost Element]) AND (CCandCE.CostCenter =
    > > TGL2005Budget.CostCenter)) LEFT JOIN TGLRollup ON (CCandCE.[Cost Element] =
    > > TGLRollup.[Cost Element]) AND (CCandCE.CostCenter = TGLRollup.CostCenter)
    > > WHERE (((CCandCE.CostCenter)=598004) AND ((TGL2005Budget.Period)=1))
    > > ORDER BY CCandCE.[Cost Element];
    > >
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > Post your SQL by opening the query in design view and click on menu VIEW -
    > > > SQL View. Copy and paste as a reply.
    > > >
    > > > "lpdc" wrote:
    > > >
    > > > > I added the table with all cost centers and accounts. I can now pull all of
    > > > > the budget data but the query does not pull in data in the actual table that
    > > > > does not have a budgeted line item associated with it. So I have all of my
    > > > > budgeted accounts but not all of my actual accounts
    > > > >
    > > > > "KARL DEWEY" wrote:
    > > > >
    > > > > > Why not build a table that contains a complete listing of cost center and
    > > > > > accounts. Use it in a left join query to the other tables.
    > > > > >
    > > > > > "lpdc" wrote:
    > > > > >
    > > > > > > I have two tables, one holds Actual data by cost center and account and the
    > > > > > > other holds Budget data by cost center and account. Some of the accounts have
    > > > > > > budget data but no actual data. And the reverse is true where they can have
    > > > > > > actual data but not budget data. How do I get all data to show up in one
    > > > > > > query? Currently the query I have set up excludes accounts that do not have
    > > > > > > data in both tables.
    > > > > > >
    > > > > > > Thanks,
    > > > > > > Larry
     
  9. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    My limited dataset that I used did not have the problem.

    Try adding Is Not Null as criteria in the empty fields one field at
    a time to eliminate the unwanted data.

    "lpdc" wrote:

    > When I used the criteria you provided it pulls the data I needed, but it is
    > also pulling every account tied to this cost center from the CCandCE table
    > even thought there are no values associated with them in the other two tables.
    >
    > "KARL DEWEY" wrote:
    >
    > > You have criteria --- TGL2005Budget.Period)=1
    > > Without a budget there is no entry for the period to match.
    > >
    > > Use the following criteria --- "1" Or Is Null
    > >
    > > "lpdc" wrote:
    > >
    > > > SELECT CCandCE.CostCenter, TGL2005Budget.Description, TGL2005Budget.Period,
    > > > CCandCE.[Cost Element], TGL2005Budget.Amount, TGLRollup.SumOfSumOfAmount
    > > > FROM (CCandCE LEFT JOIN TGL2005Budget ON (CCandCE.[Cost Element] =
    > > > TGL2005Budget.[Cost Element]) AND (CCandCE.CostCenter =
    > > > TGL2005Budget.CostCenter)) LEFT JOIN TGLRollup ON (CCandCE.[Cost Element] =
    > > > TGLRollup.[Cost Element]) AND (CCandCE.CostCenter = TGLRollup.CostCenter)
    > > > WHERE (((CCandCE.CostCenter)=598004) AND ((TGL2005Budget.Period)=1))
    > > > ORDER BY CCandCE.[Cost Element];
    > > >
    > > >
    > > > "KARL DEWEY" wrote:
    > > >
    > > > > Post your SQL by opening the query in design view and click on menu VIEW -
    > > > > SQL View. Copy and paste as a reply.
    > > > >
    > > > > "lpdc" wrote:
    > > > >
    > > > > > I added the table with all cost centers and accounts. I can now pull all of
    > > > > > the budget data but the query does not pull in data in the actual table that
    > > > > > does not have a budgeted line item associated with it. So I have all of my
    > > > > > budgeted accounts but not all of my actual accounts
    > > > > >
    > > > > > "KARL DEWEY" wrote:
    > > > > >
    > > > > > > Why not build a table that contains a complete listing of cost center and
    > > > > > > accounts. Use it in a left join query to the other tables.
    > > > > > >
    > > > > > > "lpdc" wrote:
    > > > > > >
    > > > > > > > I have two tables, one holds Actual data by cost center and account and the
    > > > > > > > other holds Budget data by cost center and account. Some of the accounts have
    > > > > > > > budget data but no actual data. And the reverse is true where they can have
    > > > > > > > actual data but not budget data. How do I get all data to show up in one
    > > > > > > > query? Currently the query I have set up excludes accounts that do not have
    > > > > > > > data in both tables.
    > > > > > > >
    > > > > > > > Thanks,
    > > > > > > > Larry
     

Share This Page