Multiple tables, One form with 3 Subforms problems

[after68]

Hi Guys & Gals, I have an Access 2007 database that I am fighting with. I have a friend who needs a data entry database. So far I have created 4 tables: Cases, Customers, Data1, Data2.
Cases_tbl has following fields:ID(autonumber), Customers_ID(number), Data1_ID(number), Data2_ID(number).

The Customers, Data1 and Data2 Tables are all similiar, they all have the ID(autonumber) Field followed be fields that need data inputed.

I would like to have the Cases table as the master table that is linked to the other 3 tables, so when I delete a customer, there case, and all of there data is deleted with it.

I have one form with 3 subforms. Each subform is used for inputing data into the customer, data1, and data2 tables. However, I cannot for the life of me link all these tables together. I have tried all different relationships and have search the web high and low and I cannot find an answer anywhere. So if somebody could help I would be much appreciative.

 

[neileg]

Not clear whether you really need three susidiary tables but I can't be certain.

Anyway, if Cases_tbl is the main table, you don't store the keys from the subsidiary tables in it, you store the PK from Cases_tbl in the subsidiary tables.

 

[after68]

I need the data tables because each of them has about 150 fields which is >255
I just need a way to link several tables so that each record has a corresponding reference in the cases table.

 

[Rabbie]

If the relationship is 1 to many you store the ID of the 1 record in each of the many records. That's how relational databases work.

 

[Rabbie]

Also Access tables should be tall and thin. When people say they have lots of fields it rings alarm bells about their design and whether the data is properly normalised.

 

[boblarson]

I also have alarm bells for normalization go off when someone says I have tables Data_1 and Data_2.

 

[after68]

I have 3 paper forms that get filled out on the road. These are evaluations. I want to input the data from the paper forms into a simple database. Each of the physical forms have about 135 places for data. I would like to make an Access database that lets me input my data into the respected table.

ie paper form1 = table1
paper form2 = table2
paper form3 = table3

All three tables would be linked to a single customer

What would be another route beside access?

 

[neileg]

The point is that instead of 400 columns (fields) you have 400 rows (records), each one with an ID field that identifies the Customer. Many newcomers to databases think in spreadsheet terms. In a spreadsheet, your data display and data storeage is in the same place. In a relational database, no one sees the tables, so you go for maximum efficiency of design in the table, and save the fancy formatting for forms and reports which is what the user sees. So there's nothing wrong with using Access, it's just you need to forget everything you ever knew about spreadsheets!

 

[after68]

Is there anyway you could give me an example as to how I should go at it?

 

[boblarson]

Check this out to start with:
http://support.microsoft.com/kb/283878

 

[after68]

Thank you for the link, however, we may be having interpretation errors. Let me explain. All three forms that need to be filled out are completely seperate. All repetitive data should be referenced to another table. None of the form data is repetitive.
table one is my customer table, I want to be able to input there address, email, phone, zip...... so on and so forth.

table two would be, PK, knee wall Sq.Ft, Floor Sq.Ft, Pre R Value, Post R-Value........

table three would be, PK, house age, heating volume, leakage site..............

All of the inputed data is going to be unique to the customer
there is no vendor, no supplier, just data

None of my data is repetitive.... So should I split up my tables so I have 50 tables with 10 fields a piece or keep it the way I'm going? Or am I just being bullheaded and not really getting what you guys are saying?

 

[neileg]

None of my data is repetitive.... So should I split up my tables so I have 50 tables with 10 fields a piece or keep it the way I'm going? Or am I just being bullheaded and not really getting what you guys are saying?

Not bullheaded, but this isn't a straightforward situation and needs some thought.

I'm not a trained programmer so I feel at home with ignoring the rules when it suits me. However, normalisation is the key to getting a well designed application. And I'll throw another problem at you: A table is limited to 255 fields but so is a query, so there's no way you're ever going to get one dataset that has all the data for one customer. However there is no such limit on the number of rows you can have. So really you have no choice but to use rows.

In addition, designing forms to house more than about 20 fields in a user friendly way is a real issue.

The way I would approach this is as follows:
I'd have one table for the customer details, name, contact details and so on.
A second table would hold all of the building measurement details that are the meat of your data. This would have 4 fields (from what you have said)

Primary key
Foreign key link to customer table
Measurement description
Measurement value

No you won't want to be creating 200 and odd fields from scratch every time, so I'd have a third table that holds a predefined list of the descriptions. Then when I create a new customer and new building, I can append the third table plus the customer ID to the building measurement table to give me the blank records ready for completion. You can then manage these records in a continuous form for easier data entry.