Two tables query not pulling all information

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
Post your SQL by opening the query in design view and click on menu VIEW -
SQL View. Copy and paste as a reply.
 
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];
 
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 said:
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 said:
Post your SQL by opening the query in design view and click on menu VIEW -
SQL View. Copy and paste as a reply.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Post your SQL by opening the query in design view and click on menu VIEW -
SQL View. Copy and paste as a reply.

:

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

:

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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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];


:

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

:

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

:

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.

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Summary Reports from Two Tables 2
Stumped ... not sure if this is a junction table issue -- 3
how do i calculate percentage of change between two or more field. 3
Nested IIF Statements 2
Running Total Cost 3
Query -Change Column Headings each time 2
Add records to a table from a query 2
Duplicate Query assistance 2

Back
Top