new to access need help with tables

[charlieb]

I am new to access just installed Access13. I am trying to set up a practice management solution for my law office. The table I need to create would contain too many fields (probably >260) so I broke it up into several tables and created a one to one relationship between them. I probably am not doing this the right way, but I figured that if I have an ID (autonumber) field in each table and connect the tables by the ID field that would be like having one big table. When I did that and created a form including all tables it worked fine if there was some data input into at least one field in each table (so that a record is created in that table maintaining the integrity for the same record in each table). If I did not have some info in at least one field of each table then that table will not advance to the next record along with the others that did have some data input. To try to correct the problem, I created a field in each table that I labeled Invisible (Invisible1, Invisible2, etc.. for each table) and set the properties to invisible, with a default value of 1. By including that field in the Form and setting the size of the textbox to 0 it takes up no space on the form and insures that there will be some input from at least this field in every table to keep the records across these tables straight. That creates the problem that records with no real info can be created in my database (since there will always be some input from that Invisible field in every table) which I don't want. What is the best way to do this? Sorry for the long post.

[June7]

Do not connect 1-to-1 tables on autonumber fields. One table can have autonumber PK and other tables must have number field to hold the PK value as a FK.

You probably have so many fields because the data structure is not normalized.

A query also cannot have more than 255 fields.

[pbaldy]

What do all the fields represent? You are probably not normalizing the data:

Fundamentals of Relational Database Design -- r937.com

[charlieb]

What do all the fields represent? You are probably not normalizing the data:

Fundamentals of Relational Database Design -- r937.com

The reason I have all these fields is because of the number of parties involved. I practice personal injury law so I need to input and store not only client personal and contact info but contact and policy info from Insurance Companies (often 3-4 different companies), opposing parties/attorneys, 7-8 treating doctors, Hospitals, Court info etc.. for each client.

I see no commonality between my tables, not sure how to join them and make them advance, record by record in conjuction with each other.

[rpeare]

Let me third the suggestion you review a normalized structure. Let me give you an example

Let's say you have a customer but the customer has multiple locations.

You do not want the following:

CustomerID  CustomerName  Address1  City1  State1  Address2  City2  State2  Address3  City3  State3
1           CustomerA     Add1      AAAAA  ME      Add2      BBBBB  OR      Add3       CCCCC  TX

Rather you want something more like:

CustomerID  CustomerName
1           CustomerA

AddressID  CustomerID  Address  City  State
1          1           Add1     AAAAA ME
2          1           Add2     BBBBB OR
3          1           Add3     CCCCC TX

Not only does it save you storage space but it makes dealing with your data a lot easier.

[June7]

Normalization would mean a table for each of those entities and multiple records. So if the case has 1 doctor or 20, they can all be associated. The only other alternative to reduce the number of fields is with multi-value fields and I NEVER use multi-value fields.

Could maybe even have 1 table to list all the people connected - have another field for their type of association (plaintiff, defendant, witness, attorney, doctor, insurer, etc).

[orange]

Charlie B,

It appears you are not only new to Access but new to database. Do yourself a big favor --work through this tutorial ASAP to get a hands-on appreciation of Tables and Relationships, Normalization, Entity Relationship Modelling and a plan.

When you're finished, there are some videos and links here to help put some additional concepts into perspective for you.

Good luck.

[charlieb]

Thank you all for the help, I will review these suggestions tonight after work