Results 1 to 9 of 9
  1. #1
    SCBarb is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    4

    Finding table records containing two fields that are values entered on form

    I am creating a database where multiple machine operators record each run. The record includes the initial order quantity, the amount of parts produced (good and bad), the number of parts previously ran, and the remaining quantity of parts for the order to be complete. The machine operator makes an entry for every bed they have completed. I would like a box on the form that details how many parts have been previously ran by searching the table and returning only the sum off all good parts ran where the Work Order Number and File Number match what has been currently been entered on the form. It needs to be dynamic so that the number is updated each time a new bed has been completed, and produces a pop up stating that the order is complete. Any help would be greatly appreciated.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Very broad question.
    How is your data stored , what have you got so far, and what doesn't work?
    I have a suspicion from your description that the way your data may be stored is possibly making this difficult, but without more details my stabby stick is in the dark
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you step back and describe the business and put your terms into context?

    ...every bed they have completed...?

    Perhaps you could post a copy of the database or show us your tables and relationships.
    Welcome and good luck with your project.

  4. #4
    SCBarb is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    4
    I've tried several times to attach a copy of the database. Each a popup states that upload failed. Ours is a manufacturing company with several CNC machines. The prototype for this database is to be tested on the waterjet machines. Currently we use an excel spreadsheet. For each job, the user completes a form which includes the operator's name, customer, work order number, file number, quantity of the order, program name, how many (if any) parts were previously ran, how many parts are produced by each machine cycle (bed), and how long it took to produce each bed. We are looking to use access instead for many reasons. I've created a table containing all of the relevant fields. I've created a form to enter that information. With each machine cycle (bed being ran), a button is pressed that stores all of the information into the table and clears the fields that record the time start/time stop, and quantity of parts being produced. When the operator moves on to another job, a different button a pressed that clears all fields. One of the boxes on the form lists the number of parts previously ran under that fille number and work order number. In order to populate that box, I want to find all records from previous runs that match both the work order and file number, and then adds the sums of how many good parts were ran to subtract that number from the quantity of the initial order. I can't seem to make a query of sql statement that will return the number of good parts ran based upon the file number and the work order numbers that are entered on the form and sum the quantities from each of the previous records in the table.

  5. #5
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    As stated, please take a step back. You description of the process is helpful, but we need to see what you've got so far as well.

    Show us your tables. And I mean tables, if you only have one you are probably already need to re-design.
    Try compacting / repairing and then zipping your database, it can only be a certain size.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    SCBarb is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    4
    Thank you. Zip was the answer to attaching it.
    Attached Files Attached Files

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay. That helps. I can see quite a lot of things wrong, but fixing them isn't a five minute job.

    I think you need to normalise your data a lot. At the moment your main Job table is storing, or trying to store to many inter-related things in too broad a fashion.
    You have thought and set this up way too much "spreadsheet" and not enough database.

    Think of each job like an order header.
    You store the fixed requirements in the header. HeaderID, Part No, Qty Req, ExpectedDate , CustomerID

    Now think of each machine run as a partial fulfilment of that order line.
    But instead of lots of fields describing and re- recording the same information treat it like a stock system.

    Something Like

    tblMachRuns

    RunIDPK
    HeaderID
    MachineID
    OperatorID
    OperationTypeID
    StartTime
    EndTime
    Qty

    If the operation type is GoodParts you only need to add up the good parts operations from this table for that header id to get your current production number for that order.
    A simple sum and then a subtraction from the ordered qty gives you the balance.

    A separate line would record the BadParts if there were any. If there aren't no entry required.

    I may have over normalised / simplified this for demonstration of principle but does this steer you in the right direction?

    Edit - Also Calculated fields are frowned on - see here for why http://allenbrowne.com/casu-14.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    SCBarb is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    4
    That's a fair amount to chew on. I appreciate your help. We really need all of the information I have in those tables, but I also appreciate your point about the overall design and perhaps there are easier ways to query the information I need if it is stored differently. For knowledge's sake, however, I would still like to know how to accomplish my original question. In our situation, the file number for a job never changes. However, each time the job is ordered, it is given a new work order number. Also, each file may have several different parts, any combination of which may be ordered on a single work order. We have pre-packaged software that tracks jobs throughout the plant. The main purpose of this particular spreadsheet is to track and compare the performance of individual machines and workers.

  9. #9
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In that case simplify what you are recording, as the actual job details are pretty immaterial.
    The only issue with doing that is you then are stuck with having to try and record and pull forwards previous values.
    if the job header existed then those values are easily referenced and usable in your running total calculations.

    You still need to store your data correctly to make things easy to add up and mange. This point is the most important.
    So think vertically, not horizontally.

    Access can't tell you what it doesn't know, equally storing the same values over and over is not good design, and becomes tiresome for the user having to re-enter it.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 1
    Last Post: 05-22-2018, 07:47 AM
  2. Replies: 9
    Last Post: 06-20-2014, 12:27 PM
  3. Replies: 4
    Last Post: 06-18-2013, 07:36 AM
  4. Replies: 1
    Last Post: 01-30-2013, 03:27 PM
  5. Replies: 2
    Last Post: 08-15-2012, 02:21 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