multiple sequential numbering within one table

By cheri.freedman ·
I am not much of a programmer and I could use some help with this.

I am attempting to clean up a database. One of the things I would like to do is combine two tables - one contains "Internal" projects and the other contains "External" projects. They both have the exact fields and an identifier field that indicates "External" or "Internal". The two tables were seperated because they needed sequential numbering. Currently the numbers are in an autonumber field in each table.

What I need help with is writing code for when new records are created in the combined table. Basically, I need code that looks at the ProjectType field(Internal or External) then locates the last ProjectNumber that was used in combination with that project type.

So you could have Internal project number 1 and External project number 1. The very end result of this is having it show as "IP01" or "EX01" to the end user as a concatented field on a form. (This part I know how to do).

Can this be done? I really would like to combine these tables and clean up a bunch of unnessary queries related to joining the records from the two tables.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Not nice

by Tony Hopkinson In reply to multiple sequential numbe ...

The SQl would be for EX, you could parameterise this or duplicate this, wack it in a stored procedure...

Begin Transaction
Declare @NextNumber int
Select Max(ProjectNumber) + 1 from AllProjects where ProjectType = 'EX'
Insert AllProjects(ProjectType,ProjectNumber) Values('EX',@NextNumber)
Select * From AllProjects Where ProjectType = 'EX' and ProjectNumber = @NextNumber
Commit Transaction

The transaction is important, if two people hit create project at the same time, you'll get numbers skipped, primary violations and other sorts of nasty.

PS you could go lateral on this, given you can't get rid of the sequential project number.

Create your all jobs table, key it conveniently. Keep your current tables but bin all the columns except the identity. Then you can get the number out of those on new job but drive every thing else from the common table.

Cheating, but .....

Collapse -

Not Nice, Indeed

by cheri.freedman In reply to Not nice

Thanks, Tony. This is really helpful and it's given me an idea on how to push this back to mgmt to rethink the sequential numbering.

Related Discussions

Related Forums