Import data, add column of data, refresh data, keep changes

[deerhntr22]

Hi all, I am a newbie with Access...but have been able to do a few things that have worked, but more that have not :banghead:...anyways, here is what I am trying to do. I have a table called "New" that imports data from a text file created by another program...I added a column to the end of the imported data so I can make "notes" about the particular line item...problem is...when I refresh the data in the "New" table, the "added" column of notes does not stay with the right line of imported data...how do I achieve this? Please help.

 

[Minty]

You would need to add a unique identifier to each record, and then make sure when you refresh the data you update the same line.
This would rely on the imported data having its own unique identifying field.

 

[jdraw]

deerhntr22,

Can you provide a little more detail? What are you really dealing with? Can you provide a few samples records to help with context?

A table called "New", really? Use meaningful names, it will help you and readers.

Good luck with your project.

 

[deerhntr22]

jdraw...I use the names New and Old because I am the only one messing with this database, and I do know what they are for...(plus I plan to rename eventually).

So basically I have a text file (delimited comma)...that is generated by my internal ERP program (it is generated every 5 mins, it is a text file of our open orders that are shipping for the day). I want this text file uploaded/refreshed into the "New" table...I then add a "column" to it on the end...and want to be able to make "notes" about each line of data....

I then want it to save off into another table called "Old"...(I use this table to create an HTML file and excel file that the end users can "VIEW ONLY")

Everything is working, except when the "New" table's text file gets renewed/changed (if order ships it deletes from the text file generated by the ERP program, if an order is added, it is added to the text file) the "added" columns data doesn't follow the "Save" to the "Old" table...

Hope that helps...

 

[jdraw]

Please tell us the purpose of the text file in detail.

It seems you are not really importing a file, but using the ERP data to update your "imported data". If you are modifying the "imported data" from 1 five minute file to the next, then it seems to me you are not dealing with importing specifically.

It appears that you have data on 2 separate systems, and you are trying to maintain each for "some purpose(s)".

You can tell us more,and/or you can show us some sample data and related changes from one "import" to the next.

Good luck.

 

[arnelgp]

use a criteria when saving your new table to old table.
or better use right outer join to update/insert records to old table:
use a query to delete first the records not in new table:

delete [old].* from [old]
left join [new]
on [old].pk1 = [new].pk1
where [new].pk1 is null;

then perform update/insert record:

update [old]
right join [new]
on [old].pk1 = [new].pk1 ...
set [old].field1 = [new].field1, ....

 

[deerhntr22]

jdraw...you are correct...my ERP system generates a .txt file every 5 mins...it is our "open orders"...I take that text file and upload it in to a access table called "New"...I then want to "copy" the data from "New" to a table in Access called "Old"...I add a column to the "Old" table at the end to make comments about each line (comments are whether or not the order is shipping, and if not, why and when)...I then save off the "Old" table to an HTML file so "users" can "view only" the data.

My hurdle is...when I upload the ERP .txt file into the "New" table (I erase the entire "New" table and reload it with the txt file again)(Orders that have been shipped have been deleted from the new txt file...new orders will show on the txt file)...then I want to add the new sales order lines to the "Old" table..and delete the "deleted/shipped" lines from the "Old" table...and rewrite the same data to the "Old" table while keeping the comments I added when rewriting the line (the reason I want to rewrite the line is because certain information on the "shipping" line may have changed, ex: order qty, date due, etc.)

 

[deerhntr22]

Maybe there is a better way to accomplish what I am trying to do...PLEASE any suggestions would be great.

 

[jdraw]

I'm having hard time understanding why the ERP system would not be the proper place to be entering this change in Order etc data.???
It seems you need to keep some identifying info from the ERP for each record --especially if you are adding comments and/or changing some facts.

I can assure you that trying to synchronize records on different systems is extremely difficult. You need more details of the logic involved to meet your overall requirement.
The details will guide you to what is possible in your situation. I don't think your current issue is specific to programming; I think it is to do more analysis.

Good luck.

 

[deerhntr22]

Unfortunately I have no access to the way the ERP text file is generated...so I can't modify it...I only have the ability to read it....

So I read it into a database table "New"...but the text file data is changing continuously...lines are being added/deleted each time the text file is created.

What I need is a way to copy that data from the ERP text file...and append a column to the end of it...(so I can add notes per line) and save it (called "Old")...then when the new text file is created...I need to check the "Old" data with the comments to see if there is any lines there that are not on the "New" data...if not, delete, then I want to add any records that are on the "New" that are not on the "Old"...then I can make changes to those "newly added" lines...and repeat the process.

I have two fields in each line that I use for "unique identifiers"...SO# (sales order number) and LINE (sales line number on sales order).

 

[jdraw]

???
What exactly do you do with the updated text file once you have added comments?
How does that info get back into the ERP system?

You could create a table to contain the text file.
Import the data into that table.
Run a query to alter the table and add a field Comment.
Now populate the Comment field

