Append query has too many records.

  • Thread starter Thread starter Bill Raterink
  • Start date Start date
B

Bill Raterink

I have a RawData table that I import a txt file into, then
I "Append" the records to a second table "calculating"
some fields in the process. I do this, because some of
the calc'ed fields are used in further formulas in the
reports generated. Once, works great. I have 66 records,
& my report is fine. When I import the NEXT txt file
with, say, 85 records, instead of getting 151 records,
it's in the thousands. The 3rd append query in 400k
records. I suspect the problem is in the join, but I don't
know how to fix it, & I can't import all of the files
together. I'm currently using the 2nd option on the
join, "all" of RawData + "Matching" on the AppendData.
What I really want is "All" of BOTH tables. All of
RAWDATA (which is cleaned out at each import) added to the
end of the ApndData table. The tables are not keyed, But
I (think) I HAD to join on something, so I chose the DATE
stamp, lots of dupe's. Thanks, -Bill.
 
The tables are not keyed, But
I (think) I HAD to join on something
Click to expand...

That was your mistake.

An Append query does NOT need to join the source table to the target
table. Just base the Append on your import file, and append to the
target table; don't join them.
 
John, I tried deleting the join, but now I get 0 rows
appended. It seems the field names being the same may be
causing a problem. I've tried to qualify them with the
table name, (RawApndPlus.Amount1, or RawApndPlus!Amount,
or RawAppendPlus[Amount]))but it doesn't like that
either. Currently my query has field names in FIELD,
RawData in TABLE, nothing in SORT, the same field name in
Append to, & nothing in CRITERIA. Do I have this right,
or should I use different Field Names. Thanks again for
the assisance. -Bill.
 
John, Thanks for the help. I deleted the join & then I
got 0 rows appended. I removed the table from the query &
then deleted the "Relationship" in the database...NOW it
seems to work fine. I'm sending it over to my tester...
she can break ANYthing. ; ) Thanks again, -Bill.
 
John, Thanks for the help. I deleted the join & then I
got 0 rows appended. I removed the table from the query &
then deleted the "Relationship" in the database...NOW it
seems to work fine. I'm sending it over to my tester...
she can break ANYthing. ; ) Thanks again, -Bill.
Click to expand...

as asked in the other thread - please post the SQL of this query.
Deleting the relationship in order to get an append to work is like
putting pennies behind all your fuses because they keep blowing! It's
not necessary and it can be really dangerous to the integrity of your
data!
 
Back
Top