Crosstab or not to crosstab (A2K)

Topic

New Reply

Is there a way to convert the top table in the image to the one on the bottom. Crosstab says you need more than 2 columns when I try to use that method.
I don’t necessarily need it in a table if I can just display it in a query.

Reply | Quote

Replies

The bottom image doesn’t correspond to something meaningful in Access. Queries have rows and columns (just like tables); a single row in a query represents data that belong together. The data in a row in your bottom image have nothing to do with each other. It might be possible to obtain it as a result of an extremely artificial method, but I don’t know how to do that at the moment.

Reply | Quote

The bottom image doesn’t correspond to something meaningful in Access. Queries have rows and columns (just like tables); a single row in a query represents data that belong together. The data in a row in your bottom image have nothing to do with each other. It might be possible to obtain it as a result of an extremely artificial method, but I don’t know how to do that at the moment.

Reply | Quote

You could force display of the data in the method you want if you had a separate query for each category and built a report for each query displaying only the models pertaining to that category. Next create a report containing each of the category reports as a subreport placing them side by side and inserting the title of each category above each subreport..

Reply | Quote

Thanks Hans– I see your point.
Judy,
That may work for what I want. I’ll give it a try

Reply | Quote

Thanks Hans– I see your point.
Judy,
That may work for what I want. I’ll give it a try

Reply | Quote

You could force display of the data in the method you want if you had a separate query for each category and built a report for each query displaying only the models pertaining to that category. Next create a report containing each of the category reports as a subreport placing them side by side and inserting the title of each category above each subreport..

Reply | Quote

My solution:

A query to sort the data by category, model and add a “Sort order” field,

SELECT Test.Category, Test.Model, 1 AS [Sort Order] INTO [Test 2]
FROM Test
GROUP BY Test.Category, Test.Model, 1
ORDER BY Test.Category, Test.Model;

A macro to create sort orders by category:

Function Index_test_2()
Dim db As Database
Dim rs As Recordset
Dim count_it As Integer
Dim Current_Category As String

Current_Category = “”
Set db = CurrentDb
Set rs = db.OpenRecordset(“Test 2”)

If rs.recordcount > 0 Then
rs.MoveFirst
While Not rs.EOF
If Current_Category rs![Category] Then
count_it = 0
Current_Category = rs![Category]
End If
count_it = count_it + 1
rs.Edit
rs![Sort Order] = count_it
rs.Update
rs.MoveNext
Wend
End If

End Function

Then crosstab the result on the sort order:

TRANSFORM Max([Test 2].Model) AS [The Value]
SELECT [Test 2].[Sort Order]
FROM [Test 2]
GROUP BY [Test 2].[Sort Order]
PIVOT [Test 2].Category;

Not perfect, but it works. This is Access97 code and queries.

Reply | Quote

JDN,
Clever and works perfectly
Thanks

Reply | Quote

JDN,
Clever and works perfectly
Thanks

Reply | Quote

My solution:

A query to sort the data by category, model and add a “Sort order” field,

SELECT Test.Category, Test.Model, 1 AS [Sort Order] INTO [Test 2]
FROM Test
GROUP BY Test.Category, Test.Model, 1
ORDER BY Test.Category, Test.Model;

A macro to create sort orders by category:

Function Index_test_2()
Dim db As Database
Dim rs As Recordset
Dim count_it As Integer
Dim Current_Category As String

Current_Category = “”
Set db = CurrentDb
Set rs = db.OpenRecordset(“Test 2”)

If rs.recordcount > 0 Then
rs.MoveFirst
While Not rs.EOF
If Current_Category rs![Category] Then
count_it = 0
Current_Category = rs![Category]
End If
count_it = count_it + 1
rs.Edit
rs![Sort Order] = count_it
rs.Update
rs.MoveNext
Wend
End If

End Function

Then crosstab the result on the sort order:

TRANSFORM Max([Test 2].Model) AS [The Value]
SELECT [Test 2].[Sort Order]
FROM [Test 2]
GROUP BY [Test 2].[Sort Order]
PIVOT [Test 2].Category;

Not perfect, but it works. This is Access97 code and queries.

Reply | Quote