arrange the display format

[gadflyjs]

Dear all,

I have some raw data in the Table look like this:

SN Item
1001 A
1001 E
1001 F
1001 B
1002 A
1003 D
1005 F
1005 C
.... ...


Is there any way re-arrange them in Query look like below?

SN Item1 Item2 Item3 Item4 Item5
1001 A B E F
1002 A
1003 D
1005 C F
.... ... ...

I am not good in any programming, so if programming is needed, example would
be appreciated.

Thank you in advance!

G

 

[Allen Browne]

There are a few ways to handle this.

The simplest would be to use a crosstab query, though the output would be
different, e.g.:
SN A B C D E
1001 x x
1002 x
1003 x x

With code, it is possible to concatenate the values together into a string,
but this doesn't really give the columns (though it could be simulated with
a fixed-width font.) Code to use:
http://allenbrowne.com/func-concat.html

 

[gadflyjs]

The CONCATENATE is almost what I want, I can live with that. ;-P
Thank you very much!

 

[gadflyjs]

Dear Allen,

I tired but I can't get it work. maybe I should change something because it
keeps telling me that something the format is not correct (I use chinese
version office, so I don't know what excatly it said).
Ok, the two tables that contain the data are looks like the two at below:

TableA -

SN Name
1001 ABC
1002 DEF
1003 GHI
.... ...

and TableB -
SN Year Item
1001 2004-2005 A
1001 2005-2006 A
1001 2004-2005 F
1001 2004-2005 B
1002 2006-2007 A
1003 2004-2005 D
1005 2007-2008 F
1005 2007-2008 C
.... ...

So, I want to do query, and I use the SQL view to design the query, is it
like this?

SELECT TableA.Name, ConcatRelated("item","TableB","SCRN = " & [SCRN])
FROM TableA;

and if I want to display the "Year" of TableB too, what should I do?

Thank you!

G

 

[gadflyjs]

Ok, I can make it work now. But it repeats the records:

SN Items
1001 A, B, D, F
1001 A, B, D, F
1003 C
1005 C, D
1005 C, D
.... ...

What's wrong? The SQL:
SELECT tableA.SN, tableA.Name, ConcatRelated("item","tableB","tableB.SN =
""" & [tableA.SN] & """","SCRN")
FROM tableA INNER JOIN tableB ON tableA.SN=tableB.SN;

Dear Allen,

I tired but I can't get it work. maybe I should change something because it
keeps telling me that something the format is not correct (I use chinese
version office, so I don't know what excatly it said).
Ok, the two tables that contain the data are looks like the two at below:

TableA -

SN Name
1001 ABC
1002 DEF
1003 GHI
... ...

and TableB -
SN Year Item
1001 2004-2005 A
1001 2005-2006 A
1001 2004-2005 F
1001 2004-2005 B
1002 2006-2007 A
1003 2004-2005 D
1005 2007-2008 F
1005 2007-2008 C
... ...

So, I want to do query, and I use the SQL view to design the query, is it
like this?

SELECT TableA.Name, ConcatRelated("item","TableB","SCRN = " & [SCRN])
FROM TableA;

and if I want to display the "Year" of TableB too, what should I do?

Thank you!

G

The CONCATENATE is almost what I want, I can live with that. ;-P
Thank you very much!

 

[gadflyjs]

OKOK, my bad. problem solved.

Ok, I can make it work now. But it repeats the records:

SN Items
1001 A, B, D, F
1001 A, B, D, F
1003 C
1005 C, D
1005 C, D
... ...

What's wrong? The SQL:
SELECT tableA.SN, tableA.Name, ConcatRelated("item","tableB","tableB.SN =
""" & [tableA.SN] & """","SCRN")
FROM tableA INNER JOIN tableB ON tableA.SN=tableB.SN;

Dear Allen,

I tired but I can't get it work. maybe I should change something because it
keeps telling me that something the format is not correct (I use chinese
version office, so I don't know what excatly it said).
Ok, the two tables that contain the data are looks like the two at below:

TableA -

SN Name
1001 ABC
1002 DEF
1003 GHI
... ...

and TableB -
SN Year Item
1001 2004-2005 A
1001 2005-2006 A
1001 2004-2005 F
1001 2004-2005 B
1002 2006-2007 A
1003 2004-2005 D
1005 2007-2008 F
1005 2007-2008 C
... ...

So, I want to do query, and I use the SQL view to design the query, is it
like this?

SELECT TableA.Name, ConcatRelated("item","TableB","SCRN = " & [SCRN])
FROM TableA;

and if I want to display the "Year" of TableB too, what should I do?

Thank you!

G

The CONCATENATE is almost what I want, I can live with that. ;-P
Thank you very much!

:

There are a few ways to handle this.

The simplest would be to use a crosstab query, though the output would be
different, e.g.:
SN A B C D E
1001 x x
1002 x
1003 x x

With code, it is possible to concatenate the values together into a string,
but this doesn't really give the columns (though it could be simulated with
a fixed-width font.) Code to use:
http://allenbrowne.com/func-concat.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dear all,

I have some raw data in the Table look like this:

SN Item
1001 A
1001 E
1001 F
1001 B
1002 A
1003 D
1005 F
1005 C
... ...


Is there any way re-arrange them in Query look like below?

SN Item1 Item2 Item3 Item4 Item5
1001 A B E F
1002 A
1003 D
1005 C F
... ... ...

I am not good in any programming, so if programming is needed, example
would
be appreciated.

Thank you in advance!

G