Database BEAST

[KC_Cheer_Coach]

The database that I thought would take me 2 weeks has taken me much longer. I
have two files that I import from .csv pipe delimeted into Access 2003. Table
1 is called CURRENT and Table 2 is called PREVIOUS. They are exactly the same
except that one has the current month's account info and one has the previous
month's accounts info.

The columns in each table are as follows: master, subacct, description,
custid, custdiscount, masterdiscount.

I need to create a comparison where I get ALL information from each table
and return a table with the following: master, subacct, description, custid,
previous.custdiscount, current.custdiscount, previous.masterdiscount,
current.masterdiscount, custdiscount difference, masterdiscount difference.

This should be easy, right? Left Join Current on Previous, Left Join
Previous on Current, Inner Join Current on Previous, stick them together in
one table, run a duplicate query and spit it out. Alas, what I thought should
just work...isn't. I am really making a mess over here.

Suggestions?

 

[Bob Barrows [MVP]]

The database that I thought would take me 2 weeks has taken me much
longer. I have two files that I import from .csv pipe delimeted into
Access 2003. Table 1 is called CURRENT and Table 2 is called
PREVIOUS. They are exactly the same except that one has the current
month's account info and one has the previous month's accounts info.

The columns in each table are as follows: master, subacct,
description, custid, custdiscount, masterdiscount.

The key fields appear to be master, subacct and custid... but why are you
making us guess?
Not really necessary in this situation. but it never hurts to tell us the
datatypes of these fields.

What does "description" refer to? The master field? Or the Subaccount?

I need to create a comparison where I get ALL information from each
table and return a table with the following: master, subacct,
description, custid, previous.custdiscount, current.custdiscount,
previous.masterdiscount, current.masterdiscount, custdiscount
difference, masterdiscount difference.

This should be easy, right? Left Join Current on Previous, Left Join
Previous on Current, Inner Join Current on Previous, stick them
together in one table,

Could someone jump in here and tell me if Access supports full outer joins
now?
If not, this is a situation for a union query. You don't need the inner join
part. Create a saved query (call it UnionOfCurrentAndPrevious if you like)
with this SQL (create a query in Design View and swtich to SQL View):

Select c.master CurrentMaster, c.subacct CurrentSubacct,
c.Description CurrentDescription, c.custid CurrentCustid,
p.master PreviousMaster, p.subacct PreviousSubacct,
p.Description PreviousDescription, p.custid PreviousCustid,
c.custdiscount CurrentCustdiscount,
c.masterdiscount CurrentMasterdiscount,
p.custdiscount PreviousCustdiscount,
p.masterdiscount PreviousMasterdiscount,
Nz(c.custdiscount,0) - Nz(p.custdiscount,0) [custdiscount difference],
Nz(c.masterdiscount ,0) - Nz(p.masterdiscount ,0) [masterdiscount
difference]
FROM [current] c left join [previous] p
ON c.master = p.master and c.subacct = p.subacct
and c.custid = p.custid
Union
Select c.master CurrentMaster, c.subacct CurrentSubacct,
c.Description CurrentDescription, c.custid CurrentCustid,
p.master PreviousMaster, p.subacct PreviousSubacct,
p.Description PreviousDescription, p.custid PreviousCustid,
c.custdiscount CurrentCustdiscount,
c.masterdiscount CurrentMasterdiscount,
p.custdiscount PreviousCustdiscount,
p.masterdiscount PreviousMasterdiscount,
Nz(c.custdiscount,0) - Nz(p.custdiscount,0) [custdiscount difference],
Nz(c.masterdiscount ,0) - Nz(p.masterdiscount ,0) [masterdiscount
difference]
FROM [current] c right join [previous] p
ON c.master = p.master and c.subacct = p.subacct
and c.custid = p.custid

 

[John Spencer]

LEAP!!!!

Access does not support full outer joins.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Bob Barrows [MVP] wrote:

 

[KC_Cheer_Coach]

I am so sorry. Yes, the master, subacct and custid fields are key. The
description refers to the subaccount.

Thank you for replying back so quickly. I will try what you said right now.

Thanks!!

The database that I thought would take me 2 weeks has taken me much
longer. I have two files that I import from .csv pipe delimeted into
Access 2003. Table 1 is called CURRENT and Table 2 is called
PREVIOUS. They are exactly the same except that one has the current
month's account info and one has the previous month's accounts info.

The columns in each table are as follows: master, subacct,
description, custid, custdiscount, masterdiscount.

The key fields appear to be master, subacct and custid... but why are you
making us guess?
Not really necessary in this situation. but it never hurts to tell us the
datatypes of these fields.

What does "description" refer to? The master field? Or the Subaccount?

I need to create a comparison where I get ALL information from each
table and return a table with the following: master, subacct,
description, custid, previous.custdiscount, current.custdiscount,
previous.masterdiscount, current.masterdiscount, custdiscount
difference, masterdiscount difference.

This should be easy, right? Left Join Current on Previous, Left Join
Previous on Current, Inner Join Current on Previous, stick them
together in one table,

