Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    6

    Unanswered: Problems with Duplicates in Report - Relationship issue?

    Hi everyone,

    I would like to start off by saying that it is a pleasure to join this site with you all and I look forward to contributing to the community.

    I do have a simple question that has been plaguing me for several days. Instead of just attempting to describe the issue in a confusing text format (as I'm sure happens a lot on here) I have included snap shots of the form as well as the relationships. Hopefully this will give you a better visual representation of the problem. Please refer to the attached images.

    In a nutshell I have a data entry form that is based on a query of the main NCR Table. Included in this form are two query subreports 'NCR Employee' and 'Problem Section'. This is because there can be multiple entries (ie: problems) per main NCR entry.

    Now I would like to create reports based on these tables but I am constantly running into duplication issues. I would like to have information from the main NCR table, as well as display the multiple entries from both the "NCR Employee" section as well as the "Problem section". The problem is that when I put the Subreports on the main report, I get many duplicates in the details section.

    This is likely an easy fix but having not done a database in awhile it's amazing how quickly you get rusty! Your responses are appreciated.
    Attached Thumbnails Attached Thumbnails reltionships.jpg   main.jpg  

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Personally after a quick look at it, I would eliminate the NCR relationship from the Problem Section table, and let the relationships between the other 3 tables bring forth the pertinent information.

    Without knowing anything other than looking at the relationships and field names, I would also eliminate the Problem table and link the Problem Section directly to the NCR Employee table and possibly add a Problem Description field to the Problem Section table, unless you're only tracking the type of problem.

    Another thought: May want to make a lookup table for your Problem Category field, would make it much easier to run future reports, unless everyone uses exactly the same words and spelling for all possible problem types.

    Sam, hth
    Last edited by SCrandall; 03-31-10 at 15:55. Reason: I can't spell today!
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Mar 2010
    Posts
    6
    Hi Sam thanks for your reply. I really appreciate your expertise and willingness to share with.

    "I would eliminate the NCR relationship from the Problem Section table, and let the relationships between the other 3 tables bring forth the pertinent information."

    The Problem Section is displayed in the NCR form as displayed in grey. I believe needs to have this relationship to the main NCR table in order to sync with each NRC record.

    "I would also eliminate the Problem table and link the Problem Section directly to the NCR Employee table and possibly add a Problem Description field to the Problem Section table, unless you're only tracking the type of problem."

    The Problem table is actually just a lookup list for the drop down menus in each of the "Problem" fields in the NCR Employee and Problem Section tables. Perhaps this doesn't need to be linked as it is just a combo lookup box for these fields. I would add a problem description field but we need to search for problem category's in the future. That is why I used the "problem" table as a drop down menu (to eliminate typos and quick access).

    "May want to make a lookup table for your Problem Category field, would make it much easier to run future reports, unless everyone uses exactly the same words and spelling for all possible problem types."

    Thank you, that is the idea I had in mind but I think this might be the root of my reporting issue. I haven't found a solution to it just yet which has brought me here. Perhaps with the info I've just provided combined with your insight and experience might generate something.

    Thanks again Sam, Cheers

    Steve

  4. #4
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Morning,

    Wait wait wait, I really need to pay attention more, I was thinking this was your actual table relationship diagram. This is a report/Query layout?

    If that's the case then replicate your Problem Identification table within the query builder (should have a name like Problem Identification_1), eliminate one of the relationships from your primary Problem Identification table and join the copy of the Problem Identification table to the table that your dropped the relationship from.

    i.e.

    Drop the relationship between NCR Employee and Problem Identification tables. Link the NCR Employee and Problem Identification_1 tables.

    I hope I've understood you a bit better this time around.

    Sam
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    Mar 2010
    Posts
    6
    Sam, I took your advise and eliminated the relationships with the problem identification tables. Their relationship only exists now as a lookup combo box. Thus I eliminated what I thought was a circling effect of the tables. unfortunately this didn't work as we'd hoped.

    Everything is nicely synchronized on the main data entry form (see attached) but it's just when it comes to reports. Is there no possible way to have three queries on a single report? IE: Main NCR info in header and 'NCR employee' data and 'problem category' data in the details section? I need to keep both separate because we count problem categories over the month however you can have more than one employee contributing to that same issue so we dont want to double count.

    I never thought this would be so complicated Sam! It seems like such a basic thing, so I don't get it. Hopefully this will clear up what I am getting at. Took a couple of tries I guess

    Steve
    Attached Thumbnails Attached Thumbnails main.jpg   relationship.jpg   report.jpg  

  6. #6
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Afternoon,

    Ok, I think I'm getting how your system works...I think. Basically a Problem Section can have more than one contributing employee? If that's the case, make a copy of your DB to play with and try what I've (poorly) demonstrated in the attached jpg. (Sorry, I left my MS Paint skills behind apparently).

    Otherwise, yes, you should be able to do a couple subreports to get the information sorted the way you'd like by using DISTINCT clauses...but, I'm betting there's a design issue that hopefully we can track down if what I've attached doesn't work (or I've managed to misinterpret the design again<g>)

    Sam, we'll get it figured out yet.
    Attached Thumbnails Attached Thumbnails relationship.jpg  
    Good, fast, cheap...Pick 2.

  7. #7
    Join Date
    Mar 2010
    Posts
    6
    Hi Sam,

    Sorry for the delayed response. I hope you had a good easter weekend. I was thrilled to see your reply and diagram. I think that you may be onto something! I will implement your idea this afternoon and report back the results. The only issue I see (which is my fault) is that I can't get rid of the problem category in the NCR Employees Table as you have illustrated. I believe I caused some confusion for you. Here are the purposes of each table.

    NCR Table - General identification of non-conformance (ie:any widgets that do not pass inspection). Ie: qty 24 made of part ABC, 12 of those are flagged for quality issue after manufacturing. One NCR (non-conformance report) is generated to record all issues related to this batch of rejects that were flagged.

    Problem Section - Identifies the reasons the widgets were rejected in this order. You could identify multiple issues per NCR because there could be separate issues with any given part. So of the 24 made in the batch total, 12 widgets could be flagged with "dimensional" issues and perhaps 6 of those 12 also have a poor surface finish as well. I have this section separate from the Employee NCR table because we will count how many occurrences happen in a quarter/year. This is counted as 1 dimensional occurrence and 1 surfacing issue. At the end of the month if we asked "how many NCRs were raised because of dimensional issues?". If this was the only record in the database, it would be 1.

    NCR Employee - This identifies the employees who were involved in in the raising on the NCR. Using the example above, of the 12 widgets that had dimensional issues, there may have been 3 employees that worked on them and caused this issue. So when I ask the database at the end of the year "how many NCRs did employee 235 have and what are their most common issues?" If this was the only NCR record, it would be 1 NCR and dimensional would be the issue. However, if we did not have a separate problem tables for Problem Section and NCR Employee Sections then we would count 3 dimensional occurrences for this NCR (one per employee) if we asked the same question above: "how many NCRs were raised because of dimensional issues?"

    I think we should change the Problem Section's "Problem" field to "Issue" from now on to avoid confusion. I apologize for not making it clear the importance of maintaining two separate problem sections per NCR. The reason is if you are counting employee issues, you would find 3 for that one NCR report. If you are looking for dimensional occurrences, you should find one.

    Lordy, I don't see how anyone could make sense out of what I just wrote. I think you might have been on the right track so I am going to play around with your idea some more this afternoon Sam.

    I want to thank you for your help, it has been truly tremendous and it means so much to me and my entire company.

    Steve
    Attached Thumbnails Attached Thumbnails test1.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •