unique ID across db

[Grip]

I'm building a database and want to have a couple tables (Notes,
Files) that relate to multiple other tables. I would like to use a
UUID for this.

Is the Replication ID the right tool for this? If not how do I create
an ID field that is unique across the database?

Thanks,
G

 

[CONRAD BOBB]

I'm building a database and want to have a couple tables (Notes,
Files) that relate to multiple other tables. I would like to use a
UUID for this.

Is the Replication ID the right tool for this? If not how do I create
an ID field that is unique across the database?

Thanks,
G

 

[KARL DEWEY]

If you are going to have a one-to-many relationship between the tables then
use this method --
In the main table use a field that is Autonumber as it will be unique. in
related table use a number - long integer to match DataType.
Open the relationship window and select the tables to relate. Click on the
Autonumber field of the main tabled and drag to number field of a related
(many side) table. Select Referential Integerity (means that must match) and
Cascade Update (automatically uses a number matching main when adding a
record).

Then you use forms to input and view data. The main table in the main form
and related table-form in subform. When creating them set the Master/Child
links using the unique field (primary key) and the related field (foreign
key).

When you scroll through the main form records the subform will display the
related table records matching that displayed on the main.

 

[KARL DEWEY]

I forgot to add that you need to click on the gold key icon to make the
autonumber field a primary key field.

 

[Grip]

If you are going to have a one-to-many relationship between the tables then
use this method --
In the main table use a field that is Autonumber as it will be unique.  in
related table use a number - long integer to match DataType.  
Open the relationship window and select the tables to relate.   Click on the
Autonumber field of the main tabled and drag to number field of a related
(many side) table.  Select Referential Integerity (means that must match) and
Cascade Update (automatically uses a number matching main when adding a
record).

Then you use forms to input and view data.  The main table in the main form
and related table-form in subform.  When creating them set the Master/Child
links using the unique field (primary key) and the related field (foreign
key).

Thanks for the reply, but that's not the help I'm looking for.

Here's what I am trying to do...

I have a table for Students. I have a table for Classes. I have a
table for Class Registrations. I want to keep track of Notes for each
of those entities. The Notes are all the same, Notes field, Created
Timestamp, UserID. Instead of adding three Notes tables (Class Notes,
Student Notes, etc), I want a single Notes table. That table will
have a field ParentID that relates it to Classes or Students or
Registrations.

How do I relate that Notes table to each of the three other parent
tables without a key conflict? I need to define a key for each of the
three parents that will be unique across them. Otherwise the Note for
Jane Smith (auto increment ID 17) will show up for Class Physics (also
auto increment ID 17).

Make sense?

G

 

[Klatuu]

Karl actuall gave you the answer, but let's take it one step further.

For starters, if you have students and classes, the business rules would be:

One Student may attend Zero to many Classes.
One Class will have One to many Students.

This is a classic many to many relationship and must be resolved. It is
done by using a Junction table. Assume the Student table has an Autonumber
primary key named StudentID and the Class table has a primary Autonumber key
named ClassID.

Your Junction table will then need two fields to associate the two.
Typically, I use the same name for a Foreign key as I do for a primary key.
so it would be:

StudentID - Long Integer
ClassID - Long Integer

That is the basics of the way it is done. There is no such thing as a
"universal key" that will link anything to anything.

 

[Grip]

Karl actuall gave you the answer, but let's take it one step further.

For starters, if you have students and classes, the business rules would be:

One Student may attend Zero to many Classes.
One Class will have One to many Students.

This is a classic many to many relationship and must be resolved.  It is
done by using a Junction table.  Assume the Student table has an Autonumber
primary key named StudentID and the Class table has a primary Autonumber key
named ClassID.

Your Junction table will then need two fields to associate the two.  
Typically, I use the same name for a Foreign key as I do for a primary key.
so it would be:

StudentID - Long Integer
ClassID - Long Integer

That is the basics of the way it is done.  There is no such thing as a
"universal key" that will link anything to anything.

I can't help but feel you didn't actually read my post.

I am not asking about how to relate Students, Classes, and
Registrations. I am quite comfortable with that. I am asking: How to
relate a single Notes table to three other tables?

Can you answer that question and no other?

 

[Jeff Boyce]

Folks here volunteer their assistance. Your response isn't likely to
encourage them (or others) to continue working with you on this.

You appear to have already settled on an approach. Are you open to other
ideas about how to accomplish what it sounds like you want to, i.e., have a
way to know which form a "note" came from? Notice that I said "form", not
"table".

By the way, if you are working directly in the tables, consider stepping
back. Access tables store data, Access forms display it. Access tables may
look like spreadsheets, but treating them like spreadsheets is a very good
way to end up with very bad data.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP


I can't help but feel you didn't actually read my post.

I am not asking about how to relate Students, Classes, and
Registrations. I am quite comfortable with that. I am asking: How to
relate a single Notes table to three other tables?

Can you answer that question and no other?

 

[Gina Whipp]

Grip,

I READ your post and am happy to report the advice you got was right on.
You, perhaps, are misunderstanding Relational Database Design and that must
be where the confusion is setting in. Do not critisize the VOLUNTEERS for
your misunderstanding of database design and TRYING to point you in the
right direction.

So to answer your question quickly and consisely... Nope, can't be done.

Have a nice day!
Gina Whipp

P.S. If you are interested...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


http://www.databasedev.co.uk/table-of-contents.html

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Karl actuall gave you the answer, but let's take it one step further.

For starters, if you have students and classes, the business rules would
be:

One Student may attend Zero to many Classes.
One Class will have One to many Students.

This is a classic many to many relationship and must be resolved. It is
done by using a Junction table. Assume the Student table has an Autonumber
primary key named StudentID and the Class table has a primary Autonumber
key
named ClassID.

Your Junction table will then need two fields to associate the two.
Typically, I use the same name for a Foreign key as I do for a primary
key.
so it would be:

StudentID - Long Integer
ClassID - Long Integer

That is the basics of the way it is done. There is no such thing as a
"universal key" that will link anything to anything.

I can't help but feel you didn't actually read my post.

I am not asking about how to relate Students, Classes, and
Registrations. I am quite comfortable with that. I am asking: How to
relate a single Notes table to three other tables?

Can you answer that question and no other?

 

[Grip]

Folks here volunteer their assistance.  Your response isn't likely to
encourage them (or others) to continue working with you on this.

You appear to have already settled on an approach.  Are you open to other
ideas about how to accomplish what it sounds like you want to, i.e., havea
way to know which form a "note" came from?   Notice that I said "form",not
"table".

By the way, if you are working directly in the tables, consider stepping
back.  Access tables store data, Access forms display it.  Access tables may
look like spreadsheets, but treating them like spreadsheets is a very good
way to end up with very bad data.

Eh, considering that the assistance I'm getting doesn't apply to my
question, I guess this newsgroup may be the wrong place to ask

I don't need basic instruction on relational databases. I am extremely
familiar with the difference between spreadsheets and databases.

I am open to other methods, but none have been offered. No response
from anyone here has provided an answer to my question (how to create
a db-wide unique ID) or provided an alternative.

 

[KARL DEWEY]

Nope, can't be done.
Maybe Grip wants to put the cart before the horse by have the Notes table as
master and the others as slaves.

You can have Notes in the main form (primary table) and Classes, Students,
and Class Registrations as subforms.
Scrolling through the notes the display would look like this --
Note1
Student23

Note2
Student2

Note3
Class5

Note4
Class Registration3

Note5
Student2 -- 2nd note for this student

Note6
Class15

Bad way to do it.

 

[Jeff Boyce]

How would we know you have that background? We can only work from what you
tell us.

Your question pre-supposes your solution... but open means willing to
consider alternate solutions.

My response pointed to using forms. If you had a form for each of those
tables, you could click a <Notes> button which could pop open a notes form
and tell it which form (and therefore, which table) had sent it. The user
would never see that, but the respective forms would have a way to "get"
their own notes.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Folks here volunteer their assistance. Your response isn't likely to
encourage them (or others) to continue working with you on this.

You appear to have already settled on an approach. Are you open to other
ideas about how to accomplish what it sounds like you want to, i.e., have
a
way to know which form a "note" came from? Notice that I said "form", not
"table".

By the way, if you are working directly in the tables, consider stepping
back. Access tables store data, Access forms display it. Access tables may
look like spreadsheets, but treating them like spreadsheets is a very good
way to end up with very bad data.

