How to merge datarecords for each unique fieldvalue in a 2nd field

[Mikael Lindqvist]

Hi,

I'm attempting to create a query that I can use for an Access report.

The table that I'm querying has three fields, AccNo, ProjectId_first and
ProjectId_last (this is straight from the database and not something I have
control over).

Example:
4060 | 12 | 15
4060 | 17| 17
4060 | 18 | 23
5010 | 10 | 25
5010 | 12 | 25
6010 | 9 | 10

Now, I want a query that merge ALL the 2nd and 3rd field for each AccNo (1st
field), like this:

4060 | 12-15, 17-17, 18-23
5010 | 10-25, 12-25
6010 | 9-10

Any advice MUCH appreciated!

Kindly,
Mikael
Sweden

 

[ghetto_banjo]

I believe the only way to display data like that horizontally, you
will need to use a crosstab query. Use your original query as the
datasource of the crosstab query, and concatenate the projectid_first
and projectid_last as a single string in the query and use that as a
Column Header in the crosstab.

Some of the gurus here can explain how to setup the crosstab in
greater detail, but I believe the above should get you headed in the
right direction.

 

[Gina Whipp]

Mikael,

Duane Hookom has a function that might work for your needs...

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm