Can anyone help me on how to set this up i think i need a sub-query but no idea on how to set it up.
Code:
TRANSFORM Nz(Count([Consignment-Query-NoOrders].Haulier))+0 AS CountOfHaulier
SELECT [Consignment-Query-NoOrders].Haulier, [Consignment-Query-NoOrders].Town, Count([Consignment-Query-NoOrders].Haulier) AS Total
FROM [Consignment-Query-NoOrders]
GROUP BY [Consignment-Query-NoOrders].Haulier, [Consignment-Query-NoOrders].Town
PIVOT [Consignment-Query-NoOrders].BookStatus In ("Ontime","Early","Late");
The above works great to get a total for ontime early & late for haulier but i need to split this down by town
so the output could look something like this
Haulier |
Town |
Ontime |
Early |
Late |
|
Haulier1 |
Town1 |
0 |
3 |
2 |
|
Haulier1 |
Town2 |
2 |
5 |
6 |
|
Haulier1 |
Town3 |
0 |
1 |
2 |
|
Haulier2 |
Town1 |
2 |
0 |
2 |
|
Haulier2 |
Town2 |
2 |
5 |
4 |
|
Haulier3 |
Town2 |
0 |
0 |
1 |
|
Haulier3 |
Town3 |
1 |
0 |
0 |
|
Haulier3 |
Town4 |
0 |
0 |
1 |
|
|
|
|
|
|
|
The Early Late Ontime function works i just need to split it up by town from the query
Thanks in advance for any help with this one