Organize Data Like a Cross Tab, without unneccessary Blanks

[CMA]

I'm looking for a unique way to return data with a query.

Using the example below - Right now, in a relational database, numbers and
letters are in two separate, linked tables.

I'd like to move from this format:

1 AA
1 BB
2 CC
3 DD
3 EE
3 FF
4 GG
4 HH
5 II


To this format:

1 AA BB
2 CC
3 DD EE FF
4 GG HH
5 II

Is there a way to acheive this? my data has about 10,000 records - I've hit
a wall in an attempt to do this using a pivot table...

 

[Jeanette Cunningham]

Hi,

With 2 queries, I have set up the tables as shown below

tblNumbers ....
NbrID >>Primary Key
ObjNumber >> Long Integer )the nos from 1 - 5


tblLetters ....
LetterID >>Primary Key
NbrID >> Foreign Key from tblNumbers
Letter >> the letters from AA - end

1st query creates the temp table called tblTemp
SELECT DISTINCT NbrID, iif(false, "", null) AS Concat INTO tblTemp
FROM tblNumbers;

2nd query concatenates the letters into tblTemp
UPDATE tblTemp INNER JOIN tblLetters ON tblTemp.NbrID= tblLetters.NbrID SET
tblTemp.concat = ( tblTemp.concat + ", " ) & tblLetters.Letter;

Open tblTemp and find the result you wanted

Once tblTemp has been created and filled with the results, you must delete
tblTemp in order to run both queries again.

Jeanette Cunningham

 

[Marshall Barton]

I'm looking for a unique way to return data with a query.

Using the example below - Right now, in a relational database, numbers and
letters are in two separate, linked tables.

I'd like to move from this format:

1 AA
1 BB
2 CC
3 DD
3 EE
3 FF
4 GG
4 HH
5 II


To this format:

1 AA BB
2 CC
3 DD EE FF
4 GG HH
5 II

You need to create a function to do that.

I like the one at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[John Spencer]

One method that might work IF the values in column 2 don't repeat for the
values in column 1 would be to use a ranking query as the source of the
crosstab.

UNTESTED SQL statements follow

The base query would be something like
SELECT NumColumn
, LetterColumn
, (SELECT Count(*)
FROM TheTable as Tmp
WHERE Tmp.NumColumn = TheTable.NumColumn
AND Tmp.LetterColumn < TheTable.LetterColumn) +1 as Rank
FROM TheTable

The crosstab would look like

TRANSFORM FIRST(LetterColumn) as F
SELECT NumColumn
FROM RankingQuery
GROUP BY NumColumn
PIVOT Rank

Make sure you have indexes on NumColumn and LetterColumn as this is going to
be slow (if it works) even with the indexes.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..