Reporting Query/Suggestion - Garment Size and Quantity

[vectorBS]

Hi,

I need a suggestion as to which method will be most efficient way of accomplishing this task. I need to build invoice report with Garment Sizes and Quantity. There are seperate records for different sizes with their corresponding quantity in the shipment table. I have to display this information in Invoice Report in a single row with quntities under size lables ( S, M, L, XL, XXL ). Access doesnt allow grouping on multiple fields so i have to stick to programming. I was thinking of the following two methods

a. I come up with a nested SQL which does the job.

b. Before the report is displayed i go through the record set and edit the recordset to get all the sizes and quantity in one row.

c. WILDER APPROACH!! I change the query which i have posted below to exempt size and quantity information so there will be only record for Garment, Style and Colour combination and then run query for each combination for every size and their relevant quantity and then either place those values in the record set and displaying the edited record set or manually setting those values on the report. TBH i havent really thought about displaying at the moments as i havent gone through the calculating part.


But before i go down any of these approaches i want to find out if it is possible to

1. Somehow group three fields in a query
2. Which event is triggered when the report is opened just before its displayed and on form navigation

Or if some one has more experience with Access Reports as i dont have any and they can inform me of a way that i dont have to go through all this.

 SELECT Invoice.InvoiceNumber, Invoice.ShipmentNumber, Shipment.ShipmentDate, Shipment.OrderNumber, ShipmentDetails.GarmentNumber, ShipmentDetails.StyleNumber, ShipmentDetails.SizeID, ShipmentDetails.ColourCode, ShipmentDetails.ShipmentQty, ShipmentDetails.UnitPrice, ShipmentDetails.Discount, ShipmentDetails.Price, StockOrder.StockOrderNumber, Customer.CustomerFullName, Customer.CustomerCompanyName, Customer.CustomerInvoiceAddress, Customer.CustomerInvoiceCity, Customer.CustomerInvoicePostcode, Customer.CustomerShippingAddress, Customer.CustomerShippingCity, Customer.CustomerShippingPostcode
FROM (Customer INNER JOIN StockOrder ON Customer.CustomerNumber = StockOrder.CustomerNumber) INNER JOIN ((Shipment INNER JOIN Invoice ON Shipment.ShipmentNumber = Invoice.ShipmentNumber) INNER JOIN ShipmentDetails ON Shipment.ShipmentNumber = ShipmentDetails.ShipmentNumber) ON StockOrder.StockOrderNumber = Shipment.OrderNumber
ORDER BY ShipmentDetails.GarmentNumber, ShipmentDetails.StyleNumber, ShipmentDetails.ColourCode;

[vectorBS]

I apologize for posting in my own question but I need help as soon as possible. Could someone give me any ideas?

[MSeda]

I think a crosstab query will help you. in the crosstab query set the garmet name or ID to the Row heading and Select Size as the Column heading then sum or count the Quantity as the value.
the result is a single record for each garmet displaying the quantity of each size. is this what you are trying to acheive?

[vectorBS]

I think a crosstab query will help you. in the crosstab query set the garmet name or ID to the Row heading and Select Size as the Column heading then sum or count the Quantity as the value.
the result is a single record for each garmet displaying the quantity of each size. is this what you are trying to acheive?

Thanks for the reply. I dont think it will help as the row has to be decided by Combination of GarmentNumber, StyleNumber and ColourCode. Then there is columns for each size but i dont have to count or sum, the quantities come from shipment.
I will give crosstab a try as i never tried it before but in the meanwhile i was trying this for every size it did take some time to load. I do face that there might be some logical mistake in this statement which is as follows:

ControlSource =nz(DLookUp("ShipmentQty","ShipmentDetails","SizeID = 'S' or SizeID = '30' And ShipmentNumber = " & [ShipmentNumber] & " AND GarmentNumber = '" & [GarmentNumber] & "' AND StyleNumber = '" & [StyleNumber] & "' AND ColourCode = '" & [ColourCode] & "'"),0)

It is returning values where there is no value in the shipment e.g. in case of XL.Do you think OR is causing any problem?

[MSeda]

I think the crosstab will work you since you can have more than one row heading. I didn't really examine the code you posted yet because I think the crosstab is much easier but if after fiddling with a cross tab you find it won't work for you we can look at some of the other things you are trying.

[vectorBS]

I think the crosstab will work you since you can have more than one row heading. I didn't really examine the code you posted yet because I think the crosstab is much easier but if after fiddling with a cross tab you find it won't work for you we can look at some of the other things you are trying.

Thanks a lot. I have tried Crosstab now. The code for the query is

TRANSFORM Sum(ShipmentDetails.[ShipmentQty]) AS SumOfShipmentQty
SELECT ShipmentDetails.[GarmentNumber], ShipmentDetails.[StyleNumber], ShipmentDetails.[ColourCode]
FROM ShipmentDetails
GROUP BY ShipmentDetails.[GarmentNumber], ShipmentDetails.[StyleNumber], ShipmentDetails.[ColourCode]
PIVOT ShipmentDetails.[SizeID] ;

