Question Two simple Access Questions

[dfenton21]

Hi,

I've created a simple client/server application with an Access backend. Unfortunately, I'm currently restricted to using Access 97.

I have two questions. Lets say there are three fields, RecordID (Key), FirstNumber and SecondNumber.

1) I need a routine to add 1 to the FirstNumber of a specified record. I could first read the value to a variable using SELECT FirstNumber FROM Table1 WHERE RecordID=iRecord, then add 1 and then use UPDATE. Is there are more efficient way of doing this?

2) What is the most efficient way for performing simple math calculations. For example, if I have a fourth field called Difference, what is the best way to subtract SecondNumber from FirstNumber. I don't think I can use a stored procedure in Access 97. If it makes a difference, the table will contain 100s of records, but the number of records displayed in a DataGridView using a SQL expression would never be more than 30.

Thanks for you help,
Damien.

 

[ss7thirty]

1) I need a routine to add 1 to the FirstNumber of a specified record. I could first read the value to a variable using SELECT FirstNumber FROM Table1 WHERE RecordID=iRecord, then add 1 and then use UPDATE. Is there are more efficient way of doing this?

UPDATE table1 SET firstNumber = firstNumber + 1 WHERE recordid = 9999

2) What is the most efficient way for performing simple math calculations. For example, if I have a fourth field called Difference, what is the best way to subtract SecondNumber from FirstNumber. I don't think I can use a stored procedure in Access 97. If it makes a difference, the table will contain 100s of records, but the number of records displayed in a DataGridView using a SQL expression would never be more than 30.

You can do this in your select statement:

SELECT (firstNumber - lastNumber) as FieldName FROM table1 WHERE recordid = 9999