Composite Primary Key with an ID and Index

P

Pete

I would like to create a composite primary key using two fields: a random
integer ID, and a "line number" index. I want the index to be incremental,
start with 1, and reset for each unique ID from the first field. For example:

ID INDEX
5127 1
5127 2
5127 3
6435 1
9921 1
9921 2

Is this possible in Access?

The context is a project management database. Each project has a unique ID.
Projects and their descriptions are stored in one table. Progress updates
are stored in a second table. I would like to be able to incrementally
identify the updates to for each project.

Thanks in advance for any help!

--Pete
 
J

John W. Vinson

I would like to create a composite primary key using two fields: a random
integer ID, and a "line number" index. I want the index to be incremental,
start with 1, and reset for each unique ID from the first field. For example:

ID INDEX
5127 1
5127 2
5127 3
6435 1
9921 1
9921 2

Is this possible in Access?

The context is a project management database. Each project has a unique ID.
Projects and their descriptions are stored in one table. Progress updates
are stored in a second table. I would like to be able to incrementally
identify the updates to for each project.

Thanks in advance for any help!

--Pete

The simplest way to do this is in VBA code on a Form (bound to the projects
table) with a Subform (bound to the updates table). In the subform's
BeforeInsert event use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![INDEX] = NZ(DMax("[INDEX]", "ProgressTable", "[ID] = " & Me!ID)) + 1
Me.Dirty = False ' immediately write the record to disk
End Sub
 
P

Pete

I'll give it a try -- thank you very much!

John W. Vinson said:
I would like to create a composite primary key using two fields: a random
integer ID, and a "line number" index. I want the index to be incremental,
start with 1, and reset for each unique ID from the first field. For example:

ID INDEX
5127 1
5127 2
5127 3
6435 1
9921 1
9921 2

Is this possible in Access?

The context is a project management database. Each project has a unique ID.
Projects and their descriptions are stored in one table. Progress updates
are stored in a second table. I would like to be able to incrementally
identify the updates to for each project.

Thanks in advance for any help!

--Pete

The simplest way to do this is in VBA code on a Form (bound to the projects
table) with a Subform (bound to the updates table). In the subform's
BeforeInsert event use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![INDEX] = NZ(DMax("[INDEX]", "ProgressTable", "[ID] = " & Me!ID)) + 1
Me.Dirty = False ' immediately write the record to disk
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top