Results 1 to 7 of 7
  1. #1
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13

    Inventory D/B (Check-IN / Check-out)

    Hi need help with this Query.............If there is a better & easier way to do this please SHOW me :-)



    I am trying to do a simple Check-in / Check out form for inventory control.

    The Check-in form is to receive RM.....Starting point
    The Check-out form is used to deduct from what has been received as we consume to keep my inventory levels accurate


    I HIGHLY APPRECIATE YOUR HELP IN ADVANCE
    Attached Thumbnails Attached Thumbnails End result.JPG  
    Attached Files Attached Files

  2. #2
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13
    The idea is at "Check-out" you pick from avail drop downs & enter how much you have used....... This keeps happening until Qty gets to Zero

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you need 2 tables,
    1 the tInventory table with the items to checkout, and each has OWNER field.
    if the OWNER field is null, it is available, otherwise it has the owner ID in it.

    1 table for tLogs, to record WHEN ,WHAT was checked out:

    name, id, action, equip, time
    bob smith, 992, checked out, wrench, 1/1/2017 5:00

    a form would show a list of equiupment,
    and a combo for renters,

    pick equip,
    pick renter
    select combo Action (rent or return)
    click GO button

    the button runs an update query to remove the id if returning,
    add the id if renting

    and runs append query to add this transaction to the tLog

  4. #4
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13
    Appreciate the response, Can you take my D/B & show me how to do the update?

    Thanks

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This would create a query according to your picture:
    SELECT RecordIN.BatchDate, RecordOUT.Slip, Sum(RecordIN.QtyIn) AS SumOfQtyIn, Sum(RecordOUT.QtyOut) AS SumOfQtyOut, Sum([QtyIn]-[QtyOut]) AS Net
    FROM RecordIN INNER JOIN RecordOUT ON RecordIN.FRex = RecordOUT.FRex2
    GROUP BY RecordIN.BatchDate, RecordOUT.Slip;

  6. #6
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13
    Quote Originally Posted by aytee111 View Post
    This would create a query according to your picture:
    SELECT RecordIN.BatchDate, RecordOUT.Slip, Sum(RecordIN.QtyIn) AS SumOfQtyIn, Sum(RecordOUT.QtyOut) AS SumOfQtyOut, Sum([QtyIn]-[QtyOut]) AS Net
    FROM RecordIN INNER JOIN RecordOUT ON RecordIN.FRex = RecordOUT.FRex2
    GROUP BY RecordIN.BatchDate, RecordOUT.Slip;
    Ok can you attach the query?

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Copy and paste the SQL into a new query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 41
    Last Post: 12-06-2016, 08:40 AM
  2. Replies: 3
    Last Post: 08-25-2014, 02:07 PM
  3. Replies: 4
    Last Post: 02-03-2014, 12:16 PM
  4. Replies: 1
    Last Post: 07-22-2013, 09:31 AM
  5. Replies: 1
    Last Post: 05-10-2012, 11:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums