One or Several Tables

[Debbilynn]

I have a conundrum. I inherited a database with one table containing approximately 100 fields. Each record will only have each piece of data one time. The problem I am encountering is in the Forms. I have built a form with 30+ fields to be entered with a new record. This is all relevant information that must be there. There are two more forms with two sets of optional information that I want linked to the main form. When the optional form(s) open, I want the PK to show. Problem is, since the main entry form is open, I cannot access the other form.

That said, I could conceivably break this table up into three separate tables and have a one-to-one relationship with the main table. If I do this, how do I go about creating a new record in the optional tables based on the PK? And what if I want to enforce Referential Integrity?

Are you as confused as I am?

 

[fneily]

It's a high probability that the table isn't normalized. All tables must be normalized first. Then they're connected through relationships. For a start see:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You just don't break a table into three separate parts. They'll still not be normalized.

If you post your table structure, eg.
tblCustomers
CustID
Fname
Lname
etc.
We can see if it needs to be normalized.
So just table name and field names, no data.

 

[Debbilynn]

The original table was not normalized but I went through it and took out many duplicate fields, added new tables, created some combo boxes, defined the fields better, et al. I figured out my problem with the forms not opening. The original forms I built off of had crazy stuff in the properties. I should have just started from scratch. <sigh>

Thanks anyway! I'm sure I will have more questions as things progress. I am learning the VBA side of access which is really exciting but I can get bogged down easily.

Thank you again,

Debbilynn