Cross Tab Query How to Get Distinct Values

hgriff16

New Member
Joined
Jul 29, 2019
Messages
4
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
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,576
Office Version
2013
Platform
Windows
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).
 

hgriff16

New Member
Joined
Jul 29, 2019
Messages
4
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.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,576
Office Version
2013
Platform
Windows
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.
 

hgriff16

New Member
Joined
Jul 29, 2019
Messages
4
Service Location NameFulfillment Order IDDate Fulfilled (Common)Total Parts Cost
Albany110/1/2019120
Albany110/1/201940
Albany210/1/201920
Albany310/8/201945
Orlando510/1/201921
Orlando510/1/201912
Orlando610/15/201934

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

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
Albany18648
Orlando3637

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

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.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,576
Office Version
2013
Platform
Windows
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:

Forum statistics

Threads
1,084,749
Messages
5,379,620
Members
401,616
Latest member
YoSquidly

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top