Primary Key with Number data type

[placek]

Hi guys
I have three tables:

tblCompany (lngCompanyNumberCnt[PK], strName)
tblBranch ( lngBranchNumberCnt[PK], strEmail,
lngCompanyNumber[FK])
tblContact(lngContactNumberCnt[PK], strName, strEmail,
lngBranchNumberCnt[FK])

One company may have many branches and one branch may have
several contacts. As Access specified that PK and FK must
have the same data type , they all have Number type apart
from lngContactNumberCnt which has autonumber.

I have a data entry form with tblCompany fields in the
main form and tblBranch fields in sub form. In an ideal
world a data entry clerk would input lngBranchNumberCnt or
lngCompanyNumberCnt in a sequence, so allocating the next
number is straight forward. However, it may be pheasable
that the clerk inputs these PK in a hap hazard fashion
(e.g. 1, 2, 3, 8, 11, 56, 677, ........etc.). In this
situation it is impossible to keep track of those numbers
used as PK.

So, the next time someone goes to input a Branch Number it
is possible that they will use an existing one. I have
found that in these cases, Access prevents you from
creating the record [due to duplicate indices]. Is the
only way to prevent this to stand over the data entry
clerk 's shoulder, to ensure that these PK's are inputted
in STRICT sequence?

Thanks, Martin

 

[Jeff Boyce]

Martin

The Access Autonumber is a long integer. Unless there's an underlying
business requirement you didn't describe, you could use Autonumber for your
tblCompany PK field, for your tblBranch PK field AND for your tblContact PK
field.

Just be sure that the corresponding FK fields are long integers.

 

[placek]

Hi Jeff

Thanks for the info.

I considered the autonumber for tblCompany and tblBranch,
but [correct me if i am wrong] but the following scenario
may occur......

Say autonumber allocates lngCompanyNumberCnt 1 for Company
X. If this company has 3 branches then (assuming the
tblBranch is being christened), autonumber will allocate
lngBranchNumberCnt 1, 2 and 3.

Ignoring the contact table for the moment, say autonumber
allocates lngCompanyNumberCnt 2 for Company Y. If this
company has 1 branch then autonumber will allocate
lngBranchNumberCnt 4 . Now , given that
lngCompanyNumberCnt is a FK in tblBranch, autonumber will
allocate
BranchNumber Company Number
1 1
2 2
3 3
4 4

This would ofcourse be factually incorrect given Branch
1,2 and 3 all belong to company 1.

Thanks in advance

 

[tina]

you're a little confused re primary key vs foreign key in a table. each
table has a primary key (the autonumber will be fine) that provides a unique
value for each record in the table.
tblCompany is your "parent" table. it's primary key field will be matched
with a foreign key field in tblBranch, as follows:

tblCompanies (parent table. "one" side of 1:n relationship w/ tblBranches)
CoID (primary key - data type Autonumber - field size is always Long
Integer)

tblBranches (child table. "many" side of 1:n relationship w/ tblCompanies)
BrID (primary key - data type Autonumber - field size Long Integer)
CoID (foreign key from tblCompanies - data type Number - field size Long
Integer)

data will be:

tblCompanies
CoID
1
2
3
4

tblBranches
BrID CoID
1 1
2 1
3 2
4 2
5 2
6 2
7 3
8 4
9 4
10 4

etc, etc.

the relationship between tblBranches and tblContacts works exactly the same.
in that instance, tblBranches is the "parent" table and tblContacts is the
"child" table.

hth

Hi Jeff

Thanks for the info.

I considered the autonumber for tblCompany and tblBranch,
but [correct me if i am wrong] but the following scenario
may occur......

Say autonumber allocates lngCompanyNumberCnt 1 for Company
X. If this company has 3 branches then (assuming the
tblBranch is being christened), autonumber will allocate
lngBranchNumberCnt 1, 2 and 3.

Ignoring the contact table for the moment, say autonumber
allocates lngCompanyNumberCnt 2 for Company Y. If this
company has 1 branch then autonumber will allocate
lngBranchNumberCnt 4 . Now , given that
lngCompanyNumberCnt is a FK in tblBranch, autonumber will
allocate
BranchNumber Company Number
1 1
2 2
3 3
4 4

This would ofcourse be factually incorrect given Branch
1,2 and 3 all belong to company 1.

Thanks in advance

-----Original Message-----
Martin

The Access Autonumber is a long integer. Unless there's an underlying
business requirement you didn't describe, you could use Autonumber for your
tblCompany PK field, for your tblBranch PK field AND for your tblContact PK
field.

Just be sure that the corresponding FK fields are long integers.

--
Good luck

Jeff Boyce
<Access MVP>

.

 

[placek]

Thanks, Tina. That has cleared that up in my mind. Your
suggestion will save me lots of time and energy.

Martin

