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

    Ignore Duplicates When the Value of two Separate Columns are duplicated

    I am struggling with a query. I am working on an Xtab that will show me the number of times a particular "ZCategory" is occurs. In order to do this, I first need to create a query that has the data for me to run an Xtab on. This query, called ZQuery1 is causing me problems.

    ZQuery one needs to have the following fields "IDNumber" "ZDate" "Activity" "Locale" Locale2" "Locale2Att" "Locale3" "Locale3Att" "ZCategory" "CatAtt1" and "CatAtt2"

    There can be multiple "ZCategorys" linked to a single "IDNumber". But I do not want to include records that show the same "ZCategory" value linked to the same "IDNumber". For example, I do not
    IDNumber ZCategory Activity
    Locale
    .....
    147852 Blue Beta GRB .....
    147852 Red Beta GRB .....
    147852 Blue Beta GRB .....


    I only want to see
    IDNumber ZCategory Activity Locale .....
    147852 Blue Beta GRB .....
    147852 Red Beta GRB .....


    I achieve this using MS Excel all the time by clicking on 'Remove Duplicates' and selecting both columns. I do not know how to do this through Access queries.

    In other words, I want to make a query where: IF Column(1).Value and Column(2).Value appear multiple times, THEN show one one row

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It shouldnt show duplicates. Make Q1 , the base query set to UNIQUE VALUES = TRUE, to remove duplicates,
    then run Xtab query on Q1.

    what are you using for the VALUE field.... 1st?

  3. #3
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Thanks for the response. Unfortunately, setting the query to "Unique Values = True" will not work. I should have explained my query a little better. I need some Unique Values to appear in the same query for other calculations. This is a better representation of the query.

    IDNumber ZCategory Activity QQActivity Locale Locale2
    147852 Blue Beta Beta - One GRB MEX
    147852 Red Beta Beta - One GRB MEX
    147852 Blue Beta Beta - One GRB MEX
    123456 Green Beta Beta - Two UNA CHN
    369852 Blue Beta Beta - Three GRB USA

    As you can see, because of the QQActivity, Locale, Locale2, and other fields, "Unique Values = True" cannot remove the duplicates I dont want.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't quite follow because the samples and results change, plus I'm not sure the last representation is the source query. Assuming it is, in a crosstab query, try making the first 4 fields as Group By + Row Heading; next one Group By + Column Heading; last one as the crosstab value. Which aggregate function you'll want I have no idea. Maybe Count, Min or Max seeing as how it seems to be a text field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 15
    Last Post: 08-11-2018, 02:02 PM
  2. How to ignore duplicates in a query
    By badmem in forum Queries
    Replies: 3
    Last Post: 07-19-2016, 04:15 PM
  3. Ignore duplicates in one field only
    By inhops in forum Queries
    Replies: 2
    Last Post: 06-10-2015, 02:30 AM
  4. Replies: 2
    Last Post: 06-13-2013, 09:10 AM
  5. Replies: 2
    Last Post: 08-17-2010, 02:58 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