Could someone jump in here and tell me if Access supports full outer joins
now?
If not, this is a situation for a union query. You don't need the inner join
part. Create a saved query (call it UnionOfCurrentAndPrevious if you like)
with this SQL (create a query in Design View and swtich to SQL View):

Select c.master CurrentMaster, c.subacct CurrentSubacct,
c.Description CurrentDescription, c.custid CurrentCustid,
p.master PreviousMaster, p.subacct PreviousSubacct,
p.Description PreviousDescription, p.custid PreviousCustid,
c.custdiscount CurrentCustdiscount,
c.masterdiscount CurrentMasterdiscount,
p.custdiscount PreviousCustdiscount,
p.masterdiscount PreviousMasterdiscount,
Nz(c.custdiscount,0) - Nz(p.custdiscount,0) [custdiscount difference],
Nz(c.masterdiscount ,0) - Nz(p.masterdiscount ,0) [masterdiscount
difference]
FROM [current] c left join [previous] p
ON c.master = p.master and c.subacct = p.subacct
and c.custid = p.custid
Union
Select c.master CurrentMaster, c.subacct CurrentSubacct,
c.Description CurrentDescription, c.custid CurrentCustid,
p.master PreviousMaster, p.subacct PreviousSubacct,
p.Description PreviousDescription, p.custid PreviousCustid,
c.custdiscount CurrentCustdiscount,
c.masterdiscount CurrentMasterdiscount,
p.custdiscount PreviousCustdiscount,
p.masterdiscount PreviousMasterdiscount,
Nz(c.custdiscount,0) - Nz(p.custdiscount,0) [custdiscount difference],
Nz(c.masterdiscount ,0) - Nz(p.masterdiscount ,0) [masterdiscount
difference]
FROM [current] c right join [previous] p
ON c.master = p.master and c.subacct = p.subacct
and c.custid = p.custid




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[KC_Cheer_Coach]

What you posted in here works great. Thank you so much!

I am so sorry. Yes, the master, subacct and custid fields are key. The
description refers to the subaccount.

Thank you for replying back so quickly. I will try what you said right now.

Thanks!!

The database that I thought would take me 2 weeks has taken me much
longer. I have two files that I import from .csv pipe delimeted into
Access 2003. Table 1 is called CURRENT and Table 2 is called
PREVIOUS. They are exactly the same except that one has the current
month's account info and one has the previous month's accounts info.

The columns in each table are as follows: master, subacct,
description, custid, custdiscount, masterdiscount.

The key fields appear to be master, subacct and custid... but why are you
making us guess?
Not really necessary in this situation. but it never hurts to tell us the
datatypes of these fields.

What does "description" refer to? The master field? Or the Subaccount?

I need to create a comparison where I get ALL information from each
table and return a table with the following: master, subacct,
description, custid, previous.custdiscount, current.custdiscount,
previous.masterdiscount, current.masterdiscount, custdiscount
difference, masterdiscount difference.

This should be easy, right? Left Join Current on Previous, Left Join
Previous on Current, Inner Join Current on Previous, stick them
together in one table,

Could someone jump in here and tell me if Access supports full outer joins
now?
If not, this is a situation for a union query. You don't need the inner join
part. Create a saved query (call it UnionOfCurrentAndPrevious if you like)
with this SQL (create a query in Design View and swtich to SQL View):

Select c.master CurrentMaster, c.subacct CurrentSubacct,
c.Description CurrentDescription, c.custid CurrentCustid,
p.master PreviousMaster, p.subacct PreviousSubacct,
p.Description PreviousDescription, p.custid PreviousCustid,
c.custdiscount CurrentCustdiscount,
c.masterdiscount CurrentMasterdiscount,
p.custdiscount PreviousCustdiscount,
p.masterdiscount PreviousMasterdiscount,
Nz(c.custdiscount,0) - Nz(p.custdiscount,0) [custdiscount difference],
Nz(c.masterdiscount ,0) - Nz(p.masterdiscount ,0) [masterdiscount
difference]
FROM [current] c left join [previous] p
ON c.master = p.master and c.subacct = p.subacct
and c.custid = p.custid
Union
Select c.master CurrentMaster, c.subacct CurrentSubacct,
c.Description CurrentDescription, c.custid CurrentCustid,
p.master PreviousMaster, p.subacct PreviousSubacct,
p.Description PreviousDescription, p.custid PreviousCustid,
c.custdiscount CurrentCustdiscount,
c.masterdiscount CurrentMasterdiscount,
p.custdiscount PreviousCustdiscount,
p.masterdiscount PreviousMasterdiscount,
Nz(c.custdiscount,0) - Nz(p.custdiscount,0) [custdiscount difference],
Nz(c.masterdiscount ,0) - Nz(p.masterdiscount ,0) [masterdiscount
difference]
FROM [current] c right join [previous] p
ON c.master = p.master and c.subacct = p.subacct
and c.custid = p.custid




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"