Data Import/Export

[revvedmoto]

This database Im trying to create is to help manage my small online business. Ive actually successfully created several excel spread sheets that does exactly what I need it to do but Im thinking access would be the ultimate way to go and simplify my life if done right. My down fall is I have worked with access very little so Im studying up as I go. At this point Ive watch several youtube and lynda videos but Im concerned that these videos only scratch the surface. Im hoping that with a few helpful pushes in the right direction that maybe I wont make too many mistakes along the way.

The mission:
-calculate Inventory levels and lead times From different distributors.
The file thats provided from my distributor supply this info:
Part#
Description
Cost
MSRP
Inventory of warehouse1
Inventory of warehouse2
Inventory of warehouse3
Inventory of warehouse4
weight



This file would have to be updated daily to keep my inventory straight so Im not selling items that sold out the day before or I now have to get from a warehouse further away.
I guess Im wondering how to handle this. In excel I did a If then statement something along this:

If warehouse 1 has over 10 instock then Stock level =10 Lead time =1day

If warehouse 1 has less then move to warehouse 2 ex...

Any suggestions on this?

[ItsMe]

...This file would have to be updated daily to keep my inventory straight...

The best scenario would be to use the DB to process transactions in real time. Then, you would not be doing double data entry.

I would consider how inventory is received and how inventory ships. You will want to have your DB reflect a record for each unit. You will want to understand if a unit can be broken down. If a unit is broken down, then you need to decide how you will keep track of the pieces. Maybe have a record for each piece in a child table. Maybe have the quantity of pieces in the unit record and do calculations from there. I would not delete a record when an item ships out. I would change the status of the record and or associate it to an outbound BOL.

Usually shipping out and or receiving will have something to do with a client. So there will probably be a need for purchase orders. So you will need some tables to keep track of this stuff. Maybe there will be a PO as well as a BOL or Packing List, etc.

This stuff goes into their respective tables. YOu will need to understand how Key values work to create joins/relationships between tables.

Study up on RDBMS, Primary Key, Foreign Key, Relational Data base, Relationships, Joins. Get your tables planned then worry abour the user interface. YOu will need tables with some sample data in them to get the ball rolling. Expect to revisit your data structure a few times as you build an app to, first, ENTER data.

[revvedmoto]

I guess maybe I should of explain a bit better. The purpose of this database is to update my website with inventory levels and qtys. This wont be linked to my Point of sale system any way.

[ItsMe]

Have your POs export into a suitable format and then import to remote DB.

[revvedmoto]

I dont need to this inventory is all drop shipped. All Im trying to do is keep track of my VENDORS inventory so i know whats available at any given time. I would then create a report to update my website with this info so Im not selling something I can no longer get.

[June7]

What is the data you receive? Is it transactions or summations? Do you need to add records or purge and replace?

[ItsMe]

Access can import files into its tables. You can automate this. You mentioned that you are using excel to track the available inventory. So your website uses Excel as a data base? Is it saved as XML?

Access is used as a data management tool. It may not be feasible to use Access if all you are going to do is import data from a third party and export it to Excel. Just curious what process you currently use to update your website.

[revvedmoto]

I receive a csv file. which gives me Part Numbers, Prices, and the inventory that they have in each one of their warehouses. Does that answer the question.

I just purged the Inventory table and re-imported the updated file.

[June7]

Maybe code behind a button Click event:

CurrentDb.Execute "DELETE FROM Inventory"

Then whatever code you want to use to import.

Or just set links to the CSV file(s) and replace daily with the same name.

[revvedmoto]

It probably doesnt help at all but its an amazon store. I can upload txt files to add, remove, or alter my listings.
Heres a quick run down of my current process
File1: Vendor Part Info which includes part numbers, inventory and prices
File 2: Is used to link Amazon listings to Part number from File 1. It currently calculates the time needed to receive the item and how many are available along with the potential profit.
File 3: Is an add file that gets uploaded to amazon to create listings. Its created from File 2 and extra info that Amazon requires (I would like this to be a report I just export from access)
File 4: Is a file that gets uploaded to amazon to alter inventory Ive already created such as price, shipping times and qtys.

[ItsMe]

You could import the files from the various vendors to Access and have Access organize the data before spitting out a single text file. I guess that could save you some time.

[revvedmoto]

Being new to access I dont know what that could possibly do to confuse the situation. I know Ill run into at least a few Part number that will conflict with the other vendors part numbers. How should I proceed with this. Should I create a whole new database for each additional Vendor.

[June7]

Should not have to.

Don't use the part number as primary key. Are there dependent child tables? If not, should not be an issue. However, each record needs a vendor ID. You don't show such field in the OP.

[revvedmoto]

Whats the best way to share a db, I tried the attachment but it doesnt seem to work.
Again sorry im not too familiar with access but my concern with creating another field In my Vendor table was wouldnt I have to then alter that table every time I went to update it?

[revvedmoto]

https://drive.google.com/file/d/0B-U...it?usp=sharing