Join - Update - Append - Tables

[Guest]

I was wondering if one of you brilliant code-guys would take a look at this
little statement and let me know how to add a second join (i guess?) so that
it works.

table looks like this...
account name (J's) - item number (124547) - item description (dark roast
coffee)
account name (J's) - item number (321654) - item description (french vanilla)
account name (P's) - item number (987654) - item description (hazelnut creme)

When I run this code, I only get the first item in the list to update
propperlly. All of the other items in each customer profile is replaced by
the first item. The correct amount of items is displayed, just not the right
items. I'm sure it has to do with the join of the customer number (A5AN8 -
Cust Num Field.)

How do you add a second join after acct number so that I pull all of the
correct items? (Access 2003) - hope this all makes sense. (As always - I
appreciate your help!)

UPDATE AccountInformation RIGHT JOIN DLFILES_CLVREPORT1 ON
AccountInformation.A5AN8=DLFILES_CLVREPORT1.A5AN8 SET
AccountInformation.A5AN8 = DLFILES_CLVREPORT1.A5AN8,
AccountInformation.A5AN809 = DLFILES_CLVREPORT1.A5AN809,
AccountInformation.A5DFIJ = DLFILES_CLVREPORT1.A5DFIJ,
AccountInformation.OTLITM = DLFILES_CLVREPORT1.OTLITM,
AccountInformation.IMDSC1 = DLFILES_CLVREPORT1.IMDSC1,
AccountInformation.IMDSC2 = DLFILES_CLVREPORT1.IMDSC2,
AccountInformation.ABAC04 = DLFILES_CLVREPORT1.ABAC04,
AccountInformation.IMSRP2 = DLFILES_CLVREPORT1.IMSRP2,
AccountInformation.ABAC02 = DLFILES_CLVREPORT1.ABAC02;

 

[George Nicholson]

UPDATE AccountInformation RIGHT JOIN DLFILES_CLVREPORT1 ON

AccountInformation.A5AN8=DLFILES_CLVREPORT1.A5AN8

Try replacing the above section with:
UPDATE AccountInformation INNER JOIN DLFILES_CLVREPORT1 ON
AccountInformation.A5AN8 = DLFILES_CLVREPORT1.A5AN8

INNERJOIN will only update the records in AccountInformation that have
matchin records in DLFILES_CLVREPORT1, and only with the information in the
matching record.

RIGHTJOIN is updating ALL records in AccountInformation, regardless of
whether there's a match in the 2nd table. Not sure what values it is using
for that update with when there's not a match (the results you report don't
sound quite right either). But RIGHTJOIN isn't what you want, I'm sure.

Is there only one record in DLFILES_CLVREPORT1 for any given A5AN8 value? If
not, it raises a question of which one of the multiple matching records is
being used to update AccountInformation.

HTH,

 

[Guest]

I knew I wasn't clear enough - sry and seriously, thx for the time you've
already invested.

DLFILES_CLVREPORT1 is file that's sitting on a network drive. It's created
when I run a J.D. Edwards report (it's a simple text file.) Then, using an
ODBC driver, I've connected my database to it. First, I plan to run a Make
Table Query and then an Update / Append Query to update my existing Account
list / Account Item table with the new informaiton. New accounts and new
items are added daily so I need to run this update daily.

I have this working correctly with another database - the problem here is,
each account is listed as many times as there are items in that account
product profile. So if an account is buying dark roast coffee, decaf coffee,
and french van. the account name and number are listed 3 times.

So - when I run the SQL statement it blows away every item after the first
time - replacing them with the first item in the list.

For now, the I created a Make Table Query that completly deletes the
existing table and replaces it will all new information rather than doing an
update / append. Becuase it's a simple db - that will work for now. Once I
start linking additional tables to the main table - I don't believe I would
be able to keep the link if I blew away the main table every day...

Blah Blah Blah - Sorry Dude