Crosstab problem - driving me nuts

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • mattlightbourn
    New Member
    • Sep 2007
    • 8

    #1

    Crosstab problem - driving me nuts

    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
  • mattlightbourn
    New Member
    • Sep 2007
    • 8

    #2
    Originally posted by mattlightbourn

    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! GarmentTypesLis t 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

    Comment

    • mattlightbourn
      New Member
      • Sep 2007
      • 8

      #3
      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_Typ es.GarmentName, Tbl_Sizes.Size, Tbl_Sizes.SizeB ase, Tbl_Size_Specif ications.BuyerR ef, Tbl_Size_Specif ications.Descri ption, Tbl_Size_Specif ications.Extend ed, Tbl_Size_Adjust ments.SizeSpecI D, Tbl_AdjustMeasu res.ChangeAmoun t
      FROM ((Tbl_Garment_T ypes INNER JOIN Tbl_Sizes ON Tbl_Garment_Typ es.GarmentID=Tb l_Sizes.Garment TypeID) INNER JOIN (Tbl_Size_Speci fications INNER JOIN Tbl_Size_Adjust ments ON Tbl_Size_Specif ications.SizeSp ecID=Tbl_Size_A djustments.Size SpecID) ON Tbl_Garment_Typ es.GarmentID=Tb l_Size_Adjustme nts.GarmentID) INNER JOIN Tbl_AdjustMeasu res ON Tbl_Size_Adjust ments.AdjustID= Tbl_AdjustMeasu res.AdjustID
      WHERE (((Tbl_Garment_ Types.GarmentNa me)=Forms!Frm_M ain!GarmentType List));


      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

      Comment

      • mattlightbourn
        New Member
        • Sep 2007
        • 8

        #4
        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 "descriptiv eID" 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.2 50
        Inside Leg............ ........-1.500....-1.000....-0.500....0.000. ...1.000....2.0 00
        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

        Comment

        • mattlightbourn
          New Member
          • Sep 2007
          • 8

          #5
          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

          Comment

          Working...