Swapping numbers.

[kained]

I am trying to help a friend make a database(and ultimately piece of none commercial software) to determine personality type as part of his thesis.

For the formula to work properly a couple of the questions need their values inverted(ie if the person answers 1 the recorded number will have to be 7 if 2 then 6 etc etc) he wants this to be automated.(or press button/macro/querey and it works it all out)

I'm guessing to do this i will have to set up a macro which reads the question number and then does some sort of IF loop(or else if) and then records the answers in a separate table.(along with other bits of data but i'm sure i can work that out)

Does this sound like the best way of handling this problem and if it is could i be shown the correct syntax for swapping a value of 1 with a value of 7.

I'm a bit stuck and any pointing in the right direction would be most useful, especially in regard to the swapping aspect as I can't find anything using google or my sQL/access book. Even the correct term for searching would be handy!

dave

[jbisono]

well i maybe not following you correctly but maybe you just have to substract 8 - whatever answer was example if you select 1 then will be 8 -1 = 7 and so on.

regards.

[kained]

well i maybe not following you correctly but maybe you just have to substract 8 - whatever answer was example if you select 1 then will be 8 -1 = 7 and so on.

regards.

Hmmmm okay that might actually work subtract the answer typed in from 8 to get the new answer. Hadn't thought of it like that.

Then store this new answer in a new table. I take it i'm right that a macro will be needed to be written to achieve this? It isn't something i have done before.

dave

[samaru]

If you're not worried about performance, best way to do this would be to write using a function, in case your logic changes for this "inversion" - you'll change the logic from one central location.

-- Create the Function
CREATE FUNCTION InvertValue ( @value SMALLINT )
RETURNS SMALLINT
AS
BEGIN
	
  DECLARE @maxNumber SMALLINT
  SET @maxNumber = 8
	
  RETURN @maxNumber - @value
	
END

-- Ways to use it:

-- Print It Out
PRINT dbo.InvertValue( 2 )

-- As SELECT statement
SELECT dbo.InvertValue( 2 )

-- Insert into a Table
INSERT INTO TempTable( magic ) VALUES ( dbo.InvertValue( 2 ) )