aliasquark
Registered User.
- Local time
- Today, 09:58
- Joined
- Dec 2, 2002
- Messages
- 14
There can be only one! **RESOLVED**
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.
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.
Last edited: