Results 1 to 7 of 7

Thread: Access 365 - Query - Duplicate Unique Values in query output - how to resolve please

  1. #1
    Join Date
    Sep 2019
    Posts
    1

    Access 365 - Query - Duplicate Unique Values in query output - how to resolve please

    The following should clearly outline the problem query output as well as the desired query output:
    Access 365 Query Issue.JPG

  2. #2
    Join Date
    Nov 2020
    Posts
    26
    Need a unique identifier field in table, autonumber should serve.

    FTEValue: IIf(DCount("*", "Table", "PositionNumber=" & PositionNumber & " AND ID<" & ID)=0,1,0)

    or

    FTEValue: IIf((SELECT Count(*) FROM Table AS Dupe WHERE Dupe.PositionNumber=Table.PositionNumber AND Dupe.ID<Table.ID)=0, 1, 0)
    Last edited by June7; 11-27-2020 at 02:39 PM.

  3. #3
    Join Date
    May 2021
    Posts
    3
    Is Access 365 still available? I thought MS announced its end of life in 2018

  4. #4
    Join Date
    Feb 2014
    Location
    Riviera Beach, Maryland, USA
    Posts
    84

  5. #5
    Join Date
    May 2021
    Posts
    3
    This is where Microsoft's Office 365 language gets really confusing. Yes, you can still buy Access Desktop using the Office 365 subscription model. However, when people say Access 365, they often also mean an online version of Access, which Microsoft used to offer as part of the Office 365 for Business suite, called Access Web Apps.

    However, Microsoft retired that in 2018 - https://www.hyperoffice.com/blog/201...ted-june-2018/

  6. #6
    Join Date
    Feb 2014
    Location
    Riviera Beach, Maryland, USA
    Posts
    84

  7. #7
    Join Date
    Aug 2022
    Posts
    20
    In Microsoft Access, if you are running a query and the output contains duplicate unique values, you can use a DISTINCT clause or a GROUP BY clause to remove the duplicates from the query results.

    To use the DISTINCT clause, you would add the keyword "DISTINCT" immediately after the SELECT keyword in your query. For example:

    SELECT DISTINCT column1, column2, column3
    FROM tableName
    This will return only the unique values for column1, column2, and column3 in the query results.

    Alternatively, you can use the GROUP BY clause to group the duplicate values together and then aggregate them. The basic syntax for the GROUP BY clause is:

    SELECT column1, column2, aggregate function(column3)
    FROM tableName
    GROUP BY column1, column2

    This will group the duplicate values for column1 and column2 together and apply an aggregate function (such as SUM, COUNT, AVG, etc.) to column3.

    It's worth noting that the DISTINCT clause can be used in combination with GROUP BY and aggregate function, to get the unique values of a column, after applying an aggregate function on it.

    SELECT DISTINCT column1, aggregate function
    (column2)
    FROM tableName
    GROUP BY column1

    I hope this helps you resolve the issue with duplicate unique values in your query output.
    Last edited by johnsonmillie53; 01-24-2023 at 01:03 AM.

Tags for this Thread

Posting Permissions

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