Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Cross Tab Table Row data into columns (2003)

    I have data that I need to report on that lists parts used against a trouble ticket.
    Each line of data will have the ticket, part used and Qty.
    There can be several rows of data for each ticket as many parts can be used on a ticket

    Ticket____Part
    ABC_____123
    ABC_____456
    ABC_____789
    DEF_____690
    GHL_____999
    GHL_____333

    What I want to do in a query is list the parts used in columns:

    Ticket __PU1____PU2___PU3
    ABC___123_____456___789
    DEF___690
    GHL___999_____333

    With the current data, there is a max of 26 different parts used against 1 ticket, but this can change as I get more data to report on.

    One thing I was thinking of, but can't quite work out how to do would be to have a query with a incremantal counter column that increases 1 by 1 as the ticket stays the same
    IE: A: if ticket = previous ticket, A=A+1,1 (can't do this in Access as you get a circular reference)

    Then do a x-tab query listing the ticket as the row, A as the column and the part as the value.

    I have tried to add a AutoNumber field to a blank table & then append the data sorted by Ticket, Part Used into that table.
    I can get a Dlookup to work and retrieve the ticket from the previous row and then use a different field to attempt to create that ticket level counter, but this is not quite working how I want it to.

    Can someone point me in the right direction?
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cross Tab Table Row data into columns (2003)

    Once you have an AutoNumber field, you can create a query based on the table, with a calculated column:

    Col: DCount("*","tblTickets","Ticket=" & Chr(34) & [Ticket] & Chr(34) & " AND ID<=" & [ID])

    where tblTickets is the name of the table, and ID is the name of the AutoNumber field. You can then create a crosstab query based on this query, with Col as Value field, and First (or Max) as Total option.

    See attached mini-demo.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cross Tab Table Row data into columns (2003)

    Thank you for pointing me to the Dcount() function. That is giving me what I am looking for. The only thing I had to do was to add Val() to the outside of the Dcount() function so the columns would sort as numbers instead of characters (so I would get 1-2-3 instead of 1-10-2-3).

    Much simplier then what I was thinking of.

    Thanks again!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Table Row data into columns (2003)

    Thanks for this excellent example. I have had numerous times that this would have come in so handy. I thought the only way around a problem like this would be to create a report containing a numbered field that incremented for each item within the group and then export the report as a text file. I then linked the text file as a table within my database to serve as the source for a crosstab query. The method you used is much simpler. I will save this example for future use.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •