ejf28
08-23-2011, 06:32 AM
Greetings,
I am new to this site for this question but have used your forums many times in the past to find solutions so thank you for that initially. Anyways, the problem that I have is that I have created an Access database purely from SQL commands from Excel (JET). I have the table already made and have a primary key column named "ID". I understand that the primary key needs to be unique so I was trying to create a method that could fix duplicates. I don't have any information tied to the ID so they just need to be unique, the actual value doesn't really matter.
My reindexing procedure does the following so far:
-It goes through the list, index by index, and checks if there is a duplicate of that index.
-If there is a duplicate then it sets all duplicates = NULL and assigns one of them to have the index (keeping it unique)
-Any missing index (IE if the index 4 was missing, to avoild 12356) will be assigned the next numerical value as its index
After it goes through my indexing method I have the result of a nicely indexed table with some values having null index values (the ones that were duplicates from before). The last step is where I need help. I need to assign indexes to these null values to be the max current index + 1 and keep incrimenting them up one by one.
I am not sure how to do this using SQL since when using the UPDATE command, the WHERE clause will bring back all the null values, not just one making it hard to seperate them in order to incriment them one at a time. Does anyone have any suggestions or ways to write an SQL command to accomplish this? If you don't get what I am asking feel free to ask questions.
Thanks,
Eric
I am new to this site for this question but have used your forums many times in the past to find solutions so thank you for that initially. Anyways, the problem that I have is that I have created an Access database purely from SQL commands from Excel (JET). I have the table already made and have a primary key column named "ID". I understand that the primary key needs to be unique so I was trying to create a method that could fix duplicates. I don't have any information tied to the ID so they just need to be unique, the actual value doesn't really matter.
My reindexing procedure does the following so far:
-It goes through the list, index by index, and checks if there is a duplicate of that index.
-If there is a duplicate then it sets all duplicates = NULL and assigns one of them to have the index (keeping it unique)
-Any missing index (IE if the index 4 was missing, to avoild 12356) will be assigned the next numerical value as its index
After it goes through my indexing method I have the result of a nicely indexed table with some values having null index values (the ones that were duplicates from before). The last step is where I need help. I need to assign indexes to these null values to be the max current index + 1 and keep incrimenting them up one by one.
I am not sure how to do this using SQL since when using the UPDATE command, the WHERE clause will bring back all the null values, not just one making it hard to seperate them in order to incriment them one at a time. Does anyone have any suggestions or ways to write an SQL command to accomplish this? If you don't get what I am asking feel free to ask questions.
Thanks,
Eric