Then what???
What is the whole process start to finish?

I understand you get a specific format from the ERP. But the key points are what is the requirement (in detail) to get your "value added info" back into the business process?

As I have tried to say earlier, trying to maintain synchronicity between data sets on different platforms that are independently modified is somewhere between difficult and futile. More details of the whole process needed.

Good luck

 

[deerhntr22]

You could create a table to contain the text file. I do that.
Import the data into that table. I do that.
Run a query to alter the table and add a field Comment. I think I do this...explain further
Now populate the Comment field How, with joins? I really am struggling.

Once I have the "comments" added to line items...I then save off the whole table with comments as an HTML file...the customer service individuals have a shortcut on their desktop that points to the HTML file...that way when a customer calls in inquiring about whether Order # 123 is shipping out today...customer service has a way of knowing that information (because the comments added field) without having to call the shipping department and ask.

Our ERP program is old COBOL code that needs revamping...and until that happens I have to find work arounds to be able to display the data I need.

 

[jdraw]

Are you part of CustomerService or Shipping?
How does the ERP ytem get updated to show/extract changing data every 5 minutes?

Does your data consist of

Sales OrderNumber, LineItem and Comment?

How exactly do you provide the HTML file to the others?

Also, it seems like Shipping is the group that has latest info on Shipments???

 

[deerhntr22]

Sorry...meant to add the whole process as well....

1. Upload .txt file into table called "New" (20 columns in all)

2. Copy data from "New" table into table called "Old" and add column called "Notes"

3. Save "Old" table off as HTML for other users to view.

At this point...the .txt file that was originally uploaded into the "New" table has been changed by our ERP program...so I upload that data into the "New" table again...(I just delete all records and re add the new text file data)...then I go start comparing

I look from the "New" table to the "Old" table and see if there is a line in the "Old" table that has the same SO# and LINE columns...if so, I rewrite the line data column by column...and keep the "comment" column...If not, I add the line...

I then look from the "Old" table to the "New" table and see if there is anything listed in the "Old" table that isn't in the "New" table...if so, delete the line...

*****that is the process*****

However, I don't believe I am doing it right, or the best way, heck, I managed to confuse myself explaining it....:banghead:

 

[deerhntr22]

Are you part of CustomerService or Shipping?
How does the ERP ytem get updated to show/extract changing data every 5 minutes? The ERP system queries our database every 5 mins and then creates a delimited text file with that data...(headers being first line)

Does your data consist of

Sales OrderNumber, LineItem and Comment? There are 20 columns of data from the ERP program text file...1 column added to the table once it is uploaded into Access database "called it Notes"

How exactly do you provide the HTML file to the others? I have a VB code that turns the Access database (the one with commetns added) into an HTML file

Also, it seems like Shipping is the group that has latest info on Shipments??? The shipping department supervisor is the one who fills in the "comments" once this is working....!!!

 

[jdraw]

Do you have a LAN? Do you have other Access applications?
How do people access the HTML file (where do you store it)?
Could those who need it, have readOnly access to your Access database?

Why do you think there is a better way? I'm not saying there isn't, I'd just like to hear your version of issues/problems etc.
You say about 20 fields--what sort of things?
How critical is this and your ERP to the company?

 

[deerhntr22]

Yes, it is on a LAN...we use Access as a database for different programs (if that is what you mean).

A shortcut points to the html file...It is stored on a Public Drive...

They could have read-only access, but they would get confused vs an html page...(I have no issue creating the html page...I know how to do that)

As far as a better way...I just think that there is an easier way for me to keep my comments with the pertinent line of data...I just don't know how other than comparing to delete, then re-compairing to add lines...


Here are the headers of the text file....

LATE,TY,HL,DATE ENTERED ,DATE DUE ,CF ,CUSTOMER ,LOC,SO# ,LINE,WO# ,PART# ,DESCRIPTION ,UM,ORD QTY, POUNDS,WO CMP/ALLOC,BACKLOG,SALESMAN ,CUS PO


This is pretty important information to have...

 

[jdraw]

A little detail - how does this process work at the moment? What is automated and what is manual?
Where does the supervisor make the changes? How?
Is there any advantage to keeping an Access table/database and adding/modifying/deleting records based on what comes from the ERP?
Does the ERP system get any updates(feedback) from your Access data or via the HTML?
When you replace your local table, what happens to the comments? That is, what is the life cycle of the comments?

 

[Minty]

Why not store your comments (and anything else) in a separate table and simply link them based on the Order Number and order line.

You then wouldn't need to worry about trying to keep the Old File updated. Simply store and edit/update anything in your comments table. The links in your joining query would maintain the status quo.

 

[deerhntr22]

I like that idea Minty...again, I am a newbie with this side of the fence...I am a network guy ...but I am learning. I think I got it...just put SO#, LINE, and COMMENTS in the "Old" table...and then link the "New" table with the "Old" and display it...???