[ms office - 2007] Updating several tables in Access 2007 with one query

[garethEds]

Here's one for you....

 

I have an query in Access 2007 which takes data from a form and adds it to a stock table. This works fine and dandy.

 

What I'm searching for (currently Googling) is a way of sending that new data to 6 other tables (individual shops). Each of the 6 tables has a different name e.g. tblStockBristol, tblStockCardiff etc

 

Basically in this database when you purchase a new item for the warehouse, the stock list for each individual shop needs to be updated as well.

 

Got me searching for a while now so maybe someone can think quicker.

 

Gareth

[SYNACK]

Um, are you sure that is the right way to handle it, what is your usage of this data. Surely if the stock is purchased for the warehouse then it is stock in the warehouse table and the stores should refer to the warehouse stock for levels or the stock once at the warehouse should be allocated and shipped as seporate transactions to each of the stores?

[garethEds]

Um, are you sure that is the right way to handle it, what is your usage of this data. Surely if the stock is purchased for the warehouse then it is stock in the warehouse table and the stores should refer to the warehouse stock for levels or the stock once at the warehouse should be allocated and shipped as seporate transactions to each of the stores?

 

Yes I see what you are saying but when new stock is aquired for the warehouse there is an initial quantity sent to the shops. I was going to transfer the item data over to the stores table automatically (or was thinking of it). I'm just looking for a simple way to show the pupils.

 

The gaps in my knowledge astound me. I hate having to learn so many packages as IT Coordinator - but yet do not have the time to fully master any of them.

 

Gareth

[SYNACK]

Ah, if there is an initial transfer then this would make sence (from the prospective of process not business sence )

 

If you were to implement this I would use a bunch of queries tied into the onclick event that transfers in the stock on the form.

 

Use the button wizard to make a new button run a query that transfers the stock from the entry form into the main table then go and view the code behind that runs that query. Now you just need to create a new query for each table to add stock and copy the run query bit for each of them.

 

It would be better to have a single paramaterized query that handled the stock movement for all of the tables but also a bit more complex. Ideally you want to view it as a transaction with a withdrawl and deposit type model.

 

If your database is highly connected then you could actually even do it in one big transaction table. Stock type, number and location stored as a bunch of transactions. Then you add 500 units of salted sea bass to the warehouse, subtract 50 units of the same product then add 50 units of that product to the cardif location. You can then get your current 'balance' of stock either in total or at each location by running a sum query on the stock type with a where clause that specifies the location you are interested in.