Re: serial numbers in tables

lariveesl 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.

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. 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.
.

Relevant Pages

  • Re: New to Access db
    ... The Foreign Key fields in the Subform which arentt linked to the main form ... Add EmpID and Employee to the query. ... AttEventID - Primary key Auto Number ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Understanding Relationships
    ... have pages of things they might track about an employee. ... and for which a history is to be kept MUST be in a related (child) ... primary key of this employee's record in tblEmployee. ... Table 2 [SalaryScale related to Table 3 SalaryScale] ...
    (microsoft.public.access.gettingstarted)
  • Re: Understanding Relationships
    ... have pages of things they might track about an employee. ... and for which a history is to be kept MUST be in a related (child) ... primary key of this employee's record in tblEmployee. ... Table 2 [SalaryScale related to Table 3 SalaryScale] ...
    (microsoft.public.access.gettingstarted)
  • Re: WWW/Internet 2009: 2nd CFP until 21 September x
    ... Firsst_Name char 25 not null, ... Noe let's say that the rules are that you can't put an employee into the table unless you know the First Name and Last Name, and you get assigned to the employee an Employee_Id that's never been used before. ... (Employee_Id integer primary key foreign key references ... There's no nulls allowed in the two table solution, but there's no law that says you can't leave an entire row out. ...
    (comp.databases.theory)
  • Re: AutoNumber Question
    ... you convinced me, im an auto number ... SSN *legally*, but unless you want to get into tracking down fraudulent ... primary key field for your employee table *as long as it is a value the ... Autonumber primary key and still allow your user to seach for an employee ...
    (microsoft.public.access.tablesdbdesign)
Loading