+ Post New Thread
Results 1 to 4 of 4
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 ...
  1. #1

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,260
    Thank Post
    965
    Thanked 324 Times in 192 Posts
    Blog Entries
    11
    Rep Power
    164

    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.

    Gareth

  2. #2

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,174
    Thank Post
    868
    Thanked 2,702 Times in 2,289 Posts
    Blog Entries
    11
    Rep Power
    773
    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?

  3. #3

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,260
    Thank Post
    965
    Thanked 324 Times in 192 Posts
    Blog Entries
    11
    Rep Power
    164
    Quote Originally Posted by SYNACK View Post
    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

  4. #4

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,174
    Thank Post
    868
    Thanked 2,702 Times in 2,289 Posts
    Blog Entries
    11
    Rep Power
    773
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2010] tables missing in Access 2010 databases
    By torytechie in forum Office Software
    Replies: 1
    Last Post: 27th October 2010, 03:22 PM
  2. access 2007 header in tables
    By imiddleton25 in forum Office Software
    Replies: 0
    Last Post: 18th May 2010, 03:54 PM
  3. Updating Exchange 2007 Address Lists
    By Nick_Parker in forum Windows
    Replies: 1
    Last Post: 28th October 2009, 03:39 PM
  4. [MS Office - 2007] forcing Save as 97-2003 in Word 2007 corrupts tables
    By joe90bass in forum Office Software
    Replies: 11
    Last Post: 7th February 2009, 12:24 PM
  5. Refreshing MS Access linked tables to CMIS MS SQL
    By dickyfinn02 in forum MIS Systems
    Replies: 0
    Last Post: 23rd April 2007, 04:33 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •