Re: serial numbers in tables

On 02/19/2009 23:25:42 Chris Brown <cbrown@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Thank you, both. I think this "key field" idea is the one I am looking

for. I will explore this further. Thank you for getting me started!!

"Grip" <grip@xxxxxxxxxxxxx> wrote in message
news:194de389-1020-4df8-b3fc-1004f3a4bbcf@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 19, 1:23 pm, "lariveesl" <larivees...@xxxxxxxxxxxxx> wrote:

I am making this up, just to use as an example.

If I were going to set up an employee db, I might want to have the data
for each person in several tables. One table could be contact info and
it would get an auto generated serial number for each employee entry.
If I now want to make another table with job details, what do I do for
the serial number? I would assume the serial numbers would have to be
the same for both tables.

Or is this a waste of time? Should I simply take ALL the info for each
employee and put it in one large table?

You would make a table for Employees that has an auto-entered serial
number. You would have a second table for Contact Info, that would have
two fields. One would be an auto enter serial number and the other would
be for the Employee serial number. These are called key fields. Key
fields that contain unique serial numbers to the table are called
"Primary Keys." Key fields that contain the numbers of other tables are
called "Foreign keys."

The simplest way to ensure that the Contact Info has the correct Employee
key field is to relate Contact Info to Employee by the Employee key
field. In the relationship, check Allow creation of related records...
Create a layout based on the Employee table occurance and put a portal
showing records from the Contact Info table.

When you enter a new phone number into the portal, a record with the
correct Employee key will be created in the Contact Info table.

G

Primary Key

A primary key field MUST be defined with certain characteristics: unique
serial not null (not empty) unique immutable (uncheck allow user override,
check prohibit modification of value during data entry)

EVERY table MUST have a primary key.


If the table is only "secondary", containing information linked to a main table by that main table's primary key, there is no need for a primary key...


The PK field can be either a number or text type; if defined as a number
field then an auto enter serial starting 1, increment 1 is used.


Why not use anumeric key field, and start numbering not at 1 but at 100001...

No problems with uniqueness, or strange sorting differences between text and numeric sorting.


If
defined as text, then an alpha-numeric value can be defined, e.g.
starting value EM000001, increment 1. Be aware number and text fields
have different sort characteristics, so if a pure number series is defined
for the auto increment type, and the field has been defined as a text
field, then sorting will look like: 1 10 100 11

A text field PK defined as an alphanumeric series, will sort as expected
EM00001 Em00002 EM00003

Wiki is as good a place as any to start with info about keys.


Hans Besjes

.

Relevant Pages

  • Re: Form needs to update two tables then ...
    ... Disclaimer: This author may have received products and services mentioned ... with its own auto number key and a list of tasks that each ... The tasks that the employee works on is selected (on a ...
    (microsoft.public.access.formscoding)
  • Re: Auto Number
    ... An auto number would incriment each time a new record is added. ... why are you using an auto-number for employee number at ... should be assigned using some meaningful method. ...
    (microsoft.public.access.forms)
  • Re: Form needs to update two tables then ...
    ... with its own auto number key and a list of tasks that each employee works on. ... The tasks that the employee works on is selected from tbl_Tasks -- OR -- gets added to the table by using a combo box in a form, ... table (e.g. tblTask for the form and tblHours for the subform for instance) with the subform's link filled in to automatically filter the records. ... you can make the subform look as if it's a part of the form so the user doesn't even know there's a subform at all on the form. ...
    (microsoft.public.access.forms)
  • Re: serial numbers in tables
    ... get an auto generated serial number for each employee entry. ... A primary key field MUST be defined with certain characteristics: ... The PK field can be either a number or text type; if defined as a number field then an auto enter serial starting 1, increment 1 is used. ...
    (comp.databases.filemaker)
  • Re: Auto Increment records like a new emplyee number
    ... "TonySper" wrote in message ... > you increment the count and then save it. ... If you want to use your own numbering system ... >>> Lastly if I gave the first employee the ID of 101 and add a new ...
    (microsoft.public.fox.programmer.exchange)
Loading