version control

[aliasquark]

I have a table in which values in a column [item] is duplicated and given a -x extention when updated. The table will look like this

[item]____[type]_____[other]
1________black______info for 1
1-b ______black______info for 1
1-c ______black______info for 1
2________white______info for 2
3________grey_______info for 3
3-b ______grey______info for 3
3-c______grey_______info for 3

what I would like is:
1) Original values (i.e. the ones without the -b,c,d etc) to be assigned xxxxx-a
2) For the version letter to be removed and placed into its own field [version]
3) For only the last occurance of the item to be used in the table

Resulting in
[item]____[version]____[type]_____[other]
1_________ c________black _____info for 1
2_________a_________white_____info for 2
3_________c_________grey _____info for 3

Mabey it is three seperate problems, I have managed to get each of the solutions seperatly but not together (i.e. I can assign the values that dont have a -b,c,d but then all other values are excluded).

Anyone know how to do this?

Q.

 

[Jon K]

Try this query:-

SELECT IIf(InStr(a.[Item],"-"), Left(a.[Item],InStr(a.[Item],"-")-1), a.[Item]) AS Item, Max(IIf(InStr(a.[Item],"-"), Right(a.[Item],1), "a")) AS Version, Last(a.[Type]) AS Type, Last(a.[Other]) AS Other
FROM [TableName] AS a
GROUP BY IIf(InStr([Item],"-"), Left([Item],InStr([Item],"-")-1), [Item]);