Pivot Query in MS Access

by

Consider:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND Cat<'" & [Cat] & "'")+1;

Or if there is a unique record identifier field – autonumber should serve:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND ID_PK<" & [ID_PK])+1;

More Related Contents:

  1. Combine values from related rows into a single concatenated string value
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. How to get random record from MS Access database
  5. create form to add records in multiple tables
  6. Operation must use an updatable query. (Error 3073) Microsoft Access
  7. Pivoting data in MS Access
  8. How do I force VBA/Access to require variables to be defined?
  9. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  10. Running Microsoft Access as a Scheduled Task
  11. How to restart counting from 1 after erasing table in MS Access?
  12. Equivalent cURL in VBA?
  13. MS Access trigger?
  14. Insert SQL command with Datetime in MS-Access
  15. Is MS Access (JET) suitable for multiuser access?
  16. VBA procedure to import csv file into access
  17. How to select top 10 in Access query?
  18. ConcatRelated() in Access, using two keys
  19. Concatenating multiple rows into single line in MS Access [duplicate]
  20. Transform multiple rows into columns with unique key
  21. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  22. Access VBA: Find max number in column and add 1
  23. Access 2007 – INSERT and instant SELECT doesn’t retrieve the inserted data
  24. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  25. Custom row source for combo box in continuous form in Access
  26. VBA – Run Time Error 3271 using DAO object
  27. Working with multiple programmers on MS Access
  28. Trouble with InputBoxes
  29. Get Name of Current VBA Function
  30. How to extract the schema of an Access (.mdb) database?

Leave a Comment