Organizing Crosstab Query

[Leesha]

Hi,
I have a crosstab query that provides net sales by store for a date range. The date range can vary from 3 months to 12 months and the months may overlap two years. The current date range for the data is 8/1/22 - 7/31/23. The data is correct however the report starts with January and goes through July vs starting with August 2022 and going through July 2023. I've tried setting the year as a row heading, but all the does is give me two rows of data per store, with null fields where there is no data for that year. I've attached an excel spreadsheet with 3 views. View 3 is what I'm trying to achieve but am not sure it's possible. This report, and other's like it are run frequently and although the user does export it to excel, their goal is to not have to put the columns into the correct format each time.
Thanks!
Leesha

[HansV]

You could use [DateField]-Day([DateField])+1 as column field.

[robertocm]

an idea of SQL from a similar case

Code: Select all

SELECT
  e1.Nombre, e1.Descripcion, e1.Fecha, e1.DescripcionAmpliada, e1.Unidades, e1.Servido,
  e1.Jan, e1.Feb, e1.Mar, e1.Apr, e1.May, e1.Jun, e1.Jul, e1.Aug, e1.Sep, e1.Oct, e1.Nov, e1.Dic
FROM (
  SELECT
    CLIENTES.NOMBRE_F As Nombre, LINEASCONTRATO.descripcion, CABECERASCONTRATO.FECHA As Fecha, LINEASCONTRATO.DESCRIPCIONAMPLIADA, LINEASCONTRATO.UNIDADES As Unidades, Sum(LINEASVENTA.UNIDADES) As Servido,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 1, LINEASVENTA.UNIDADES, 0)) As Jan,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 2, LINEASVENTA.UNIDADES, 0)) As Feb,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 3, LINEASVENTA.UNIDADES, 0)) As Mar,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 4, LINEASVENTA.UNIDADES, 0)) As Apr,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 5, LINEASVENTA.UNIDADES, 0)) As May,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 6, LINEASVENTA.UNIDADES, 0)) As Jun,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 7, LINEASVENTA.UNIDADES, 0)) As Jul,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 8, LINEASVENTA.UNIDADES, 0)) As Aug,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 9, LINEASVENTA.UNIDADES, 0)) As Sep,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 10, LINEASVENTA.UNIDADES, 0)) As Oct,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 11, LINEASVENTA.UNIDADES, 0)) As Nov,
    Sum(IIf(DATEPART(m, CABECERAVENTA.FECHA) = 12, LINEASVENTA.UNIDADES, 0)) As Dic
  FROM (((((CLIENTES INNER JOIN CABECERASCONTRATO ON CLIENTES.Id = CABECERASCONTRATO.ID_CLIENTE) INNER JOIN LINEASCONTRATO ON CABECERASCONTRATO.ID = LINEASCONTRATO.ID_CABECERA) INNER JOIN ARTICULOS ON LINEASCONTRATO.ID_ARTICULO = ARTICULOS.Id) LEFT JOIN LINEASPEDCLI ON LINEASCONTRATO.ID = LINEASPEDCLI.IDLINEACONTRATO) LEFT JOIN LINEASVENTA ON LINEASPEDCLI.ID = LINEASVENTA.IDLINEAPED) LEFT JOIN CABECERAVENTA ON LINEASVENTA.ID_CABECERA = CABECERAVENTA.ID
  WHERE CABECERASCONTRATO.SERVIDO = 0
  GROUP BY CLIENTES.NOMBRE_F, LINEASCONTRATO.descripcion, CABECERASCONTRATO.FECHA, LINEASCONTRATO.DESCRIPCIONAMPLIADA, LINEASCONTRATO.UNIDADES
) e1
ORDER BY e1.Nombre;

[Leesha]

Thank you Hans and Robertocm! I will go with Hans's suggestion as it is so much easier and works just as I need it to. One more question, Is there a way to format the date field as MMMM YYYY? I tried Billing Month: Format(([Service_Date]-Day([Service_Date])+1),"mmmm yyyy") but it didn't work.
Thanks!

[HansV]

The problem is that Format returns a text value, so once you do that, the values are sorted as text: first April, then August, December, etc.
You could list the months explicitly, as in RobertoCM's suggestion, but that won't work if you want to use different date ranges.
The best you can do without using VBA is

Format([Service_Date]-Day([Service_Date])+1,"yyyy mm")

[Leesha]

The different date ranges will be the death of me lol. The user will have to live with the dates the way they are!
Thanks again,
Leesha