By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

Unique Index

P: n/a
I have a table listing drawing numbers for jobs. It's primary key combines
Job and numeric part of the drawing number. The structure allows the number
part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet to
create an index on job / dwg to allow the drawing field to be unique for a
job? I would like to do it at the table level instead of the form.
Aug 14 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
paii, Ron wrote:
I have a table listing drawing numbers for jobs. It's primary key
combines Job and numeric part of the drawing number. The structure
allows the number part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet to
create an index on job / dwg to allow the drawing field to be unique
for a job? I would like to do it at the table level instead of the
form.
No, not if you want the Dwg number to start over per Job. You could assign
that number in the code event of a form though. I would use the
BeforeUpdate event.

If Me.NewRecord Then
Me.Dwg = Nz(DMax("Dwg", "TableName", "Job = " & Me!Job), 0) + 1
End If

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Aug 14 '06 #2

P: n/a

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:rL******************@newssvr11.news.prodigy.c om...
paii, Ron wrote:
I have a table listing drawing numbers for jobs. It's primary key
combines Job and numeric part of the drawing number. The structure
allows the number part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet to
create an index on job / dwg to allow the drawing field to be unique
for a job? I would like to do it at the table level instead of the
form.

No, not if you want the Dwg number to start over per Job. You could
assign
that number in the code event of a form though. I would use the
BeforeUpdate event.

If Me.NewRecord Then
Me.Dwg = Nz(DMax("Dwg", "TableName", "Job = " & Me!Job), 0) + 1
End If

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

That is what I thought, using the Access GUI. I was hoping for a SQL command
that would create the index.

Thank for your reply.
Aug 14 '06 #3

P: n/a
paii, Ron wrote:
I have a table listing drawing numbers for jobs. It's primary key combines
Job and numeric part of the drawing number. The structure allows the number
part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet to
create an index on job / dwg to allow the drawing field to be unique for a
job? I would like to do it at the table level instead of the form.

Not sure if I'm missing your intent, but you can check out my recent
reply regarding multi-column indexes here:

http://tinyurl.com/sxrok

Please don't fail to read Arvin Meyer's response to same.

Also, consider that your table structure is not normalized. If "jobs"
have "drawings" then this is a hint that these entities should be in
separate tables.

--
Smartin
Aug 15 '06 #4

P: n/a
"paii, Ron" <pa**@packairinc.comwrote in message
<Wv******************************@athenet.net>:
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:rL******************@newssvr11.news.prodigy.c om...
>paii, Ron wrote:
>>I have a table listing drawing numbers for jobs. It's primary key
combines Job and numeric part of the drawing number. The structure
allows the number part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet
to create an index on job / dwg to allow the drawing field to be
unique for a job? I would like to do it at the table level instead
of the form.

No, not if you want the Dwg number to start over per Job. You could
assign that number in the code event of a form though. I would use
the BeforeUpdate event.

If Me.NewRecord Then
Me.Dwg = Nz(DMax("Dwg", "TableName", "Job = " & Me!Job), 0) + 1
End If

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


That is what I thought, using the Access GUI. I was hoping for a SQL
command that would create the index.

Thank for your reply.
I think you can create the index through DDL, but that's the means of
ensuring data integrity, not how to populate it. So, if the two fields
are the current compound primary key, and you wish those to become a
compound index and add an autonumber field to the table to be primary
key, I think something like the following air code might perhaps be of
assistance

dim db as dao.database
set db = dbengine(0)(0)
db.execute "DROP INDEX PrimaryKey ON table1"
' PrimaryKey - the name of the primary key index
db.execute "ALTER TABLE table1 ADD COLUMN id Counter"
db.execute "CREATE INDEX PrimaryKey ON table1 (id) WITH PRIMARY"
db.execute "CREATE UNIQUE INDEX idxJobDwg ON table1 (Job, Dwg) " & _
"WITH DISALLOW NULL"
set db = nothing

--
Roy-Vidar
Aug 15 '06 #5

P: n/a

"Smartin" <sm********@yahoo.comwrote in message
news:Qb******************************@giganews.com ...
paii, Ron wrote:
I have a table listing drawing numbers for jobs. It's primary key
combines
Job and numeric part of the drawing number. The structure allows the
number
part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet to
create an index on job / dwg to allow the drawing field to be unique for
a
job? I would like to do it at the table level instead of the form.

Not sure if I'm missing your intent, but you can check out my recent
reply regarding multi-column indexes here:

http://tinyurl.com/sxrok

Please don't fail to read Arvin Meyer's response to same.

Also, consider that your table structure is not normalized. If "jobs"
have "drawings" then this is a hint that these entities should be in
separate tables.

--
Smartin
Job is a Foreign Key in this table, and primary in the "Jobs" table.
Combining [Job], [Dwg] and one other field creates a file name. I broke it
up to allow quick lookup of job drawings. And used [Job],[Dwg] as the
primary key to keep users from duplicating the [Dwg] part when creating new
drawing files, for the job. If I add a 3rd field to the primary index, [Dwg]
is no longer guaranteed unique unless I add code to the form.
Aug 15 '06 #6

