Date Range for Crosstab Query Column Heading (1 Viewer)

ChrisMore

Member
Local time
Today, 02:22
Joined
Jan 28, 2020
Messages
137
Hi all,

I am trying to make a crosstab query with a date range as the column heading. My 'Shipments' table contains a field called 'Despatch_Date' and I would like the crosstab query to use this data as the column heading starting from today's date through to the maximum date in the data. However, the data does not have consecutive dates and will have some gaps. What I would like is for the missing dates to also appear in the column heading.

I found the below thread and post #5 shows a public function to generate the IN list for the column headings. I don't know if this is a possible solution...?
https://www.access-programmers.co.uk/forums/threads/dynamic-crosstab-report.242545/

Thanks for your help.
Chris
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:22
Joined
May 7, 2009
Messages
15,030
how many columns do you think you want to display?
remember you are only limited by 255 columns.

to generate the "IN" (header), you only need
to generate the dates using For...Next loop
(using the smallest Date, up to the Greater date).
 

ChrisMore

Member
Local time
Today, 02:22
Joined
Jan 28, 2020
Messages
137
how many columns do you think you want to display?
remember you are only limited by 255 columns.

to generate the "IN" (header), you only need
to generate the dates using For...Next loop
(using the smallest Date, up to the Greater date).
The date columns shouldn't need go above 150 so 255 limit won't be a problem.

