Hi all,
I am using a report to create a custom layout for printing onto labels which stick onto pallets. Each label can have up to three product details on it and the report is grouped by pallet number. The report detail height is fine when a specific pallet number has three items but if the report outputs one or two items per pallet number the report is not tall enough for the label size, meaning the other pallet number grouped items concertina up over the 2 per page labels.
Is there a way to fix the detail section height so there is blank space as a place holder for one or two items? I am currently using a subreport for the item information as I thought this might be the way to go but it hasn't worked with my current set up. I have attached a screenshot of the relationships for the items and pallet labels tables. Maybe my tables are not set up correctly to achieve what I want?
My Report and Subreport record source are queries, SQL for both queries if helpful for you:
Subreport
Report
Thanks for your help in advance.
Chris
I am using a report to create a custom layout for printing onto labels which stick onto pallets. Each label can have up to three product details on it and the report is grouped by pallet number. The report detail height is fine when a specific pallet number has three items but if the report outputs one or two items per pallet number the report is not tall enough for the label size, meaning the other pallet number grouped items concertina up over the 2 per page labels.
Is there a way to fix the detail section height so there is blank space as a place holder for one or two items? I am currently using a subreport for the item information as I thought this might be the way to go but it hasn't worked with my current set up. I have attached a screenshot of the relationships for the items and pallet labels tables. Maybe my tables are not set up correctly to achieve what I want?
My Report and Subreport record source are queries, SQL for both queries if helpful for you:
Subreport
SQL:
SELECT Customer_Orders.Order_Number, Shipments.Order_Shipment_Number, Works_Order_Items.Ordered_Item_ID, Manufactured_Products.Product_Name, Works_Order_Items.Manufactured_Product_ID, Manufactured_Products.DPC_Dimensions, Manufactured_Products.Void_Depth, Pallet_Label_Items.Pallet_Item_Quantity, Pallets.Pallet_Number, Pallet_Label_Items.Pallet_ID
FROM (Manufactured_Products INNER JOIN ((Customer_Orders INNER JOIN Shipments ON 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) INNER JOIN (Pallets INNER JOIN Pallet_Label_Items ON Pallets.Pallet_ID = Pallet_Label_Items.Pallet_ID) ON Works_Order_Items.Ordered_Item_ID = Pallets.Ordered_Item_ID
WHERE (((Customer_Orders.Order_Number)=[Reports]![AIM_Pallet_Labels_Report_and_Subreport]![Order_Number]) AND ((Shipments.Order_Shipment_Number)=[Reports]![AIM_Pallet_Labels_Report_and_Subreport]![Order_Shipment_Number]));
Report
SQL:
SELECT Shipments.Order_Number, Shipments.Order_Shipment_Number, Shipments.Transport_Mode, Shipments.Number_Of_Pallets, Shipments.Clips_Pallet_Number, Works_Order_Items.Ordered_Item_ID, Pallets.Pallet_ID, Pallets.Pallet_Number, Pallet_Label_Items.Pallet_Item_Quantity
FROM (Shipments INNER JOIN Works_Order_Items ON Shipments.Order_Shipment_Number = Works_Order_Items.Order_Shipment_Number) INNER JOIN (Pallets INNER JOIN Pallet_Label_Items ON Pallets.Pallet_ID = Pallet_Label_Items.Pallet_ID) ON Works_Order_Items.Ordered_Item_ID = Pallets.Ordered_Item_ID
WHERE (((Shipments.Order_Shipment_Number)=[Forms]![Pallet_Labels_Shipment_Number_Pop_Up_Form]![Combo0]));
Thanks for your help in advance.
Chris
Attachments
Last edited: