Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
flag every 6th record
- Thread starter markbrum
- Start date
every 6th record
How are the record numbered ?
If you have a properly sequenced autonumber field named, say, ID:
UPDATE yourTable SET yourField=True WHERE (ID Mod 6)=0;
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
How are the record numbered ?
If you have a properly sequenced autonumber field named, say, ID:
UPDATE yourTable SET yourField=True WHERE (ID Mod 6)=0;
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
As PHV hints at, there is no such thing as 6th or 12th or 18th record unless the records are ordered. This requires a field that you can order by.
You haven't provided any field or table names or sample data.
If we don't know where you are and only have a vague idea of where you are going, we can't be of much help in getting you there.
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
You haven't provided any field or table names or sample data.
If we don't know where you are and only have a vague idea of where you are going, we can't be of much help in getting you there.
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
properly sequenced autonumber field"
Aye, theres the rub. What if rows have been deleted?
If this is a one-time-only operation, you could ensure the ID field has no missing values by appending the table to a new table which has the same columns plus one more autonumber column, TempID. The extra column will be filled as the rows are inserted and there wont be any missing values. Use the TempID column as PHV says to flag the rows.
Now you have an extra table which you can use henceforth, or you could UPDATE the new column in the original table based on a JOIN. Which also leads to the thought that all you need in the temporary table is a foreign key, that is, a column with the values from the primary key column in the original table.
Bet you are thinking why should something so easy be so difficult? Fundamentally, the rows in a relational database table do not have any order. The idea of every 6th record has no meaning in SQL.
Aye, theres the rub. What if rows have been deleted?
If this is a one-time-only operation, you could ensure the ID field has no missing values by appending the table to a new table which has the same columns plus one more autonumber column, TempID. The extra column will be filled as the rows are inserted and there wont be any missing values. Use the TempID column as PHV says to flag the rows.
Now you have an extra table which you can use henceforth, or you could UPDATE the new column in the original table based on a JOIN. Which also leads to the thought that all you need in the temporary table is a foreign key, that is, a column with the values from the primary key column in the original table.
Bet you are thinking why should something so easy be so difficult? Fundamentally, the rows in a relational database table do not have any order. The idea of every 6th record has no meaning in SQL.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question