By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,059 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Crosstab problem - driving me nuts

P: 8
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_Adjustments Query].ChangeAmount) AS FirstOfChangeAmount
SELECT [Tbl_Size_Adjustments Query].GarmentName, [Tbl_Size_Adjustments Query].Description, [Tbl_Size_Adjustments Query].Extended
FROM [Tbl_Size_Adjustments Query]
GROUP BY [Tbl_Size_Adjustments Query].GarmentName, [Tbl_Size_Adjustments Query].Description, [Tbl_Size_Adjustments Query].Extended
PIVOT [Tbl_Size_Adjustments 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_Adjustments 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
Sep 18 '07 #1
Share this Question
Share on Google+
4 Replies


P: 8

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?

Matt
On the above bit - I went into the original query and under [GarmentName] I put "Tops & Jackets" in the criteria field and saved the query.

When I went to the CrossTab query, it just displayed the records I wanted and ensured that only the amount of columns I wanted appeared. Perfect.

What do I put in the criteria for it to reference Forms!Frm_Main!GarmentTypesList as source of criteria?

For the list view in the form, I would set it up to still have 14 columns but the data would always show starting at the first column instead of choosing one on the other side of the list - this is fantastic news!!


The 2 VITAL remaining problems are

1. Displaying the VALUE of the field contents - NOT a count of, sum of, etc

2. Displaying that value as ##.### - three decimal places

Thanks for your time

Matt
Sep 18 '07 #2

P: 8
Sorry, replying to my own posts must be a bit weird, just adding a bit more detail - as I have tried a few things whilst I await a reply.

SELECT Tbl_Garment_Types.GarmentName, Tbl_Sizes.Size, Tbl_Sizes.SizeBase, Tbl_Size_Specifications.BuyerRef, Tbl_Size_Specifications.Description, Tbl_Size_Specifications.Extended, Tbl_Size_Adjustments.SizeSpecID, Tbl_AdjustMeasures.ChangeAmount
FROM ((Tbl_Garment_Types INNER JOIN Tbl_Sizes ON Tbl_Garment_Types.GarmentID=Tbl_Sizes.GarmentTypeI D) INNER JOIN (Tbl_Size_Specifications INNER JOIN Tbl_Size_Adjustments ON Tbl_Size_Specifications.SizeSpecID=Tbl_Size_Adjust ments.SizeSpecID) ON Tbl_Garment_Types.GarmentID=Tbl_Size_Adjustments.G armentID) INNER JOIN Tbl_AdjustMeasures ON Tbl_Size_Adjustments.AdjustID=Tbl_AdjustMeasures.A djustID
WHERE (((Tbl_Garment_Types.GarmentName)=Forms!Frm_Main!G armentTypeList));


Didn't work - no error, just nothing listed.

I went to Frm_Main and selected a garment type from the GarmentTypeList and looked at the Query and it has no records listed. I even clicked refresh.

What am I doing wrong?

thanks
Sep 18 '07 #3

P: 8
Please, please, please - help me with this one - all other stuff falls into insignificance if I can't solve this issue!

Hi all,

I really need a crosstab query that allows me to view data rather than sum, count, avg, first, last. I just want it to show the actual data listed and can find no way of doing it.

If a crosstab can't do it, is there a way of creating something from code to do it instead?

I have a query which pulls data from a load of different tables and I then have a crosstab that uses that query to produce what looks like a table.

The Crosstab ends up displaying in rows, the name of a type of garment("Tops & Jackets"), a measurement description ("shoulder to cuff") and then in row headings are the different sizes of a garment (4,6,8,10,12,14,16,18,20) and the data to be displayed which has a relationship that cross references "sizeID" with "descriptiveID" is the "adjustment value".

The adjustment value is the amount of change to a measurement of a pattern between sizes. If a garment is based on a size 10, a size 8 will have a "shoulder to cuff" reduction of -0.750cm, for a size 6 it is reduced by -1.025cm, etc.

So what I should end up with is:

--------------------------------------------------------------------------------

Tops & Jackets...................4............6.......... ..8.........10.........12.........14
Shoulder to Cuff..........-1.750....-1.025....-0.750....0.000....0.750....1.250
Inside Leg....................-1.500....-1.000....-0.500....0.000....1.000....2.000
etc
--------------------------------------------------------------------------------


I can only get a Crosstab to display a count of occurances OR I can get it to display First or last occurance - which is REALLY ANNOYING!

If I select First, the whole thing looks like:

--------------------------------------------------------------------------------

Tops & Jackets...................4............6.......... ..8.........10.........12.........14
Shoulder to Cuff..........-1.750....-1.750....-1.750...-1.750...-1.750...-1.750
Inside Leg....................-1.500....-1.500....-1.500...-1.500...-1.500...-1.500
etc
--------------------------------------------------------------------------------

Can someone PLEASE Help me find a solution to this - please, please, please!

Thanks you for any and all input!

Kind Regards

Matt
Sep 20 '07 #4

P: 8
I've been doing a bit more research and found that you should be able to do Concatenated results instead of a count or occurances.

If concatenated results can be done, that is fine as there is only 1 occurance for each X meets Y relationship.

Thanks again
Sep 20 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.