Would you be able to provide some more detail about the For...Next Loop (as I have never used one before). The smallest date will be the current (today's) date. Also, where do I reference to the function for it to apply to the IN header?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:22
Joined
May 7, 2009
Messages
15,030
here is a simple demo.
 

Attachments

  • demo_crosstab.accdb
    608 KB · Views: 76

ChrisMore

Member
Local time
Today, 02:22
Joined
Jan 28, 2020
Messages
137
here is a simple demo.
Amazing, thanks so much for this. I have almost got it working with my database but as my table structure is more complex than your sample I need to make some changes to the code. Below is what I currently have for my version of the code for 'cons_query'.

Code:
"TRANSFORM Max(Customer_Orders_Items.Order_Quantity) " & _
"SELECT Manufactured_Products.Product_Name, Manufactured_Products.Dimensions_Size_Profile, Customer_Orders_Items.Order_Shipment_Number " & _
"FROM (Inventory_Query INNER JOIN Products ON Inventory_Query.Product_Code = Products.Product_Code) INNER JOIN (Order_Status INNER JOIN (Manufactured_Products INNER JOIN (((Customer_Orders INNER JOIN Customer_Orders_Items ON Customer_Orders.Order_Number = Customer_Orders_Items.Order_Number) INNER JOIN Shipments ON (Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number) AND (Customer_Orders.Order_Number = Shipments.Order_Number)) INNER JOIN Works_Order_Items ON (Shipments.Order_Shipment_Number = Works_Order_Items.Order_Shipment_Number) AND (Customer_Orders.Order_Number = Works_Order_Items.Order_Number)) ON Manufactured_Products.Manufactured_Product_ID = Works_Order_Items.Manufactured_Product_ID) ON Order_Status.Order_Status_Code = Shipments.Shipment_Status) ON Products.Product_Code = Customer_Orders_Items.Product_Code " & _
"GROUP BY Manufactured_Products.Product_Name, Manufactured_Products.Dimensions_Size_Profile, Customer_Orders_Items.Order_Shipment_Number " & _
"PIVOT Shipments.Despatch_Date IN (<exp>);" _

There are a couple of issues which need addressing:
  • My version of 'cons_query' needs to have some where conditions added to the code. Below is the SQL for my version of the 'qry_dummy' query containing the same where conditions needed for the 'cons_query' code.
Code:
SELECT [Manufactured_Products].[Product_Name] & " " & [Dimensions_Size_Profile] AS Product, Customer_Orders_Items.Order_Shipment_Number
FROM (Inventory_Query INNER JOIN Products ON Inventory_Query.Product_Code = Products.Product_Code) INNER JOIN (Order_Status INNER JOIN (Manufactured_Products INNER JOIN (((Customer_Orders INNER JOIN Customer_Orders_Items ON Customer_Orders.Order_Number = Customer_Orders_Items.Order_Number) INNER JOIN Shipments ON (Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number) AND (Customer_Orders.Order_Number = Shipments.Order_Number)) INNER JOIN Works_Order_Items ON (Shipments.Order_Shipment_Number = Works_Order_Items.Order_Shipment_Number) AND (Customer_Orders.Order_Number = Works_Order_Items.Order_Number)) ON Manufactured_Products.Manufactured_Product_ID = Works_Order_Items.Manufactured_Product_ID) ON Order_Status.Order_Status_Code = Shipments.Shipment_Status) ON Products.Product_Code = Customer_Orders_Items.Product_Code
WHERE (((Shipments.Despatch_Date)>=Date()) AND ((Customer_Orders_Items.Product_Code)=250) AND ((Manufactured_Products.Manufactured_Product_ID)=44015 Or (Manufactured_Products.Manufactured_Product_ID)=44016 Or (Manufactured_Products.Manufactured_Product_ID)=44017 Or (Manufactured_Products.Manufactured_Product_ID)=90092) AND ((Order_Status.Order_Status_Code)<>"04" And (Order_Status.Order_Status_Code)<>"05" And (Order_Status.Order_Status_Code)<>"10")) OR (((Order_Status.Order_Status_Code) Is Null))
GROUP BY [Manufactured_Products].[Product_Name] & " " & [Dimensions_Size_Profile], Customer_Orders_Items.Order_Shipment_Number;

  • The Shipments.Despatch_date field will need to have a format added but I wasn't sure where to add that as I couldn't get it to work.
Thanks arnelgp
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:22
Joined
May 7, 2009
Messages
15,030
You add the formatting on the Function.
 

ChrisMore

Member
Local time
Today, 02:22
Joined
Jan 28, 2020
Messages
137
Ok, I tried to add the format to this part of the code
Code:
"PIVOT Shipments.Despatch_Date IN (<exp>);" _
but I wasn't able to get it to work and get a compile error. Whereabouts on the function does the format go?

I also tried to add the where condition to the 'cons_query' section of the form button click event but that produced an error, I think because I didn't convert the SQL to VBA correctly.
 

ChrisMore

Member
Local time
Today, 02:22
Joined
Jan 28, 2020
Messages
137
You add the formatting on the Function.
I've got the where condition working as below but I am still unable to figure out where the date formatting goes in the function, I either get an error or no formatting at all. @arnelgp can you point me in the right direction?

Code:
"TRANSFORM Max(Customer_Orders_Items.Order_Quantity) " & _
"SELECT Manufactured_Products.Product_Name, Manufactured_Products.Dimensions_Size_Profile, Customer_Orders_Items.Order_Shipment_Number " & _
"FROM (Inventory_Query INNER JOIN Products ON Inventory_Query.Product_Code = Products.Product_Code) INNER JOIN (Order_Status INNER JOIN (Manufactured_Products INNER JOIN (((Customer_Orders INNER JOIN Customer_Orders_Items ON Customer_Orders.Order_Number = Customer_Orders_Items.Order_Number) INNER JOIN Shipments ON (Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number) AND (Customer_Orders.Order_Number = Shipments.Order_Number)) INNER JOIN Works_Order_Items ON (Shipments.Order_Shipment_Number = Works_Order_Items.Order_Shipment_Number) AND (Customer_Orders.Order_Number = Works_Order_Items.Order_Number)) ON Manufactured_Products.Manufactured_Product_ID = Works_Order_Items.Manufactured_Product_ID) ON Order_Status.Order_Status_Code = Shipments.Shipment_Status) ON Products.Product_Code = Customer_Orders_Items.Product_Code " & _
"WHERE Shipments.Despatch_Date >=Date()AND Customer_Orders_Items.Product_Code= 250 AND Manufactured_Products.Manufactured_Product_ID= 44015 AND Order_Status.Order_Status_Code<> '04' AND Order_Status.Order_Status_Code<> '05' AND Order_Status.Order_Status_Code<> '10' OR IsNull(Order_Status.Order_Status_Code) " & _
"GROUP BY Manufactured_Products.Product_Name, Manufactured_Products.Dimensions_Size_Profile, Customer_Orders_Items.Order_Shipment_Number " & _
"PIVOT Shipments.Despatch_Date IN (<exp>);" _
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:22
Joined
May 7, 2009
Messages
15,030
what is the supposed format? eg:

02-Nov-2021 ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:22
Joined
May 7, 2009
Messages
15,030
see the cons_query on form1, i added Format$() function on the date.
same i did on the function in the module.
 

Attachments

  • demo_crosstab.accdb
    472 KB · Views: 71

ChrisMore

Member
Local time
Today, 02:22
Joined
Jan 28, 2020
Messages
137
see the cons_query on form1, i added Format$() function on the date.
same i did on the function in the module.
Perfect, thank you for your assistance :)

Just a couple of questions regarding this setup:

Is it possible to add a Sum totals for each column on the crosstab?
Is it possible to add multiple subforms which are controlled by the one command button on the form?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:22
Joined
May 7, 2009
Messages
15,030
i created query2 (you just copy/paste query1 and rename it query2).
on the form's code, i make union query and put the sql to query2.
i then make query2 the sourceObject of the subform.
 

Attachments

  • demo_crosstab.accdb
    864 KB · Views: 77

ChrisMore

Member
Local time
Today, 02:22
Joined
Jan 28, 2020
Messages
137
i created query2 (you just copy/paste query1 and rename it query2).
on the form's code, i make union query and put the sql to query2.
i then make query2 the sourceObject of the subform.
Thanks! And is it possible to have multiple subforms of crosstab queries on this form controlled by the command button? All the subforms would display similar results but with slight changes to the query where condition.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:22
Joined
May 7, 2009
Messages
15,030
Its possible and is close to what i have done.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:22
Joined
Feb 19, 2002
Messages
34,426
If your date range spans a year, you need to format the date as yyyy-mm-dd in order to get it to sort logically.
 

Users who are viewing this thread

Top Bottom