Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Update Multiple Tables - Problem

Discussion in 'Information Technology' started by GeraldM, Nov 12, 2005.

  1. GeraldM

    Expand Collapse

    G'day Everyone:

    I am developing an access 2003 db which tracks tools issued from a tool
    store to employees. I have run into a problem tring to work out how to update
    data in more than one table as tools are issued and returned.

    Tables - fields:
    tblTool - ToolId, ToolDescription, ToolQty, AvailableQty
    tblEmployee - EmpId, EmpName, ToolLimit, ToolsOnLoan
    tblIssueReg - RegId, fToolId, fEmpId, IssuDate, RetDate, IssuQty

    Each tool has a unique ID but there may have more than one of a particular
    tool, eg 10 x 5mm drill, 5 x hacksaws etc.

    Each employee has a unique employee ID and a preset limit to the number of
    tools he may have at any one time (ToolLimit).

    The issue register records issues and returns.

    When a tool is issued to an employee:
    1. a record is added to tblIssueReg
    2. tblTool.QtyAvailable needs to reduce buy the qty issued (but not < zero).
    3. tblEmployee.ToolsOnLoan needs to increase by the qty issued (but not >

    When a tool is returned by an employee:
    1. the appropriate record in tblIssueReg is ammended
    2. tblTool.QtyAvailable needs to increas buy the qty returned.
    3. tblEmployee.ToolsOnLoan needs to reduce by the qty returned.

    all this will be controlled using a ToolIssue form.

    So far so good - now the hard bit (for me anyway).

    I cannot work out a simple way to update the quantity issued or returned in
    both tblTool and tblEmployee. i have considered writing vba script to do this
    but this appears to be overkill to me and may in fact not be the most
    efficient. i suspect i can use an update query but cannot work out how to
    enter the values into one via the ToolIssue form or to update both tables

    -- Thanks in advance
    PS if anyone has any suggestions you can e-mail me direct at:
  2. Loading...

    Similar Threads Forum Date
    Self Update On The Gurdwara Blogs Apr 3, 2017
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Update Blogs Oct 16, 2015
    Update Wikipedia Enteries On Sikh Socio-Political Topics Breaking News Jul 19, 2015
    Pacific Oil Slicks Spotted In Search For Missing Malaysia Airlines Plane (Live Updates) Breaking News Mar 8, 2014

Since you're here... we have a small favor to ask...     Become a Supporter      ::     Make a Contribution     

Share This Page