Table, query or vba problem not sure!

[slade.justin]

Hi there

I have a problem that I just can't find a way to solve.

This is an example of what my table looks likethe differnt colors are for the differnt coloums)

Transaction no item fig 1 fig 2
1 car1 100
2 car2 240
3 car3 160
4 car1 150
5 car4 60
6 car2 270
7 car4 120
8 car3 240
9 car1 190
10 car4 140


What I need to do is create an automated process that will enter a fig into fig2, the fig is the next fig1 for the piticular item e.g.transaction 1 fig 2 = 150 . fig1 is entered into the database at the time of entry.

This is what it should look like when its done:

Transaction no item fig 1 fig 2
1 car1 100 150
2 car2 240 270
3 car3 160 240
4 car1 150 190
5 car4 60 120
6 car2 270
7 car4 120 140
8 car3 240
9 car1 190
10 car4 140

If anyone can help me with this situation I will really appreciate it

Regards
Justin

 

[rsmonkey]

does fig2 have an identifier with the other table? i.e. do you have a seperate table which has say

1 | 150
2 | 270
3 | 240

etc... if so this is very easy. If not then in order to automate this process you will have to find some say to have an identifier between the car and fig2. If you do not have this then its time to look at your database design books again me ol'china.

 

[slade.justin]

Hi there

Thanks for the post I really appreciate it.

This is all on one table because it is reletive to each record. But say I added another table with an identifer how would that help me?

Regards
Justin

 

[pbaldy]

Sounds like a standard previous/next value type of problem. You could use domain aggregate functions or subqueries to solve it. If the target value is always higher, you're simply looking for the smallest fig1 that bigger than the current entry (and item is equal to the current item).

 

[slade.justin]

Thanks Paul that really sounds like it could work! Is it all possible for you show some sample code on how I would do that.

Regards
Justin

 

[pbaldy]

Try

SELECT item, Fig1, (SELECT Min(Fig1) FROM TableName as Alias WHERE Alias.item = TableName.item and Alias.Fig1 > TableName.Fig1) AS Fig2
FROM TableName
ORDER BY Fig1