Office Software Thread, Updating several tables in Access 2007 with one query in Technical; Here's one for you....
I have an query in Access 2007 which takes data from a form and adds it ...
27th March 2011, 05:57 PM #1
Updating several tables in Access 2007 with one query
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.
27th March 2011, 06:01 PM #2
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?
27th March 2011, 06:05 PM #3
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.
Originally Posted by SYNACK
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.
27th March 2011, 06:21 PM #4
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.
By torytechie in forum Office Software
Last Post: 27th October 2010, 03:22 PM
By imiddleton25 in forum Office Software
Last Post: 18th May 2010, 03:54 PM
By Nick_Parker in forum Windows
Last Post: 28th October 2009, 03:39 PM
By joe90bass in forum Office Software
Last Post: 7th February 2009, 12:24 PM
By dickyfinn02 in forum MIS Systems
Last Post: 23rd April 2007, 04:33 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)