I am an intern working at a fairly small company over the summer. They work with about 10 retail locations, several warehouses and are looking into developing a website to sell their inventory. As of now the company has a serious problem keeping up with inventory. The stores and warehouses do not have accurate inventory counts and I am thinking if you throw a web business into the mix things are going to get even more complicated.

My education is in business but I have taken quite a few advanced MS Access classes and made a MS Access database for a different retail company before (which they use but on a local basis). I have an idea of what I think would work for this situation, but because of my limited technical background and the fact that most of my experience is in MS Access, I wanted to ask someone else if they think my idea would work and what the advantages/disadvantages are for doing it this way are.

So my plan: I want to have an MySQL server that keeps track of all the inventory, customer and sales data. I then want to make different MS Access programs: one for retail, one for warehouse and one for headquarters. These MS databases would pull the tables off the MySQL and allow each place to update the data and send it back to MySQL. The overall goal is to have MySQL be able to keep a track of total inventory at all the locations. From there I am thinking the web programmer that is making the website could use the MySQL database to pull information about what is for sale to populate the website inventory.

So... first of all, would this work? I am pretty great in MS Access but have limited training in working with MySQL. From what I understand you basically just need to make the tables in MySQL and then you can build relationships and interface kind of stuff on MS Access to change the data in the tables (I think I could handle that).

One other thing. Having everything setup this way, would it become a problem if multiple locations were working on the same table in MySQL? I don't think a small lag in the currency of the data would be a problem but is there a chance inventory counts could get messed up because they are being edited at different locations and times?

I know this is long and has a lot of information. I greatly appreciate anyone taking the time to look over this and give feedback. I have enough training to think what I am looking to do may work but am afraid to bring this forward to my boss until I am certain it would work.

Thank you,
Chase

The system should work although it's a massive endeavour if you have no MySQL or web coding experience. Also the MySQL to Access stuff all seems a tad complex. You would be better storing everything in MySQL databases on a server then coding some PHP stuff to update the inventory and to pull reports from the tables.

The issues of multiple editing at once would cause issues at times with possible results of corrupt data, which is where the PHP site comes in again as you could then add a PHP coded buffer into the mix so that when the submit the data it buffers it before writing to the database to ensure no conflicts from other sites. .

Either way better getting studying on MySQL and PHP if you want to pull this off.