Display Access Report Listbox One Field into 3 Columns

Question

I'm currently stuck on trying to format a listbox that contains a lot of values (9) from one column that is 9 values long to a listbox that contains 3 columns, thereby shortening the length of the listbox. The information is just for display purposes within a report.

What I Currently Have:

Field 1
[Entry 1]
[Entry 2]
[Entry 3]
[Entry 4]
[Entry 5]
[Entry 6]
[Entry 7]
[Entry 8]
[Entry 9]

What I would like, all still the same field:

[Entry 1]                     [Entry 2]                       [Entry 3]
[Entry 4]                     [Entry 5]                       [Entry 6]
[Entry 7]                     [Entry 8]                       [Entry 9]

I've tried a few techniques to make this happen, but nothing works nicely. I just want to be able to display customer names on a report without the listbox being super long.

Any help would be greatly appreciated

Thanks, Kev

Answer 1

This works for exactly 9 records. Requires a unique identifier field. Assumes the ID is sequential 1 to 9.

TRANSFORM Max(Table5.Field1) AS MaxOfField1
SELECT Switch([ID]<4,"a",[ID]<7,"b",[ID]<10,"c") AS Row
FROM Table5
GROUP BY Switch([ID]<4,"a",[ID]<7,"b",[ID]<10,"c")
PIVOT Choose([ID] Mod 3+1,3,1,2);

If you want to allow for unlimited records, expect VBA will be needed. Either a function called by query or writing records to a temp table, most likely the latter.