How to merge records 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

 

[ntc]

well you have two issues;

in terms of manipulating the text you can do a very simple calculated field
in your query something like:

NewNumber: [Numer1FieldName]&"-"&[Number2FieldName]

Then your second issue is wanting this sub data to go left to right rather
than stacked. You pay a price for wanting this appearance. It is not
normalized. So you would then probably need to set up a cross tab query to
make this happen.

How's your global warming in sweden these days??