Help with Access DB and ASP – relationships?

Question by axo: Help with Access DB and ASP – relationships?
Hi

Im building a web application to control the stock of a small company.

I already made the database with all the items and descriptions in one table.

This would be the main warehouse. so now i have to create 3 different departments where i have to move stuff from the main warehouse.
So if i have 10 boxes in main, and i move 4 to department1. then i would get 6 boxes in main and 4 in dep1.

So dep1,2,3 would be in their own tables in the database.
My web app would be constantly updating the stuff being sold in departments 1,2,3…. so here is my question.

I dont know how to use access… then how do i make the database to update the quantities by itself in the main warehouse table if department X sells anything…?

Like in Excel, when you work with different sheets that can update the other ones if you change a number.

Otherwise i would have to write a lot of extra SQL code in the ASP scripts and im really out of shape cause i don’t program in ASP sisnce long time ago. Thanks!

Best answer:

Answer by BlueFeather
Since this is a web-based application, the database needs to reside on the server AND you need to be familiar enough with a server-side scripting language to be able to write and implement the queries necessary to perform the tasks you require.

My experience has been primarily with ASP and MS Access so I could probably help with some of the details if you go that route.

Given the information you have provided, here is a description (as simple as I can make it) of what needs to be done:

When a sale occurs, it needs to trigger a script that will append a record FROM the main table TO one of the department tables, then (and only then) it would delete that record in the main table.

Now, having said that, I hasten to add that it would be far better to simply update counts in the relevant tables, rather than appending and deleting records.

The script that builds and runs the necessary queries would probably be triggered by a button (on one of your pages) that says “Buy Now” or something similar.

Since you are not familiar with Access and probably also not familiar with scripting languages, you need to be aware that there is a LOT of work involved in what you want to do. I suggest that you consult an experienced professional in your area, someone who could provide hands-on help. Try contacting some User Groups, they may have members who would be willing to help – or could direct you to someone else who would.
§

Add your own answer in the comments!