By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,335 Members | 1,902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,335 IT Pros & Developers. It's quick & easy.

Append Problems

P: n/a
All,
Users of the database I have built receive an automated text file from
one of our systems at work via e-mail. A problem with the text file exists
which I have no control over. The problem is that the set and unit qty are
doubled on the last line of each ASN. An example of the text file is below:

"2005-01-27","06.00.00","000000000000000000000000082287","1 4112",85070,1163,7338,102,10,480845,"B","N",360,2, "FE686
KKTU7329032"
"2005-01-27","06.00.00","000000000000000000000000082287","1 4112",85070,1163,7338,102,11,480846,"B","N",720,4, "FE686
KKTU7329032"

The fields are date, time,
ASN,PO,Vendor,Class,Style,Color,Size,Sku,Packtype, Ratio,Units,Sets,Ref.

There maybe be several PO's within an ASN and several Sku's within a PO. The
text file arrives in order of ASN -> PO -> Sku and it is always the last sku
from an ASN which has the Sets/Units doubled.

Can anyone suggest a way in which to remove the "Doubling" during the append
into my table?

Many thanks,

Mark
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"Mark" <ma**********@ntlworld.com> wrote in message
news:sU***************@newsfe5-win.ntli.net...
All,
Users of the database I have built receive an automated text file from
one of our systems at work via e-mail. A problem with the text file exists
which I have no control over. The problem is that the set and unit qty are
doubled on the last line of each ASN. An example of the text file is below:
"2005-01-27","06.00.00","000000000000000000000000082287","1 4112",85070,1163,
7338,102,10,480845,"B","N",360,2,"FE686 KKTU7329032"
"2005-01-27","06.00.00","000000000000000000000000082287","1 4112",85070,1163,
7338,102,11,480846,"B","N",720,4,"FE686 KKTU7329032"

The fields are date, time,
ASN,PO,Vendor,Class,Style,Color,Size,Sku,Packtype, Ratio,Units,Sets,Ref.

There maybe be several PO's within an ASN and several Sku's within a PO. The text file arrives in order of ASN -> PO -> Sku and it is always the last sku from an ASN which has the Sets/Units doubled.

Can anyone suggest a way in which to remove the "Doubling" during the append into my table?


If you can determine, after the fact, which was the last record (remembering
that relational tables are, by definition, unordered so you can't rely on
"MoveLast" which moves to the record that is physically last), then you can
just go in and recalculate that field.

If you want to intercept it "on the way in", you can use File I/O to read,
maniupulate, and write your data.

I can't think of an approach to use with Access import or TransferText.

Larry Linson
Microsoft Access MVP

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.