Eh, considering that the assistance I'm getting doesn't apply to my
question, I guess this newsgroup may be the wrong place to ask

I don't need basic instruction on relational databases. I am extremely
familiar with the difference between spreadsheets and databases.

I am open to other methods, but none have been offered. No response
from anyone here has provided an answer to my question (how to create
a db-wide unique ID) or provided an alternative.

 

[Klatuu]

Had you been paying attention, you would have understood the answer.
Jeff is a much nicer guy than I am. So in your stuborn, uncooperative
demeanor you didn't understand. You would use the same approach to relate
the notes to any of the other tables, but then you already knew that, right?

If you are so familiar with relational databases you would be able to figure
it out yourself.

Have a wonderful and enlightening day.
I really do hope you get it working.

 

[John W. Vinson]

In the main table use a field that is Autonumber as it will be unique. in
related table use a number - long integer to match DataType.
Open the relationship window and select the tables to relate. Click on the
Autonumber field of the main tabled and drag to number field of a related
(many side) table. Select Referential Integerity (means that must match) and
Cascade Update (automatically uses a number matching main when adding a
record).

Nitpick: Cascade Updates only works when you *edit* the value in the parent
table linking field - and since Autonumbers cannot be edited, it is neither
necessary nor useful in that case. Just leave Cascade updates unchecked and
let the subform's Master/Child link take care of it.

 

[Clifford Bass]

Hi,

If you do not mind not enforcing the referential integrity of the notes
table to the other tables, you can do it. If referential integrity is
important to you and you want Access to enforce it, then you cannot do it
because it violates basic database design in that you cannot relate one child
to multiple parents using the same key fields. For example, say you have a
class with an ID of 1 and a student with an ID of 2 and they are the only
items in their respective tables. When you try to add a record to your notes
table using an ID of 1, for the class, it will complain that no student
exists with an ID of 1. Likewise if you attempt to add a note for the
student using an ID of 2, it will complain that no class exists with an ID of
2. You could do the enforcement of the integrity in the form, but that
leaves the system open to corruption. If you have a back end with the
ability to specify insert, update and delete triggers on its tables, you
could enforce the rules using triggers.

Clifford Bass

 

[Gina Whipp]

Karl,

You know, I never thought of that... Hmmmm, lame but I guess it would work
in an IRrational database!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

Is the Replication ID the right tool for this? If not how do I create
an ID field that is unique across the database?

There are a couple of possibilities. One would be to make your three tables
subclasses of an Entity class - have a table, Entities, with an autonumber
primary key, and make your Students, Classes, and Registrations tables all
child tables of this master table, all with one-to-one relationships. You'll
still want foreign keys in the Registrations table to the primary keys of
Students and Classes, as WELL as linking the primary key of Registrations to
the Entities PK.

Or, you could use a GUID or Random autonumber as the PK of each of the three
tables, and use Subforms to inherit them into the foreign key of the Notes
table. This would let you have the Notes table as the "child" of all three
tables, albeit not with referential integrity.

Or, you could use a Custom Counter rather than an autonumber as the primary
key of the three tables, assigning a different range to each - Students with
ID's 1 to 999999, Classes ID's 1000000 to 1999999, Registrations 2000000 to
2999999, again using the Subform to programmatically enforce RI.

 

[jacksonmacd]

Would this work for you?
Have a "create note" button on each of the three forms. The button
will run code to find the maximum value of a field in an IDField
table. Increment the max value; that value becomes the ID number of
the note regardless of which of the three tables is its "parent".

AFAIK, there is no way to do it without creating the ID number
programatically.

 

[John Spencer]

You would need three fields in your notes table. One for each of the tables
you want to match to. At least I am guessing that the other tables are on the
one-side of the relationship. Many notes about one student or many notes
about one class or many notes about about class registration.

I may be wrong but I would expect that relationship. You can have a unique id
for each record in the notes field as the primary key.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

:

Is the Replication ID the right tool for this?

A GUID is the only data type in Access that gives you what you need.
But they are problematic to work with:

http://trigeminal.com/usenet/usenet011.asp?1033

There's nothing insurmountable there, but it does complicate things
substantially.