Coding Problem please help!!

[2461]

Hi..

I have 2 tables. 1st table with codes and values. 2nd table with codes.

Example of what I am trying to do.

1st table

[BodySys] [PrimCode] [Primval] [SubCode] [SubVal]
BL SCC 2
BL SCC U 1
BL SCC N 1.5



2nd table

[BodySys] [Clin1] [Clin1sub] [SIscore]
BL SCC U ?

ok..i hope u now have a clear idea of my tables.

Now what I need to do is work out the SIscore field.

I see that it says BL in the bodysys, so I lookup BL in 1st table. Found! then I look at Clin1 field and its says SCC(relating to primcode in the 1st table). So now lookup BL and SCC in 1st table. it says BL........SCC.......= 2 (from primval in 1st table).

Now we have 2 as a result, BUT...if we look under Clin1sub it says U. So this means going back to the 1st table and looking for BL......SCC.......U....= 1 (from subVal in 1st table). This = 1.

So this means: 2 + 1 = 3(SIscore field result).

I need help in coding this. Please help, as I am really struck and feel abit lost in the coding area. Do I use SQL or VB and what would be the code for a task like this.

You help is appreicated. Thankyou in advance.

Ruby

 

[MichaelRed]

Since there are a few 'grey' areas, I will not attempt an actual soloution, but give some thoughts.

Create two queries, each of which would return either the "Score" listed in the table ([Primval] or [secval] or Zero. Add the two.

It would APPEAR that your concept is that Primval would always exist, so -IF [BodySys] [PrimCode] [SubCode] always represent a unique set, you can just look up this combo and retrieve [Primval] for the specific value. Of course, the lookup for [primval] needs to specify #Null# for the [SubCode], and the "U" for the retrieval of the [Subval].

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[2461]

Thankyou micheal for getting back to me.

I can look at the 3 columns as a combination, as you said.
So...

Score table

[BodySys] [PrimCode] [SubCode] [Primval] [SubVal]
BL SCC U 2 1
BL SCC N 2 1.5

Result Table

[BodySys] [Clin1] [clin1sub] [SIscore]
BL SCC ?
BL SCC U ?


so SIscore field would lookup the [BodySys] and [Clin1] in the score table under [BodySys] and [PrimCode] and [Primval]. The lookup finds the [BodySys] and [Clin1] = [BodySys] and [PrimCode] giving the [PrimVal] value. Which = 2.


I kind of understand what you are saying. But how would I code this as I am finding it difficult and where would the code go. Please help.....

sorry..if im acting abit stupid in this case...but i feel abit lost on how to go about coding this.

Thankyou in advance

ruby

 

[MichaelRed]

Just do two seperate queries. Each (properly setup) will return either 0 (if the results are "Null&quot or the value in their respective lookup. Add their "lookups".

Generate each query (probably w/ parameters). Instantiate each in a nodule. Set a var = to the sum of the lookup fields.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over