Presenting data the right way 1

[BrianWen]

I have a table that looks like this:

id country region level
1 England uk 1
2 Wales uk 3
3 Ireland uk 1
4 Italy seu 2
5 France neu 1
6 Belgium neu 3

(those are just some made sample data, but that is the way, the correct data are stored. Exactly 3 levels, but with 6 different regions)

I'd like to represent that in a form with cells, with a cell for each combination of region and level:

1 2 3
uk C1 C2 C3
seu C4 C5 C6
neu C7 C8 C9

So in cell 1 (C1) I like to list the countries in uk with level 1 and like that with all the other cells to. I can do that with a pivottable, but as I see it, formatting such a table is extremely limited, and it's going to be a part of a nicely formatted form 'application'. Is there any way to produce some sort of query (crosstab, maybe) or the like that I could use to tackle this problem?

 

[dhookom]

Brian,
Why not post actual values instead of C1, C2, etc? Also, you didn't provide a table or query name.

I would use the generic concatenate function faq701-4233 in a crosstab with a SQL view of:

TRANSFORM First(Concatenate("SELECT Country FROM TTBrianWen TT WHERE TT.Region = """ & [region] & """ and TT.Level = " & [Level])) AS Expr1
SELECT TTBrianWen.Region
FROM TTBrianWen
GROUP BY TTBrianWen.Region
PIVOT "Level" & [Level] In ("LEVEL1","LEVEL2","LEVEL3");

This should result in:
[tt][blue]
Region LEVEL1 LEVEL2 LEVEL3
neu France Belgium
seu Italy
uk England, Ireland Wales
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[BrianWen]

Thanks for the answer. The C1, C2 etc. would mean Cell 1 and so on. It was to illustrate that the countries were split into cells. I could have provided you a table name, that's right.

I tried this solution and it seems to work, but it's fairly slow, compared to what I have come up with after creating this thread. I just made a listbox for all combinations of region and level and pulled the appropriate countries out for each. After formatting the boxes it looks fine and loads in about one third of the time for the concatenation query.

But thanks anyway, your suggested solution might come in handy later in my project.

 

[dhookom]

The performance will depend on indexes and table sizes. The function could be optimized by not creating the database object at each call.

Duane
Hook'D on Access
MS Access MVP