Is this possible?

[bcurrey]

Our purchasing dept has asked me to build this, and I'm not sure it's possible.



We have a vendor that sends us a CSV file on the 1st of each month. It contains all the parts they sell along with every detail imaginable. There are about 30,000 lines on this file.

Each day, we get a daily report that shows any changes or product adds that have occurred to any of the items that were on the original monthly file.

My purchasing dept wants me to have the Monthly file update automatically based on the daily reports that are received. If a price changes, they want it to update that price, if a new product is offered, they want that product added.

Any ideas if this is possible and if so, how would you go about doing it?

Thanks!

[dreday]

Certainly that can be done. Tell me more about the daily reports you receive. Are they also CSV?

Basic premise for this would be to import the CSV into access and then create and bind a form to the table so that you can update the fields and add new records. If the daily reports you get are also in CSV, you can build a query that merges the new info with the old stuff. Hope this helps

[Rhino373]

Assuming the daily file has all the information for new products as the monthly does, this is really done just through 2 tables and a bunch of queries. So you load the monthly into one table and the daily into another. You can then run update queries to update what has changed. You'd run a non-matching query to find the new items and then an appead query to add them to the monthly table. Depending on what needs to be updated you may need quite a few queries but its easy work. You might add a couple of columns to the monthly file. For instances a "Date Added" column. It would be updated with the date if an append query was run to let you know it wasn't originally part of the monthly file. Same kind of idea for updates. You would want to tell yourself the date it was updated and maybe the file it came from.

There wouldn't be any need for a form since the daily file already comes with all the changes you are just comparing them to the monthly file and asking Access to make the updates and additions.

[dreday]

Assuming the daily file has all the information for new products as the monthly does, this is really done just through 2 tables and a bunch of queries. So you load the monthly into one table and the daily into another. You can then run update queries to update what has changed. You'd run a non-matching query to find the new items and then an appead query to add them to the monthly table. Depending on what needs to be updated you may need quite a few queries but its easy work. You might add a couple of columns to the monthly file. For instances a "Date Added" column. It would be updated with the date if an append query was run to let you know it wasn't originally part of the monthly file. Same kind of idea for updates. You would want to tell yourself the date it was updated and maybe the file it came from.

There wouldn't be any need for a form since the daily file already comes with all the changes you are just comparing them to the monthly file and asking Access to make the updates and additions.

Yep, this is a much cleaner solution