Syntax for DISTINCT query on one column in MS Query

Question

I have an Excel spreadsheet which I use as a relational database for my milk round. I query this database using MS Query in Excel (Mac 2011 Version) to generate my delivery routes. One of the columns is the customer address and I'd like to have this shown once per order i.e. have a distinct query for just this column while displaying multiple other rows. It's purely for cosmetic purposes to make the spreadsheet less cluttered.

The main spreadsheet I use as my database has column headings which I have screenshotted, complete with some sample data:

From this main spreadsheet I use MS Query to generate my delivery route which looks like this:

As you can see there is a lot of repeated data in the route generated from the query. What I'd like to do is have just one instance of the address per customer's order, it would help with the legibility of the route when opened in an iPad. I hide other columns that aren't really necessary to help in that regard.

*EDIT From isolated's comments below, here's a screenshot of ideally how the data returned from the query should look:

I've manually deleted the repeated info in the name & address column to achieve the desired result. I've also hidden some columns that aren't really necessary and I use some conditional formatting rules to help distinguish each customer's order.

EDIT*

I have tried using a group by clause and the following window function but can't get it to work:

SELECT *
FROM (
    SELECT “All Orders”.”Route ID”,
          “All Orders”.Name,
          “All Orders”.Address
          ROW_NUMBER() OVER(PARTITION BY “All Orders”.Address
                                ORDER BY “All Orders”.Address DESC) AS row_number
    FROM “All Orders”
    ) AS rows
WHERE row_number = 1;

Whenever I try to run the query I get an error message regarding syntax. Hopefully someone can tell me where I'm going wrong!

Answer 1

I don't know MS Sql at all, but you could do something with a formula in excel. If you don't like this solution, simply put a comment below that you would still like a sql route and I can get you a query to try to adapt to ms sql.

Create another column and call it address2 (or several more columns if your address field is multiple columns).

Then use this/these formula and adjust as needed:

    Column F (address2): =IF(A2=A1,"",C2)
    Column G (town2): =IF(A2=A1,"",D2)

You can then hide columns C and D.

============= U P D A T E

Here's a method that works in many dbms such as postgres, but I don't know how to adapt [rank() over (partition by...] to excel sql.

    select account, 
    cust_name, 
    item, 
    case
        when prod_rank = 1 then address
        else ''
    end address
    from (
        select 
        account, 
        cust_name, 
        item, 
        address, 
        rank() over (partition by account order by item) as prod_rank
        from table1
        )z
    order by account, item

I tried a few variations in excel sql and finally got this one to work.

    select a.Account, 
    a.Name, 
    a.Product,
    Iif(a.product = b.min_item,a.address,'') as [address]
    FROM table1 as a
    ,(
    select 
    z.Account,
    min(z.Product) as min_item
    FROM table1 as z
    group by z.Account ) as b
    where b.account = a.Account
    order by a.account, a.product