P: n/a

"RoyVidar" <ro*************@yahoo.nowrote in message
news:mn***********************@yahoo.no...
"paii, Ron" <pa**@packairinc.comwrote in message
<Wv******************************@athenet.net>:
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:rL******************@newssvr11.news.prodigy.c om...
paii, Ron wrote:
I have a table listing drawing numbers for jobs. It's primary key
combines Job and numeric part of the drawing number. The structure
allows the number part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet
to create an index on job / dwg to allow the drawing field to be
unique for a job? I would like to do it at the table level instead
of the form.

No, not if you want the Dwg number to start over per Job. You could
assign that number in the code event of a form though. I would use
the BeforeUpdate event.

If Me.NewRecord Then
Me.Dwg = Nz(DMax("Dwg", "TableName", "Job = " & Me!Job), 0) + 1
End If

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

That is what I thought, using the Access GUI. I was hoping for a SQL
command that would create the index.

Thank for your reply.

I think you can create the index through DDL, but that's the means of
ensuring data integrity, not how to populate it. So, if the two fields
are the current compound primary key, and you wish those to become a
compound index and add an autonumber field to the table to be primary
key, I think something like the following air code might perhaps be of
assistance

dim db as dao.database
set db = dbengine(0)(0)
db.execute "DROP INDEX PrimaryKey ON table1"
' PrimaryKey - the name of the primary key index
db.execute "ALTER TABLE table1 ADD COLUMN id Counter"
db.execute "CREATE INDEX PrimaryKey ON table1 (id) WITH PRIMARY"
db.execute "CREATE UNIQUE INDEX idxJobDwg ON table1 (Job, Dwg) " & _
"WITH DISALLOW NULL"
set db = nothing

--
Roy-Vidar

I will give that code a try. Will the new key show in the GUI or will I need
to document.
Aug 15 '06 #7

P: n/a

"RoyVidar" <ro*************@yahoo.nowrote in message
news:mn***********************@yahoo.no...
"paii, Ron" <pa**@packairinc.comwrote in message
<Wv******************************@athenet.net>:
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:rL******************@newssvr11.news.prodigy.c om...
paii, Ron wrote:
I have a table listing drawing numbers for jobs. It's primary key
combines Job and numeric part of the drawing number. The structure
allows the number part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet
to create an index on job / dwg to allow the drawing field to be
unique for a job? I would like to do it at the table level instead
of the form.

No, not if you want the Dwg number to start over per Job. You could
assign that number in the code event of a form though. I would use
the BeforeUpdate event.

If Me.NewRecord Then
Me.Dwg = Nz(DMax("Dwg", "TableName", "Job = " & Me!Job), 0) + 1
End If

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

That is what I thought, using the Access GUI. I was hoping for a SQL
command that would create the index.

Thank for your reply.

I think you can create the index through DDL, but that's the means of
ensuring data integrity, not how to populate it. So, if the two fields
are the current compound primary key, and you wish those to become a
compound index and add an autonumber field to the table to be primary
key, I think something like the following air code might perhaps be of
assistance

dim db as dao.database
set db = dbengine(0)(0)
db.execute "DROP INDEX PrimaryKey ON table1"
' PrimaryKey - the name of the primary key index
db.execute "ALTER TABLE table1 ADD COLUMN id Counter"
db.execute "CREATE INDEX PrimaryKey ON table1 (id) WITH PRIMARY"
db.execute "CREATE UNIQUE INDEX idxJobDwg ON table1 (Job, Dwg) " & _
"WITH DISALLOW NULL"
set db = nothing

--
Roy-Vidar

Thank you, It work perfect. It was doable in the GUI, once I saw what your
code created.
Aug 15 '06 #8

P: n/a

"Smartin" <sm********@yahoo.comwrote in message
news:Qb******************************@giganews.com ...
paii, Ron wrote:
I have a table listing drawing numbers for jobs. It's primary key
combines
Job and numeric part of the drawing number. The structure allows the
number
part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet to
create an index on job / dwg to allow the drawing field to be unique for
a
job? I would like to do it at the table level instead of the form.

Not sure if I'm missing your intent, but you can check out my recent
reply regarding multi-column indexes here:

http://tinyurl.com/sxrok

Please don't fail to read Arvin Meyer's response to same.

Also, consider that your table structure is not normalized. If "jobs"
have "drawings" then this is a hint that these entities should be in
separate tables.

--
Smartin
I reread your post and applied the code from RoyVidar. It turns out I was
trying to do as you recommended, but until today did not know how to create
a unique index on multiple fields that were not a primary key. The original
table structure work ok until I needed to reference it in another
application, where the multiple field user edited primary key would cause
problems.

Thank you for your help.
Aug 15 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.