Now i must say the crosstab query is amazing. It came within an inch of solving my problem but only just :(
The problems i am having with it are that there are two kinds of sizes S,M,L,XL,XXL for Shirts e.t.c and 30,32,34,36,38 for Jeans etc. Now the display columns on the report are as follows S/30, M/32, L/34, XL/36, XXL/38. Cross tab will display each size seperately rather than what is required. Also the Total Quantity and Price need to be summed for all the relavant sizes and displayed seperate column as well as the unit price for each row.

[MSeda]

you can either make a second query or perform the calculations directly on the report.
If the price is the same for all sizes of a Garmet/Style/Color then you can just add it to the cross tab query as a row heading. In order to combine the size catagories for S/30, M/32 etc... just add the two fields if you're doing the calculations on the report just set the control source to "= [S] + [30]" and so on. for the total of all sizes you would do the same but with all of the size fields included in the equation. On your report or in the secondary query multiple the total of all sizes by the total column to get the extended total.

[vectorBS]

Excellent stuff!! You are a star. Problem solved. One more thing came up though. If the shipment table doesnt have any quantities for a size then the size column for that size doesnt come up and i get errors. What do you recomend in this situation?

[MSeda]

Use a union query to add 0 quantity entries for every size to ensure they appear in the crosstab.

SELECT ShipmentDetails .[GarmentNumber], ShipmentDetails .[StyleNumber], ShipmentDetails .[ColourCode], ShipmentDetails .[SizeID], ShipmentDetails .[ShipmentQty] FROM ShipmentDetails
Union SELECT 0, 0, 0, “S”, 0 FROM ShipmentDetails
Union SELECT 0, 0, 0, “30”, 0 FROM ShipmentDetails
Union SELECT 0, 0, 0, “M”, 0 FROM ShipmentDetails
Union SELECT 0, 0, 0, “32”, 0 FROM ShipmentDetails
Etc.

then use this query for the crosstab instead of shipmentdetails directly.
You will probably want to make a query on the crosstab query to filter out the dummy records and clean up the nulls as well as combine the sizes to be the datasource for your report, something like

SELECT CrossTab.[GarmetID], CrossTab.[StyleID], CrossTab.[Color], CrossTab.Cost, nz([S],0) + nz([30],0) AS [S/30] etc…
FROM CrossTab
WHERE CrossTab.[GarmetID] <> 0;

[vectorBS]

Cheers for that i managed to do it through format function as follows

TRANSFORM nz(Sum(StockOrderDetails.Quantity),0) AS SumOfQuantity
SELECT StockOrder.StockOrderNumber, StockOrder.StockOrderDate, StockOrder.StockOrderShipDate, Customer.CustomerFullName, Agent.AgentFullName, StockOrder.SeasonNumber, StockOrderDetails.GarmentNumber, StockOrderDetails.StyleNumber, StockOrderDetails.ColourCode, Colour.ColourDescription, StockOrderDetails.StockNumber, StockOrderDetails.UnitPrice, StockOrderDetails.Discount, Customer.CustomerCompanyName, Customer.CustomerShippingAddress, Customer.CustomerShippingCity, Customer.CustomerShippingPostcode, Sum(StockOrderDetails.Quantity) AS [Total Of Quantity], Sum(StockOrderDetails.Price) AS SumOfPrice
FROM (Customer INNER JOIN (Agent INNER JOIN StockOrder ON Agent.AgentNumber = StockOrder.AgentNumber) ON Customer.CustomerNumber = StockOrder.CustomerNumber) INNER JOIN (StockOrderDetails INNER JOIN Colour ON StockOrderDetails.ColourCode = Colour.ColourCode) ON StockOrder.StockOrderNumber = StockOrderDetails.StockOrderNumber
GROUP BY StockOrder.StockOrderNumber, StockOrder.StockOrderDate, StockOrder.StockOrderShipDate, Customer.CustomerFullName, Agent.AgentFullName, StockOrder.SeasonNumber, StockOrderDetails.GarmentNumber, StockOrderDetails.StyleNumber, StockOrderDetails.ColourCode, Colour.ColourDescription, StockOrderDetails.StockNumber, StockOrderDetails.UnitPrice, StockOrderDetails.Discount, Customer.CustomerCompanyName, Customer.CustomerShippingAddress, Customer.CustomerShippingCity, Customer.CustomerShippingPostcode
PIVOT Format(StockOrderDetails.SizeID) In ("S","M","L","XL","XXL","30","32","34","36","38");

I have one more question. I have made a form through the same query but the fields are read only. After some digging around i found out that crosstab query based forms are readonly. Is there a way around that? It will save user entering same information everytime for a new size.

Much appreciated.

[vectorBS]

I would appreciate if some one can comment as this is a requirement now. I dont want to alter the DB Design just for this. Any ideas will be appreciated.