One-to-One relationsip

[Sandy]

Hello -

I have a set of data that I have broken out into two tables since there is a
lot of data. The tables are JOBS and QUOTES. The JOB NUMBER and the QUOTE
NUMBER should be the same. The JOB NUMBER field in the JOBS table is an
autonum field. The QUOTE NUMBER field in the QUOTE table is a number field.

I would like some help determining the best way to set up the relationships
between these two tables.

The jobs form and the quote form are set up in a tab control. The data
source of the form behind the tab control is the JOBS table. The forms on the
Tab Control are all linked by the JOB NUMBER.

Ideas I have been considering:

I can set up the Quote number to be linked as a one-to-one relationship with
the Job Number. When I create a new job, I can create a new recored in the
QUOTE table and set the value of the QUOTE NUMBER to equal the JOB NUMBER.

Another option is to make one large table.

I am not sure of the best option and ideas would be appreciated.

many thanks
sandra

 

[Roger Carlson]

It is rare when a table has so many fields that it needs to be broken up
into two tables. More than likely, your table isn't normalized.

Look for fields with repeated values, like Material1, Material2,
Material3... and so forth. These fields can be collected into a separate
table with a single field called Material and a foreign key field related to
the main table.

Also look for Yes/No fields where the actual value is stored in the field
name, especially when many of them are options for the same thing. For
example, you might have Yes/No field like: Quoted, Bid, Accepted,... and so
on. These could also be collapsed into a single field called "Status" whose
values are the above. You can store these values in a LookUp table for
reference.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Sandy]

Thanks Roger - One table is probably the best way to go.
s-