Add New Record to Tables that have a one to one relationship

[leej1]

Hi
I have 2 table that are in a one to one relationship. The reason that the info is not in one table is because the number of fields exceed the 255 field capacity.
Using a form how can I add New Records without getting errors because of the relationship.
Thanks for any Help.
Leej

 

[PHV]

the number of fields exceed the 255
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

To expand on what PHV said, that's alot of columns. Maybe you can post your tables' structures (fieldnames in each table, primary key). It sounds like they're not normalized.

 

[leej1]

PHV & Fneily
Thanks for the reply. Below are the file definitions.
Leej1

I hope this is the way you want me to post the file definisions

C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Correction Table Page: 1



Columns

Name Type Size

PkCorKey Long Integer 4
CCom No Integer 2

CDiv Integer 2
CDept Integer 2
CEmp No Integer 2

CFirst Name Yes/No 1
CLast Name Yes/No 1
CStreet Yes/No 1

CCity Yes/No 1
CState Yes/No 1
CZip Yes/No 1

CZip4 Yes/No 1
CSoc No Text 11
CWage Town Yes/No 1

CRate Yes/No 1
CSalary Yes/No 1
CMar Status Yes/No 1

CState Mar Status Yes/No 1
CNo Deps Yes/No 1
CCode Yes/No 1

CShift Yes/No 1
CState Tax State Yes/No 1
CUC Tax State Yes/No 1

CFit Percent Yes/No 1
CExt Fit Yes/No 1
CExt State Yes/No 1

CExt Wage Yes/No 1
CEic Code Yes/No 1
CNo Fit Yes/No 1

CNo Fica Yes/No 1
CNo Med Yes/No 1
CNo State Yes/No 1

CNo State UC Yes/No 1
CNo Wage Yes/No 1
CNo Occup Yes/No 1

CWage Tax Percent Yes/No 1
CPays Per Year Yes/No 1
CDed Amt 1 Yes/No 1

CDed Amt 2 Yes/No 1
CDed Amt 3 Yes/No 1
CDed Amt 4 Yes/No 1

CDed Amt 5 Yes/No 1
CDed Amt 6 Yes/No 1
CDed Amt 7 Yes/No 1

CDed Amt 8 Yes/No 1
CDed Amt 9 Yes/No 1
CDed Amt 10 Yes/No 1

CDed Amt 11 Yes/No 1
CDed Amt 12 Yes/No 1
CDed Amt 13 Yes/No 1

C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Correction Table Page: 2


CDed Amt 14 Yes/No 1
CDed Amt 15 Yes/No 1

CDed Stop 1 Yes/No 1
CDed Stop 2 Yes/No 1
CDed Stop 3 Yes/No 1

CDed Stop 4 Yes/No 1
CDed Stop 5 Yes/No 1
CDed Stop 6 Yes/No 1

CDed Stop 7 Yes/No 1
CDed Stop 8 Yes/No 1
CDed Stop 9 Yes/No 1

CDed Stop 10 Yes/No 1
CDed Stop 11 Yes/No 1
CDed Stop 12 Yes/No 1

CDed Stop 13 Yes/No 1
CDed Stop 14 Yes/No 1
CDed Stop 15 Yes/No 1

CDed Paid 1 Yes/No 1
CDed Paid 2 Yes/No 1
CDed Paid 3 Yes/No 1

CDed Paid 4 Yes/No 1
CDed Paid 5 Yes/No 1
CDed Paid 6 Yes/No 1

CDed Paid 7 Yes/No 1
CDed Paid 8 Yes/No 1
CDed Paid 9 Yes/No 1

CDed Paid 10 Yes/No 1
CDed Paid 11 Yes/No 1
CDed Paid 12 Yes/No 1

CDed Paid 13 Yes/No 1
CDed Paid 14 Yes/No 1
CDed Paid 15 Yes/No 1

CDed Percent 1 Yes/No 1
CDed Percent 2 Yes/No 1
CDed Percent 3 Yes/No 1

CDed Percent 4 Yes/No 1
CDed Percent 5 Yes/No 1
CDed Percent 6 Yes/No 1

CDed Percent 7 Yes/No 1
CDed Percent 8 Yes/No 1
CDed Percent 9 Yes/No 1

CDed Percent 10 Yes/No 1
CDed Percent 11 Yes/No 1
CDed Percent 12 Yes/No 1

CDed Percent 13 Yes/No 1
CDed Percent 14 Yes/No 1
CDed Percent 15 Yes/No 1

CDir Dep Acct 1 Yes/No 1
CDir Dep Acct 2 Yes/No 1
CDir Dep Acct 3 Yes/No 1

CDir Dep Acct 4 Yes/No 1
CDir Dep Acct 5 Yes/No 1
CDir Dep Acct 6 Yes/No 1

CDir Dep Acct 7 Yes/No 1

C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Correction Table Page: 3


CDir Dep Acct 8 Yes/No 1
CDir Dep Acct 9 Yes/No 1

CDir Dep Acct 10 Yes/No 1
CDir Dep Acct 11 Yes/No 1
CDir Dep Acct 12 Yes/No 1

CDir Dep Acct 13 Yes/No 1
CDir Dep Acct 14 Yes/No 1
CDir Dep Acct 15 Yes/No 1

CDir Dep Bank 1 Yes/No 1
CDir Dep Bank 2 Yes/No 1
CDir Dep Bank 3 Yes/No 1

CDir Dep Bank 4 Yes/No 1
CDir Dep Bank 5 Yes/No 1
CDir Dep Bank 6 Yes/No 1

CDir Dep Bank 7 Yes/No 1
CDir Dep Bank 8 Yes/No 1
CDir Dep Bank 9 Yes/No 1

CDir Dep Bank 10 Yes/No 1
CDir Dep Bank 11 Yes/No 1
CDir Dep Bank 12 Yes/No 1

CDir Dep Bank 13 Yes/No 1
CDir Dep Bank 14 Yes/No 1
CDir Dep Bank 15 Yes/No 1

CDir Dep Code 1 Yes/No 1
CDir Dep Code 2 Yes/No 1
CDir Dep Code 3 Yes/No 1

CDir Dep Code 4 Yes/No 1
CDir Dep Code 5 Yes/No 1
CDir Dep Code 6 Yes/No 1

CDir Dep Code 7 Yes/No 1
CDir Dep Code 8 Yes/No 1
CDir Dep Code 9 Yes/No 1

CDir Dep Code 10 Yes/No 1
CDir Dep Code 11 Yes/No 1
CDir Dep Code 12 Yes/No 1

CDir Dep Code 13 Yes/No 1
CDir Dep Code 14 Yes/No 1
CDir Dep Code 15 Yes/No 1

CDir Dep Amount Yes/No 1
CDir Dep How Yes/No 1
CDir Ck Acct Yes/No 1

CDir Ck Bank Yes/No 1
CDir Ck Code Yes/No 1
CDir Ck Percent Yes/No 1

CDate Hired Yes/No 1
CDate Term Yes/No 1
CDate Last Raise Yes/No 1

CG/L Num Yes/No 1
CTip Rate Yes/No 1
CMeal Rate Yes/No 1

CNewEmployee Yes/No 1



Relationships

C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Correction Table Page: 4



Master TableMaster Correction Table


Master Table Master Correction
1 1
Div CDiv
1 1
Dept CDept
1 1
Emp No CEmp No
1 1
Soc No CSoc No


Attributes: Unique, Enforced, Cascade Updates, Cascade Deletes
RelationshipType: One-To-One




Table Indexes

Name Number of
FkCorEmpId 4

Fields:
CDiv Ascending
CDept Ascending

CEmp No Ascending
CSoc No Ascending

FkCorSocNo 1
Fields:
CSoc No Ascending

Master TableMaster Correction 4
Fields:

CDiv Ascending
CDept Ascending
CEmp No Ascending

CSoc No Ascending
PrimaryKey 1
Fields:

PkCorKey Ascending























C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Table Page: 5



Columns

Name Type Size

Pkmst Long Integer 4
Com No Integer 2

Div Integer 2
Dept Integer 2
Emp No Integer 2

First Name Text 15
Last Name Text 15
Street Text 30

City Text 20
State Text 2
Zip Text 5

Zip4 Text 4
Soc No Text 11
Wage Town Text 20

Rate Currency 8
Salary Currency 8
Mar Status Text 1

State Mar Status Text 1
No Deps Byte 1
Code Text 1

Shift Text 1
State Tax State Text 2
UC Tax State Text 2

Fit Percent Currency 8
Ext Fit Currency 8
Ext State Currency 8

Ext Wage Currency 8
Eic Code Text 1
No Fit Text 1

No Fica Text 1
No Med Text 1
No State Text 1

No State UC Text 1
No Wage Text 1
No Occup Text 1

Wage Tax Percent Currency 8
Pays Per Year Text 2
Ded Amt 1 Currency 8

Ded Amt 2 Currency 8
Ded Amt 3 Currency 8
Ded Amt 4 Currency 8

Ded Amt 5 Currency 8
Ded Amt 6 Currency 8
Ded Amt 7 Currency 8

Ded Amt 8 Currency 8
Ded Amt 9 Currency 8
Ded Amt 10 Currency 8

Ded Amt 11 Currency 8
Ded Amt 12 Currency 8
Ded Amt 13 Currency 8

C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Table Page: 6


Ded Amt 14 Currency 8
Ded Amt 15 Currency 8

Ded Stop 1 Currency 8
Ded Stop 2 Currency 8
Ded Stop 3 Currency 8

Ded Stop 4 Currency 8
Ded Stop 5 Currency 8
Ded Stop 6 Currency 8

Ded Stop 7 Currency 8
Ded Stop 8 Currency 8
Ded Stop 9 Currency 8

Ded Stop 10 Currency 8
Ded Stop 11 Currency 8
Ded Stop 12 Currency 8

Ded Stop 13 Currency 8
Ded Stop 14 Currency 8
Ded Stop 15 Currency 8

