There Can Be Only One!

Need an SQL statement (or similar Microsoft Access trick) which queries a table and only produces the first occurence of a field called item and puts info of the next occurance(s) into a new field called info(x)

item___info
1_____1111
2_____2222
2_____222a
2_____n/a
3_____bla bla bla
3_____brother
3_____sister

will become:

item___info_____info2_____info3
1_____1111____ _____
2_____2222____222a_____
3___bla bla bla___brother___sister

I have been able to produce the fields info(x) and populate them appropriatly, however using item=3 in the example above I would actually get 3 records for when item=3 (one for each info(x) value)... so it would look like this

item___info_____info2_____info3
1_____1111____ _____
2_____2222____222a_____
2_____2222____222a_____
3___bla bla bla___brother___sister
3___bla bla bla___brother___sister
3___bla bla bla___brother___sister

In any case the statement I used for this was very very messy and I'm sure there should be a way to incorporate both the population of info(x) as well as the exclusion of any duplicated value whilst leaving the first occurance of that value in one SQL statement.

For the data I am using there will be a maximum of 5 possible info fields.

Hopefully the problem is clear enough, and the solution is easy enough.

Thanks in advance.
Q.

I can probably do that in code, but in Sql.. hmm not sure.

If you are using Sql server, you might be able to implement a procedure on it? where the server does all the manouvering for you into the correct places.


Vince

unfortunatly i don't have access to edit anything on the server... just read only access.

I import the relevant tables from the SQL server as a dynamic link and play wiht them in Access, thus anything before retreival of the tables I am unable to change!

So it has to be done in SQL unfortunatly.

any suggestions?

You import from Sql Server to access... so use code in access to format a report table the way you want in code.

Should be able to achieve what you need. :)


Vince