Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Transform / Crosstab - get alle values

    I'm looking for a way to create a crosstab query, that wont just get me either first or last value, but all values fitting the crosstab.

    I have a fairly large table of people, with a relative week, and a weeknumber. The relative week is the row, a checkup week (every 4 weeks), and the weeknumber is the column header and the actual weeknumber (in the calendar) that the specific checkup happens.

    The attached PNG is something I made manually in Excel to give an example how I want it. The below code will only give me the first person, and removing "first" can only be done by choosing something else, count, sum, avg, last etc. There is no such thing as "all".

    I'm afraid I'm not at all familiar with transforms.

    Any help is appreciated. Trin

    Code:
    TRANSFORM First(Forloeb_tilopfoelgning.[person_cpr]) AS FirstOfperson_cpr
    SELECT Forloeb_tilopfoelgning.[Forloeb_relativuge]
    FROM Forloeb_tilopfoelgning
    WHERE (((Forloeb_tilopfoelgning.[Forloeb_ugenummer])>=DatePart("ww",Date())))
    GROUP BY Forloeb_tilopfoelgning.[Forloeb_relativuge]
    PIVOT Forloeb_tilopfoelgning.[Forloeb_ugenummer];
    Attached Thumbnails Attached Thumbnails Untitled.png  
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Oct 2005
    Posts
    183
    Just to follow up on my post. I ended up creating a small functon, that through recordset fetched all the information I needed in the cell.

    It slows down the transform on larger queries, but since the database in itself is a concept/prototype, Its of little concern to me.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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