MS-Access: SQL for updating a column in a table

Question

Hi,

I have the following table in an access database

id  VisitNo     Weight      
1   1           100
1   2           95
1   3           96
1   4           94
1   5           93

Now row 2 and 4 are deleted. So i have...

id  VisitNo     Weight      
1   1           100
1   3           96
1   5           93

However what i need is...

id  VisitNo     Weight      
1   1           100
1   2           96
1   3           93

What is the SQL query i need to accomplish the above?

thanks

Answer 1

You shouldn't really "re-organise" a key in your database. If you are using the database in a relational way, you may reference the VisitNo from another table as a way of linking the weight information to some other information you hold from the visit. This link is called a relationship and the ID is referred to as a primary key, which should never change for a row in your table as it would break any references in other tables (these references are called foreign-keys).

While it looks nice and "tidy" to have to breaks in your VisitNo sequence, it isn't really the correct process to follow when you delete records.

However, no this lesson is out of the way, here is how you would change the VisitNo, assuming it isn't a key at all, but a piece of user data (i.e. the first time I visited the gym, the second time I visited the gym and the third time I visited the gym)

CREATE TABLE #RowNumber (
  RowNumber int IDENTITY (1, 1),
  id INT,
  Weight INT  )

INSERT #RowNumber (emp_id)
SELECT id, Weight
FROM tblMyTable
WHERE id = 1
ORDER BY VisitNo

SELECT * FROM #RowNumber

DROP TABLE #RowNumber

If you run this query (replace tblMyTable with the name of your table) you'll see that the RowNumber column is essentially the re-numbered VisitNo. You can use this data to throw back at your table... although there is one slight problem with your table structure in this respect. You need a primary key on the table to give you a nice unique handle to each row:

recordid    id  VisitNo     Weight      
1           1   1           100
2           1   2           95
3           1   3           96
4           1   4           94
5           1   5           93

Without this, you'll have trouble updating the new VisitNo back against the table, which will look like this:

recordid    id  VisitNo     Weight      
1           1   1           100
3           1   3           96
5           1   5           93

And after you change it, it would look like this:

recordid    id  VisitNo     Weight      
1           1   1           100
3           1   2           96
5           1   3           93