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!

How do you want the output to look? For example, would Jane Bloggs still have two rows, but you want to display the address on just the first row? Also, why does Joe Generic have two acc_id's, one of which matches Jane Bloggs? — Isolated, Oct 02 '20 at 19:29
Yes, exactly how you've described. Just one address in the address column for Jane Bloggs but with the two rows for her order. Joe Generic having 2 acc_ids was a typo! — pintofheineken, Oct 03 '20 at 13:21
And I suppose you don't want to select just the distinct address from the query that returns your 2nd screenshot? If you still need everything displayed (multiple rows per account), then does MS Query support rank function? Maybe you could rank each account/row (acc 1, rank 1-2-3... acc2, rank 1-2-3 and so on) as a sub-query. Then use a case statement such as (case when acct_prod_rank = 1 then address else ' '). — Isolated, Oct 05 '20 at 13:55
I've edited my original question to include a screenshot of how the ideal result would look. I'm not sure if MS Query supports rank function and would have no idea of the proper syntax! — pintofheineken, Oct 06 '20 at 11:27

Isolated · Answer 1 · 2020-10-07T02:12:30.320

0

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

edited Oct 07 '20 at 02:12

answered Oct 06 '20 at 14:09

Isolated

5,169
1
6
18

Thanks for the answer Isolated, ideally a SQL query would still be the preferred solution as the position of the second address column in Excel will break the contiguous data return from the query – pintofheineken Oct 06 '20 at 20:55
I grew up on a dairy farm and have a soft spot for milk drivers. I hope the update above works for you. – Isolated Oct 07 '20 at 02:06
Hi Isolated, thanks again for your help. Unfortunately the update doesn't appear to work, I get an error message saying "no such function: if". Is there a typo before the if statement in the excel sql variation above or is it a square bracket? Either way doesn't work, I hope your soft spot remains for us delivery drivers a while longer! – pintofheineken Oct 07 '20 at 08:00
It should be "iif" (two i's). If that still doesn't work, then I'm out of ideas sorry. Works for me, but I'm a PC user on excel 2016. – Isolated Oct 07 '20 at 13:27
Unfortunately it still gives me the same error, "no such function: iif". I really appreciate all your help with this Isolated, many thanks for trying! – pintofheineken Oct 07 '20 at 13:35

Syntax for DISTINCT query on one column in MS Query

1 Answers1

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