Hi all,
I have a problem which has been driving me nuts. Crosstab queries!
I have a database witch a few different tables to do with garment manufacturing.
I have a table for a client account, garment type (Jacket, skirt, etc), client sizing spec and descriptives (measurement names i.e: Shoulder to cuff, inside leg, etc), available sizes (ie. 4,6,8,10,12,14, 16,18,20,S,M,L, SM,ML,One) and pattern adjustments (if size 10 is the base, the shoulder to cuff measurement for a size 8 is adjusted by -0.375 cms, etc)
Now, I am used to seeing and using the data in a spreadsheet where I take advantage of the columns and rows.
I have made a set of lists on a form from the accounts_tbl and its all good. I can select a garment type, select descriptives used for measuring that type of garment and then select a size and in a subform, I can then add an adjustment for it.
To view it, I have created a query to join the sizes with adjustments and descriptives and garment types. I then did a crosstab query on it and got "kind of" the result I want, however.
The setup of crosstab
The rows are listed using garment type and descriptives
The columns are using size and the data "to be" displayed is ChangeAdjust
SQL View is:
TRANSFORM First([Tbl_Size_Adjust ments Query].ChangeAmount) AS FirstOfChangeAm ount
SELECT [Tbl_Size_Adjust ments Query].GarmentName, [Tbl_Size_Adjust ments Query].Description, [Tbl_Size_Adjust ments Query].Extended
FROM [Tbl_Size_Adjust ments Query]
GROUP BY [Tbl_Size_Adjust ments Query].GarmentName, [Tbl_Size_Adjust ments Query].Description, [Tbl_Size_Adjust ments Query].Extended
PIVOT [Tbl_Size_Adjust ments Query].Size;
Problems:
1. I don't want to transform ChangeAmount - I want to show it as it is in the table. I don't want Count, Sum, Average, First, Last,etc
2. I want the ChangeAdjust data to be formatted as "##.###" - the crosstab query is rounding up/down and it needs to be exact - cannot find a way of formatting it.
3. I changed PIVOT line to PIVOT [Tbl_Size_Adjust ments Query].Size In (4,6,8,10,12,14 ,16,18,"S","M", "L","SM","ML"," ONE"); to stop alphabetical sorting of columns.
4. There are 14 columns when displaying ALL records. However, If I filter to show a garment from the the row list, I want it to change the amount of columns to reflect the amount of sizes available for that garment (example: Tops = S, M, L = 3 columns). I am assuming that the easiest way to do this would be to have a WHERE statement in the CrossTab query or maybe in the Query that the Crosstab is based upon. How would you do this?
I look forward to your help - Sorry they are all questions at the moment and no moments of enlightenment to share but, hopefully my turn will come soon.
Thanks in advance,
Matt
I have a problem which has been driving me nuts. Crosstab queries!
I have a database witch a few different tables to do with garment manufacturing.
I have a table for a client account, garment type (Jacket, skirt, etc), client sizing spec and descriptives (measurement names i.e: Shoulder to cuff, inside leg, etc), available sizes (ie. 4,6,8,10,12,14, 16,18,20,S,M,L, SM,ML,One) and pattern adjustments (if size 10 is the base, the shoulder to cuff measurement for a size 8 is adjusted by -0.375 cms, etc)
Now, I am used to seeing and using the data in a spreadsheet where I take advantage of the columns and rows.
I have made a set of lists on a form from the accounts_tbl and its all good. I can select a garment type, select descriptives used for measuring that type of garment and then select a size and in a subform, I can then add an adjustment for it.
To view it, I have created a query to join the sizes with adjustments and descriptives and garment types. I then did a crosstab query on it and got "kind of" the result I want, however.
The setup of crosstab
The rows are listed using garment type and descriptives
The columns are using size and the data "to be" displayed is ChangeAdjust
SQL View is:
TRANSFORM First([Tbl_Size_Adjust ments Query].ChangeAmount) AS FirstOfChangeAm ount
SELECT [Tbl_Size_Adjust ments Query].GarmentName, [Tbl_Size_Adjust ments Query].Description, [Tbl_Size_Adjust ments Query].Extended
FROM [Tbl_Size_Adjust ments Query]
GROUP BY [Tbl_Size_Adjust ments Query].GarmentName, [Tbl_Size_Adjust ments Query].Description, [Tbl_Size_Adjust ments Query].Extended
PIVOT [Tbl_Size_Adjust ments Query].Size;
Problems:
1. I don't want to transform ChangeAmount - I want to show it as it is in the table. I don't want Count, Sum, Average, First, Last,etc
2. I want the ChangeAdjust data to be formatted as "##.###" - the crosstab query is rounding up/down and it needs to be exact - cannot find a way of formatting it.
3. I changed PIVOT line to PIVOT [Tbl_Size_Adjust ments Query].Size In (4,6,8,10,12,14 ,16,18,"S","M", "L","SM","ML"," ONE"); to stop alphabetical sorting of columns.
4. There are 14 columns when displaying ALL records. However, If I filter to show a garment from the the row list, I want it to change the amount of columns to reflect the amount of sizes available for that garment (example: Tops = S, M, L = 3 columns). I am assuming that the easiest way to do this would be to have a WHERE statement in the CrossTab query or maybe in the Query that the Crosstab is based upon. How would you do this?
I look forward to your help - Sorry they are all questions at the moment and no moments of enlightenment to share but, hopefully my turn will come soon.
Thanks in advance,
Matt
Comment