-----Original Message-----
you're a little confused re primary key vs foreign key in a table. each
table has a primary key (the autonumber will be fine) that provides a unique
value for each record in the table.
tblCompany is your "parent" table. it's primary key field will be matched
with a foreign key field in tblBranch, as follows:

tblCompanies (parent table. "one" side of 1:n relationship w/ tblBranches)
CoID (primary key - data type Autonumber - field size is always Long
Integer)

tblBranches (child table. "many" side of 1:n relationship w/ tblCompanies)
BrID (primary key - data type Autonumber - field size Long Integer)
CoID (foreign key from tblCompanies - data type Number - field size Long
Integer)

data will be:

tblCompanies
CoID
1
2
3
4

tblBranches
BrID CoID
1 1
2 1
3 2
4 2
5 2
6 2
7 3
8 4
9 4
10 4

etc, etc.

the relationship between tblBranches and tblContacts works exactly the same.
in that instance, tblBranches is the "parent" table and tblContacts is the
"child" table.

hth

Hi Jeff

Thanks for the info.

I considered the autonumber for tblCompany and tblBranch,
but [correct me if i am wrong] but the following scenario
may occur......

Say autonumber allocates lngCompanyNumberCnt 1 for Company
X. If this company has 3 branches then (assuming the
tblBranch is being christened), autonumber will allocate
lngBranchNumberCnt 1, 2 and 3.

Ignoring the contact table for the moment, say autonumber
allocates lngCompanyNumberCnt 2 for Company Y. If this
company has 1 branch then autonumber will allocate
lngBranchNumberCnt 4 . Now , given that
lngCompanyNumberCnt is a FK in tblBranch, autonumber will
allocate
BranchNumber Company Number
1 1
2 2
3 3
4 4

This would ofcourse be factually incorrect given Branch
1,2 and 3 all belong to company 1.

Thanks in advance

-----Original Message-----
Martin

The Access Autonumber is a long integer. Unless

there's
an underlying

business requirement you didn't describe, you could use Autonumber for your
tblCompany PK field, for your tblBranch PK field AND

for
your tblContact PK

field.

Just be sure that the corresponding FK fields are long integers.

--
Good luck

Jeff Boyce
<Access MVP>

.

.

 

[tina]

you actually had your tables constructed properly, except for thinking that
your foreign key fields should be Autonumber, when of course they needed to
be Number. Jeff's reply gave you correct info, too; i just gave a more
detailed explanation of the mechanics of PK/FK. glad it helped clear things
up for you.

Thanks, Tina. That has cleared that up in my mind. Your
suggestion will save me lots of time and energy.

Martin

-----Original Message-----
you're a little confused re primary key vs foreign key in a table. each
table has a primary key (the autonumber will be fine) that provides a unique
value for each record in the table.
tblCompany is your "parent" table. it's primary key field will be matched
with a foreign key field in tblBranch, as follows:

tblCompanies (parent table. "one" side of 1:n relationship w/ tblBranches)
CoID (primary key - data type Autonumber - field size is always Long
Integer)

tblBranches (child table. "many" side of 1:n relationship w/ tblCompanies)
BrID (primary key - data type Autonumber - field size Long Integer)
CoID (foreign key from tblCompanies - data type Number - field size Long
Integer)

data will be:

tblCompanies
CoID
1
2
3
4

tblBranches
BrID CoID
1 1
2 1
3 2
4 2
5 2
6 2
7 3
8 4
9 4
10 4

etc, etc.

the relationship between tblBranches and tblContacts works exactly the same.
in that instance, tblBranches is the "parent" table and tblContacts is the
"child" table.

hth

Hi Jeff

Thanks for the info.

I considered the autonumber for tblCompany and tblBranch,
but [correct me if i am wrong] but the following scenario
may occur......

Say autonumber allocates lngCompanyNumberCnt 1 for Company
X. If this company has 3 branches then (assuming the
tblBranch is being christened), autonumber will allocate
lngBranchNumberCnt 1, 2 and 3.

Ignoring the contact table for the moment, say autonumber
allocates lngCompanyNumberCnt 2 for Company Y. If this
company has 1 branch then autonumber will allocate
lngBranchNumberCnt 4 . Now , given that
lngCompanyNumberCnt is a FK in tblBranch, autonumber will
allocate
BranchNumber Company Number
1 1
2 2
3 3
4 4

This would ofcourse be factually incorrect given Branch
1,2 and 3 all belong to company 1.

Thanks in advance

-----Original Message-----
Martin

The Access Autonumber is a long integer. Unless there's
an underlying
business requirement you didn't describe, you could use
Autonumber for your
tblCompany PK field, for your tblBranch PK field AND for
your tblContact PK
field.

Just be sure that the corresponding FK fields are long
integers.

--
Good luck

Jeff Boyce
<Access MVP>

.

.