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

    GeraldM
    Expand Collapse
    Guest

    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 >
    ToolLimit).

    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:
    geraldm@westnet.com.au
     
  2. Loading...


Share This Page