Results 1 to 8 of 8
  1. #1
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182

    No Duplicates when Viewing Query

    Hello.



    I am trying to setup a query between two tables (tblAlpha and tblBeta). These tables share an IncidentIDNum.

    tblAlpha can only have one record per IncidentIDNum

    However, tblBeta can have up to two records per IncidentIDNum. This is because tblBeta can have an incident where the value of cboCategory can be both "Fake" and "Smuggled"

    When I run my query now, I can have the IncidentIDNum repeated twice because tblBeta has both "Fake" and "Smuggled" records.

    When I parse my data however, "Fake" always supercedes "Smuggled". Therefore, I would like my query to do as follows: {Wherever a tblBeta has both "Fake" and "Smuggled" linked to a single incident, only return "Fake" in the query.)

    I really hope that makes sense...

    Thanks!

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Query 1 - all the "Fake".
    Query 2 - all the "Smuggled" where the ID does not exist in query 1 (non-matching query)
    Query 3 - UNION both queries together

  3. #3
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    My database keeps freezing when I try to do that. I also do not thing that is going to work...


    Number of Incident Date of Incident Activity City
    20 14-Jan-16 Fake New York
    35 11-Feb-16 Fake New York
    35 11-Feb-16 Smuggled New York
    41 01-May-16 Fake New York
    42 27-Jan-16 Smuggled New York


    In the image above, I ran a query to find all incidents involved New York occurring in 2016. "Number of Incident" shared between two tables. "Date" is in tblAlpha. "Activity" and "City" are in tblBeta.

    Incident#35 appears twice in my query because it was listed as a "Fake" and "Smuggled" incident. I want my query to ignore the second "Incident#35" with the 'Smuggled' value.





  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Query 2 (see above)

  5. #5
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    I must not be doing "Query 2" correctly. Can you please give me a little more information

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Go to query wizard, select unmatched query. First will be query1, second will be the table/query that you used in query1.
    Then select which field is the matching field (incident?). This query will show all the records in that table that have only smuggled.

  7. #7
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    second will be the table/query that you used in query1.
    I used two tables to make the Query1.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Use the table that has the incident and the smuggled, compare that to query 1.

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

Similar Threads

  1. Replies: 4
    Last Post: 05-27-2016, 01:40 PM
  2. Viewing data from a query
    By Paranap in forum Access
    Replies: 11
    Last Post: 10-22-2015, 10:17 AM
  3. Replies: 3
    Last Post: 12-04-2014, 10:12 AM
  4. running query without viewing that
    By afshin in forum Queries
    Replies: 19
    Last Post: 01-19-2012, 11:29 AM
  5. Replies: 1
    Last Post: 02-20-2011, 08:42 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