Ded Paid 1 Currency 8
Ded Paid 2 Currency 8
Ded Paid 3 Currency 8

Ded Paid 4 Currency 8
Ded Paid 5 Currency 8
Ded Paid 6 Currency 8

Ded Paid 7 Currency 8
Ded Paid 8 Currency 8
Ded Paid 9 Currency 8

Ded Paid 10 Currency 8
Ded Paid 11 Currency 8
Ded Paid 12 Currency 8

Ded Paid 13 Currency 8
Ded Paid 14 Currency 8
Ded Paid 15 Currency 8

Ded Percent 1 Currency 8
Ded Percent 2 Currency 8
Ded Percent 3 Currency 8

Ded Percent 4 Currency 8
Ded Percent 5 Currency 8
Ded Percent 6 Currency 8

Ded Percent 7 Currency 8
Ded Percent 8 Currency 8
Ded Percent 9 Currency 8

Ded Percent 10 Currency 8
Ded Percent 11 Currency 8
Ded Percent 12 Currency 8

Ded Percent 13 Currency 8
Ded Percent 14 Currency 8
Ded Percent 15 Currency 8

Dir Dep Acct 1 Text 17
Dir Dep Acct 2 Text 17
Dir Dep Acct 3 Text 17

Dir Dep Acct 4 Text 17
Dir Dep Acct 5 Text 17
Dir Dep Acct 6 Text 17

Dir Dep Acct 7 Text 17

C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Table Page: 7


Dir Dep Acct 8 Text 17
Dir Dep Acct 9 Text 17

Dir Dep Acct 10 Text 17
Dir Dep Acct 11 Text 17
Dir Dep Acct 12 Text 17

Dir Dep Acct 13 Text 17
Dir Dep Acct 14 Text 17
Dir Dep Acct 15 Text 17

Dir Dep Bank 1 Text 10
Dir Dep Bank 2 Text 10
Dir Dep Bank 3 Text 10

Dir Dep Bank 4 Text 10
Dir Dep Bank 5 Text 10
Dir Dep Bank 6 Text 10

Dir Dep Bank 7 Text 10
Dir Dep Bank 8 Text 10
Dir Dep Bank 9 Text 10

Dir Dep Bank 10 Text 10
Dir Dep Bank 11 Text 10
Dir Dep Bank 12 Text 10

Dir Dep Bank 13 Text 10
Dir Dep Bank 14 Text 10
Dir Dep Bank 15 Text 10

Dir Dep Code 1 Text 2
Dir Dep Code 2 Text 2
Dir Dep Code 3 Text 2

Dir Dep Code 4 Text 2
Dir Dep Code 5 Text 2
Dir Dep Code 6 Text 2

Dir Dep Code 7 Text 2
Dir Dep Code 8 Text 2
Dir Dep Code 9 Text 2

Dir Dep Code 10 Text 2
Dir Dep Code 11 Text 2
Dir Dep Code 12 Text 2

Dir Dep Code 13 Text 2
Dir Dep Code 14 Text 2
Dir Dep Code 15 Text 2

Dir Dep Amount Currency 8
Dir Dep How Text 1
Dir Ck Acct Text 17

Dir Ck Bank Text 10
Dir Ck Code Text 2
Dir Ck Percent Currency 8

Date Hired Date/Time 8
Date Term Date/Time 8
Date Last Raise Date/Time 8

G/L Num Text 6
Tip Rate Currency 8
Meal Rate Currency 8

Date Of Birth Date/Time 8
Phone Text 50


Relationships

C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Table Page: 8




Company MasterMaster Table

Company Master Master Table

Comp No Com No



Attributes: Not Enforced
RelationshipType: One-To-Many



Master TableEarnings Table

Master Table Earnings Table

Div 1 ¥ Ern Div

Dept 1 ¥ Ern Dept

Emp No 1 ¥ Ern Emp No

Soc No 1 ¥ Ern Soc No



Attributes: Enforced, Cascade Updates, Cascade Deletes
RelationshipType: One-To-Many



Master TableMaster Correction Table

Master Table Master Correction

Div 1 1 CDiv

Dept 1 1 CDept

Emp No 1 1 CEmp No

Soc No 1 1 CSoc No



Attributes: Unique, Enforced, Cascade Updates, Cascade Deletes
RelationshipType: One-To-One



Master TableMaster Import Table


Master Table Master Import Table

Soc No ISoc No


Attributes: Not Enforced
RelationshipType: Indeterminate




Table Indexes

Name Number of


C:\Users\leejp\Crihist\Test\Access-Payroll-Enter.mdb Wednesday, April 30, 2008

Table: Master Table Page: 9

FkMstChangeKey 4
Fields:
Div Ascending

Dept Ascending
Emp No Ascending
Soc No Ascending

FkMstEmpId 3
Fields:
Div Ascending

Dept Ascending
Emp No Ascending

FkMstSocNo 1
Fields:
Soc No Ascending

PrimaryKey 1
Fields:

Pkmst Ascending

 

[PHV]

Did you read (and make sense) what I've suggested ?