Joining - Help me save what hair I have left

[Jason Trolian]

I want to try to make this is understandable and
uncomplicated as possible. I have asked for help before
and all that have answered have been nothing but helpful.
So in advance I appreciate everyones help.

Here goes:

2 queries
#1 HOFFE RECOVERY: COLACCT (accounts active)
#2 HOFFE RECOVERY: COLACHIS (historical accounts)

#1 query shows: account category (this is the identifier
that I am trying to group my results by) total original $
value of accounts placed, remaining balance of accounts
placed, and total collected (original balance - current
balance)

#2 query shows: shows the same information for accounts in
history (different table)

Running each of these queries individually I come up with
the correct totals.

The problem I am having is not every actcat(account
category) is listed in query#2 that is listed in query#1
so in this querry below when I try to get the totals the
information from each query i only get results if the
specifc acct category (actcat) is listed in both other
wise it does not take the "null" field and assume it is
$0.00 and just do the math. It simply reports a blank
output. I have tried every possible variation of joins
that I can think of with no positive results. below is
the SQL view of the "Totaling query". If you can help me
you certinly will make my christmas card list at the very
least. I have a client comming in on Monday and need to
have these numbers available for a meeting. Thank you in
advance.

Jason

SELECT [HOFFE RECOVERY: COLACCT].actcat, [hoffe recovey:
total placed data SUMMARY.SumOfactprin]+[hoffe recovery:
total placed history SUMMARY.SumOfactprin] AS [TOTAL
PLACED], [hoffe recovery: total placed collectable
SUMMARY.SumOfactprin]+[hoffe recovery: total placed
collectable history SUMMARY.SumOfactprin] AS [PLACED
COLLECTABLE], [Total Collected]+[Total Collected Hist] AS
[COLLECTED GROSS]
FROM [HOFFE RECOVERY: COLACCT], [HOFFE RECOVERY: COLACHIS]
GROUP BY [HOFFE RECOVERY: COLACCT].actcat, [hoffe recovey:
total placed data SUMMARY.SumOfactprin]+[hoffe recovery:
total placed history SUMMARY.SumOfactprin], [hoffe
recovery: total placed collectable SUMMARY.SumOfactprin]+
[hoffe recovery: total placed collectable history
SUMMARY.SumOfactprin], [Total Collected]+[Total Collected
Hist];

 

[Jason Trolian]

I failed to mention that I am using Access 2000

 

[Kelvin]

How about converting you 2 queries into 1 union query, then doing a sum of
the result of this union.

Kelvin

 

[John Spencer (MVP)]

You seem to be missing some tables in the query you posted or at least I can't
distinguish what your query should have. I am guessing you must have some other
tables/queries such as [hoffe recovey: total placed data SUMMARY] and [hoffe
recovery: total placed history SUMMARY]. You have not bracketed them correctly
in your posted SQL statement. Also, you haven't included them in the FROM clause.

If I am wrong, then perhaps you can explain what these are.


SELECT [HOFFE RECOVERY: COLACCT].actcat, [hoffe recovey:
total placed data SUMMARY.SumOfactprin]+[hoffe recovery:
total placed history SUMMARY.SumOfactprin] AS [TOTAL
PLACED], [hoffe recovery: total placed collectable
SUMMARY.SumOfactprin]+[hoffe recovery: total placed
collectable history SUMMARY.SumOfactprin] AS [PLACED
COLLECTABLE], [Total Collected]+[Total Collected Hist] AS
[COLLECTED GROSS]
FROM [HOFFE RECOVERY: COLACCT], [HOFFE RECOVERY: COLACHIS]
GROUP BY [HOFFE RECOVERY: COLACCT].actcat, [hoffe recovey:
total placed data SUMMARY.SumOfactprin]+[hoffe recovery:
total placed history SUMMARY.SumOfactprin], [hoffe
recovery: total placed collectable SUMMARY.SumOfactprin]+
[hoffe recovery: total placed collectable history
SUMMARY.SumOfactprin], [Total Collected]+[Total Collected
Hist];

I want to try to make this is understandable and
uncomplicated as possible. I have asked for help before
and all that have answered have been nothing but helpful.
So in advance I appreciate everyones help.

Here goes:

2 queries
#1 HOFFE RECOVERY: COLACCT (accounts active)
#2 HOFFE RECOVERY: COLACHIS (historical accounts)

#1 query shows: account category (this is the identifier
that I am trying to group my results by) total original $
value of accounts placed, remaining balance of accounts
placed, and total collected (original balance - current
balance)

#2 query shows: shows the same information for accounts in
history (different table)

Running each of these queries individually I come up with
the correct totals.

The problem I am having is not every actcat(account
category) is listed in query#2 that is listed in query#1
so in this querry below when I try to get the totals the
information from each query i only get results if the
specifc acct category (actcat) is listed in both other
wise it does not take the "null" field and assume it is
$0.00 and just do the math. It simply reports a blank
output. I have tried every possible variation of joins
that I can think of with no positive results. below is
the SQL view of the "Totaling query". If you can help me
you certinly will make my christmas card list at the very
least. I have a client comming in on Monday and need to
have these numbers available for a meeting. Thank you in
advance.

Jason

SELECT [HOFFE RECOVERY: COLACCT].actcat, [hoffe recovey:
total placed data SUMMARY.SumOfactprin]+[hoffe recovery:
total placed history SUMMARY.SumOfactprin] AS [TOTAL
PLACED], [hoffe recovery: total placed collectable
SUMMARY.SumOfactprin]+[hoffe recovery: total placed
collectable history SUMMARY.SumOfactprin] AS [PLACED
COLLECTABLE], [Total Collected]+[Total Collected Hist] AS
[COLLECTED GROSS]
FROM [HOFFE RECOVERY: COLACCT], [HOFFE RECOVERY: COLACHIS]
GROUP BY [HOFFE RECOVERY: COLACCT].actcat, [hoffe recovey:
total placed data SUMMARY.SumOfactprin]+[hoffe recovery:
total placed history SUMMARY.SumOfactprin], [hoffe
recovery: total placed collectable SUMMARY.SumOfactprin]+
[hoffe recovery: total placed collectable history
SUMMARY.SumOfactprin], [Total Collected]+[Total Collected
Hist];