help joining 2 queries

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

 

[tina]

how about joining the records of the two tables in a Union
Query first and then doing the summarizing and math etc on
that query, rather than doing it on each table separately
and then trying to put the results together.

hth

 

[Jeff Boyce]

Jason

The disadvantages of maintaining an "historical" table includes, as you've
found, difficulty recombining it with the "active" table. Moreover, you can
get issues with synchronization (as you also found), and must add a
process/code to handle 'moving' information back/forth between tables.

An alternate approach would be to add a single Yes/No field to your "active"
table, and call it "Historical". When a row/account/whatever is judged to
be "historical", check that field. With a design like this, you can limit a
view (query) to those records with a "No" in the Historical field (i.e., the
"active" rows), or just those with a "Yes" (i.e., Historical).

And when you want to combine them all, don't bother to include that (Y/N)
field!

Good luck

Jeff Boyce
<Access MVP>