Relationships in querries

[Sleepless In the Natti]

I have an Accounts Receivable DB. The information is fed to the DB by a
couple of text files that come from my companies internal system. I have set
these files up as linked tables. The linked table I am having problems with
right now is ARReport. I have taken a look at the table and it contains the
right information but when I write a query I either get way to much info or
not enough. Once I am able to get the right amount of info I need to change
it to an append query so that it will update the master table of tblArReport.

Tables - ARREPORT - Linked table with below fields
EIRef, Client #, Invoice #, Inv Date,
Advances, Other, Total.
tblArReport - Master table that ARREPORT Feeds into.
EIRef, Client #, Invoice #, Inv Date,
Advances, Other, Total
Paid, Total Deposits

I have setup a Select query to find only the data from ARREPORT that I need
by setting criteria on Total to "Is Not Null". The ARREPORT itself contains
some header info that I don't need to pull into the DB.

Run the query and all of the correct information is pulled. Then I change
it to an append query and tell it to append to tblArReport. Run the append
it says I am about to change 28 rows. That is fine, click YES. Then it
comes up with an error. It cannot append all the records in the query. 28
records were not updated do to key violations. Below is the SQL for this
query as an append query.

INSERT INTO tblArReport ( EIRef, [Client #], [Invoice #], [Inv Date],
Advances, Other, Total )
SELECT ARREPORT.EIRef, ARREPORT.[Client #], ARREPORT.[Invoice #],
ARREPORT.[Inv Date], ARREPORT.Advances, ARREPORT.Other, ARREPORT.Total
FROM ARREPORT
WHERE (((ARREPORT.Total) Is Not Null));

 

[Dustin B]

I figured this one out. The linked table ARREPORT was pulling the original
table instead of the spot where the updates were going.