Two tables query not pulling all information

L

lpdc

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
 
K

KARL DEWEY

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.
 
L

lpdc

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
 
K

KARL DEWEY

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

lpdc

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];
 
K

KARL DEWEY

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...
 
L

lpdc

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...
 
K

KARL DEWEY

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...
 
Top