Results 1 to 7 of 7

Thread: Cross Tab Query How to Get Distinct Values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cross Tab Query How to Get Distinct Values

    Hello everyone, This is my first time posting just as an FYI. I am trying to make a cross tab query that shows Location as the rows and Week Number as the Columns with a value set up in builder. Where I'm running into an issue is that in order to get the accurate value, I need the distinct count or Order ID's because items are shipped together and each shipment cost $3.5 + the sum of the part cost.. Every Time I try to group the Order ID's the locations duplicate, but the values are correct. Let me know if there is any more information you need.Thanks

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross Tab Query How to Get Distinct Values

    Sounds like it's not working but it's not entirely clear what your sql is doing. You probably should post the query text (and some sample data if possible).

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross Tab Query How to Get Distinct Values

    Here is the SQL data.

    TRANSFORM Sum([bo5a]![Total Parts Cost]+3.5) AS Expr1
    SELECT bo5a.[Service Location Name]
    FROM bo5a LEFT JOIN Calander ON bo5a.[Date Fullfilled (Common)] = Calander.[2019]
    WHERE (((Calander.[Period ])=10) AND ((bo5a.[National Account Flag])="N"))
    GROUP BY bo5a.[Service Location Name], bo5a.[Fulfillment Order Id], Calander.[Period ], bo5a.[National Account Flag]
    PIVOT Calander.Week;



    and I cannot publish any sample data because it has customer information on it.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross Tab Query How to Get Distinct Values

    The grouping looks like it won't work that way but it is hard to say because I don't see what is being grouped. However, since you are grouping on fields that aren't in the output it is almost certainly creating extra groups that will appear as duplicate rows.

    One solution would be to put the results in a temp table, then select distinct from that table - effectively removing the duplicates. (two steps).

    Another would be to resolve the grouping and get the output fixed up on in a first query then do the pivot transform on the first query (also two steps).

    You don't need to publish the actual data. Just dummy data that can be used to replicate the problem.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross Tab Query How to Get Distinct Values

    Service Location Name Fulfillment Order ID Date Fulfilled (Common) Total Parts Cost
    Albany 1 10/1/2019 120
    Albany 1 10/1/2019 40
    Albany 2 10/1/2019 20
    Albany 3 10/8/2019 45
    Orlando 5 10/1/2019 21
    Orlando 5 10/1/2019 12
    Orlando 6 10/15/2019 34

    This is what the data basically looks like and at the end it should look like

    Service Location Name Week 1 Week 2 Week 3
    Albany 186 48
    Orlando 36 37

    So if the fulfillment order ID is the same they go on the same truck so the shipping would only be $3 per distinct fulfillment order.

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross Tab Query How to Get Distinct Values

    using my second two step solution described above, first you preprocess the data to get something that works for the crosstab:

    Code:
    select 
    	T.[Service Location Name], 
    	T.[Date Fulfilled (Common)], 
    	Sum((T.[TotalPartsCost])+3) as TotalCost
    
    from
    	(
    		select 
    			[Service Location Name], 
    			[Date Fulfilled (Common)], 
    			[Fulfillment Order ID], 
    			Sum([Total Parts Cost]) as TotalPartsCost
    		from
    			Table13
    		group by 
    			[Service Location Name], 
    			[Date Fulfilled (Common)], 
    			[Fulfillment Order ID]
    	) T
    
    group by 
    	T.[Service Location Name], 
    	T.[Date Fulfilled (Common)]
    Then you can cross tab it:
    Code:
    transform Sum(TotalCost) AS SumOfTotalCost
    select 
    	[Service Location Name]
    from
    	Query34
    group by 
    	[Service Location Name]
    pivot 
    	[Date Fulfilled (Common)];

    The result of the first query is (I call this Query 34):
    ---------------------------------------------------------------
    | Service Location Name | Date Fulfilled (Common) | TotalCost |
    ---------------------------------------------------------------
    | Albany                | Week 1                  |       186 |
    | Albany                | Week 2                  |        48 |
    | Orlando               | Week 1                  |        36 |
    | Orlando               | Week 3                  |        37 |
    ---------------------------------------------------------------


    The result of the second query is (I call this Query35):
    ----------------------------------------------------
    | Service Location Name | Week 1 | Week 2 | Week 3 |
    ----------------------------------------------------
    | Albany                |    186 |     48 |        |
    | Orlando               |     36 |        | 37     |
    ----------------------------------------------------



    Note:
    I didn't bother with the dates - not sure if you really need date data transformed into Week 1, Week 2, etc. but that would be an additional piece if so.
    Last edited by xenou; Oct 15th, 2019 at 05:37 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross Tab Query How to Get Distinct Values

    This worked perfectly, Thank you so much.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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