Results 1 to 8 of 8
  1. #1
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Method to Detect Small Variances in Records

    I have a table listing various parts by part number along with the length, weight and description of the piece. In my list, the same part number can be recorded several times.



    I run into trouble when the extra details, like the weight or length, is slightly different for the same part number. Often its a typo or rounding error. It should always be the same.

    When I try and pull up information on he specific part number, each version of the part comes up which creates a problem.

    Is there a method to review the information when entering it into the table and let me know if there is a difference in this information.

    Thanks,

    Greg

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you show an example of your table (list)?

    Why are you repeating the same part number / weight / length?

  3. #3
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Quote Originally Posted by kd2017 View Post
    Can you show an example of your table (list)?

    Why are you repeating the same part number / weight / length?
    I tried to attach a PDF of a query of the list. The pieces are actually entered per specific job and the same mark number can be used on different jobs but the characteristics should be the same. The weight is a particular issue. If it is .01 off, the mark number will be pulled up twice.

    Hope this make it a little clearer.

    Greg
    Attached Files Attached Files

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Is [Mark] what you're referring to as a part number? Can you post a zip of your database or at least a screenshot of your table in the relationship window? (We can help you but I think we need more information to know what's going on)

    It looks to me like your table(s) aren't normalized. You should have a parts catalogue table that holds values [part_id], [part_description], [weight_per_foot], etc..., and each part would only be listed ONCE. Then in some separate table (or tables) you would link these parts to a job (or order) and specify the required length for that job/order. Because you know the [weight_per_foot] of a part your query would calculate the weight of that part for that job.

    Depending on your needs you might could always roundup the length to the nearest half foot, or foot, or what ever works for your business.

    What is the tolerance you're looking for?

    [edit]
    It should always be the same.
    So a part number will ALWAYS have the same length and weight? If that's true then store those properties in the separate part catalogue table and only refer to the [part_id] in the job order table and forget about what said about [weight_per_foot]. Again, in the master part catalogue table a part is only ever listed once so you can't have these small variances you're referring to.

    I've whipped up an example db and attached it.
    Attached Files Attached Files

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Greg,

    What exactly is the problem?
    1- your query brings up multiple records instead of 1?
    2 -differences in part weights is a quality assurance issue?

    You could have a query to get the min and max (extremes) weight grouped by Part.
    You could adjust the query to remove the criteria for weight variation.

  6. #6
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    [QUOTE=orange;456447]Greg,

    What exactly is the problem?
    1- your query brings up multiple records instead of 1?
    2 -differences in part weights is a quality assurance issue?

    You could have a query to get the min and max (extremes) weight grouped by Part.
    You could adjust the query to remove the criteria for weight variation.[/QUO

    Sorry for the delay in my reply.

    I create the list of every item that requires production. I create another table to track our production including the number of complete mark numbers and the weight of items produced. If there is a slightvariance in my original list of parts and I call out a mark number for completion, it will pull up every record with the mark number which skews my report.

    I cannot eliminate the weight from the master table because I need this data for production tracking.

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Is this weight an actual physical measurement of every item produced or is the variance simply explained by human error?

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Greg,
    I suggest you explain the process again. We do not know your environment nor your business jargon (marks).

    You have Items (whatever they are). Items require Production (whatever that involves). There appears to be some sort of stages that involve (mark numbers whatever they are) and the Production changes the Item and the resultant "produced Item" has a weight. The is some variation in the "weights" of "similarly produced Items" that is causing an issue (whatever that entails).

    Treat us like 8 yr olds and describe to us in simple terms what these bracketed phrases mean. Simple, simple plain English. Remember we (at least me) do not understand your environment nor the production process nor the significance of the weight variation.
    In many production shops, parts/products have specifications/tolerances that are identified. The produced parts are verified against such specifications and there are standard procedures for the parts that do not meet specifications. It is unclear if this is your situation or not.
    Please help us to help you by clarifying things.

    We have to understand the WHAT of the problem/opportunity before we can suggest/advise on the HOW it might be resolved using MS Access.
    Good luck.

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

Similar Threads

  1. Replies: 16
    Last Post: 03-22-2018, 09:27 AM
  2. update multiple records, best method
    By NikoTheBowHunter in forum Database Design
    Replies: 2
    Last Post: 06-08-2017, 08:59 AM
  3. Replies: 2
    Last Post: 03-29-2017, 02:43 PM
  4. Unique Records, Is this the best method?
    By pattrickcolin in forum Access
    Replies: 4
    Last Post: 02-22-2017, 01:37 AM
  5. How do I detect new records.
    By jcollins in forum Programming
    Replies: 3
    Last Post: 03-17-2011, 03:48 PM

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