Results 1 to 4 of 4
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    distinct count only one column

    Hello guys,
    can you help me distinct count ourder_number and sum order_quantity and filled_quantity.

    order_number Commodity date ordered_quantity
    2749316 FROZEN 11/5/2022 2
    2749316 PERISHABLES 11/5/2022 4
    2749316 GROCERY 11/5/2022 1
    5087400 FROZEN 11/5/2022 4


    5087400 PERISHABLES 11/5/2022 16
    5087400 GROCERY 1 1/5/2022 4

    I want this result.

    order_number_Count Commodity ordered_quantity
    2 FROZEN 6
    PERISHABLES 20
    GROCERY 5


    row() number or rank () or dense_rank() can be used I am not sure.

    Thanks,

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    This?

    SELECT Count(order_number) AS CntOrder, commodity, Sum(ordered_quantity) AS SumQty
    FROM table
    GROUP BY commodity;

    Those functions you referenced are not supported in Access SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If you want to use SQL functions like row() or rank(): create a view on your database with the desired expressions and link the view to access.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Are you using Access as frontend to SQLServer backend?

    Create view in SQLServer or pass-through query in Access if you want to use SQL functions Access SQL does not support.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 0
    Last Post: 02-11-2020, 08:12 PM
  2. Replies: 4
    Last Post: 01-15-2019, 11:50 AM
  3. distinct count of a filtered column
    By Jay1395 in forum Access
    Replies: 1
    Last Post: 08-23-2018, 03:38 PM
  4. Replies: 2
    Last Post: 12-05-2011, 04:53 AM
  5. Replies: 2
    Last Post: 11-18-2009, 06:49 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