How to set the relationship

[Erictsang]

Dear all


I have a huge file that contains the following 42 fields See attached



I want to split them into few small tables and convert into forms

I need to join them up.

Am i right that i need to use the ID field for all the spilt files into to join them

I tried but Access prompt for iNDEX key and also alert me that the Primary key should not be null value

Appreciate your help, it would be my first project. i was told there are around 5000 records

eric

[June7]

Why split into separate tables? If the records will have 1-to-1 relationship might as well remain as a single table. Then instead of multiple subforms, have a single form with fields distributed on pages of a Tab control.

FULLNAME should really be split into several fields. Name parts should be in separate fields - LastName, FirstName, MidName, Title, Suffix.

[Erictsang]

June 7

Sorry, i do not understand.

The reason i split the big table of 42 fields (in fact more than that) . So i want to break it into few tables

So it is not wise to break it into small tables?

What do you mean by tab control ?

[Missinglinq]

Glancing at your graphic there appears to be no reason for a second, related Table, and using one will only add work for you.

If one of your Fields could have multiple Values, say you had a field for academic degrees, and the Values could be BS in Biology, MS in Microbiology and PhD in Epidemiology, then yes, you'd need a second Table and it would be linked/related by way of the ID Field...it being the Primary Key for the Main Table and the Foreign Key for the 'degrees' Table.

A Tabbed Control is a Control on a Form that has a large number of Controls, where you can place the Controls that hold the data from Fields in a logical manner, i.e the yellow, green, blue and gray groups of Fields each on a different page. It's a device for not only displaying data in a logical way, but saves 'real estate' when you have a lot of Fields, such as you do.

Linq ;0)>

[ssanfu]

I think you might well consider splitting a few tables out of the main table... I would look at normalizing the structure.

Fields 3 - 6 (yellow) seem to be about a person.
Fields 15 - 24+ (blue) seen to deal with flights.

Fields 12 - 14 - the names seem to be data & not good field names. Maybe a better name would be Places.

Other fields?????


My $0.02.......

[June7]

Whether or not to split depends on data relationships. Can each person have more than one flight?