I have a list of projects that are broken down by location and status. Every projects that has a status listed as "Active" has a number that is manually entered in. When a project becomes inactive it loses its number. When that happens I end up with gaps in the numbers. Is there a way to reset all the "active" project numbers starting at one?
Renumber column
- Thread starter sandylt
- Start date
- Local time
- Today, 14:25
- Joined
- Feb 19, 2002
- Messages
- 47,317
Reassigning the identifying number for a record is poor technique. What happens if you have to reactivate a project? What happens to the paper documents referring to the project number?
All projects will have a unique ID assigned by the autonumber function. The numbering system I would like to reorder will be a different field manually entered. Does anyone have any solutions? Would this be a table of forms question?
- Local time
- Today, 13:25
- Joined
- Feb 28, 2001
- Messages
- 30,330
Even if the number in question is not the prime key of the table, I would not renumber it as long as any other projects above it are still active. The implication is that if you have, say, 50 projects and number 1 goes dormant, you would have to renumber 49 other projects.
If this number is so ephemeral that it gets renumbered every time any other project changes status, and it isn't an autonumber to begin with, don't store it. Doing so would violate at least one normalization rule. (The rule that says a field in a table must depend only on the entire prime key.) This number does not depend on the prime key of only the current record. It depends on the prime keys of other records, too. It does not belong with other data regarding your project.
Compute it in a query by finding an expression such as
1 + DCount( "[ProjPK]", "ProjTable", "[ProjPK] < " & CStr$( [ProjPK] ) )
In other words, order the projects by prime key and this number you want is 1 + the number of projects with lesser prime keys. For the first project (lowest PK), the number is 1.
If this number is so ephemeral that it gets renumbered every time any other project changes status, and it isn't an autonumber to begin with, don't store it. Doing so would violate at least one normalization rule. (The rule that says a field in a table must depend only on the entire prime key.) This number does not depend on the prime key of only the current record. It depends on the prime keys of other records, too. It does not belong with other data regarding your project.
Compute it in a query by finding an expression such as
1 + DCount( "[ProjPK]", "ProjTable", "[ProjPK] < " & CStr$( [ProjPK] ) )
In other words, order the projects by prime key and this number you want is 1 + the number of projects with lesser prime keys. For the first project (lowest PK), the number is 1.
