mulitple or single tables

[sscastor]

If I'm going to have several forms that have similar data and I want to pull information from them for reports. Should I have a table for each form or can I make one table with all the items, i.e. name, address, phone and use it on all the forms, not all data in the table would be used on each form however.

 

[DougP]

Yes ONE table, mutilple forms. that's how extremely large applications are written. Data can be gathered form all over the world using the Internet.
Have all of your forms look at the same table.
then if things are different for each form add a new field to your table called Where-I-CameFrom. Say form1 adds data to your Table then your New field "Where-I-CameFrom"'s data could be "form1"
forms 2 data added to the same table would have "form2" in the new field called "Where-I-CameFrom".
This is kind of silly to put in form2 etc but you get the idea. Put whatever makes sense to you

then you can create queries, reports and whatever that either look at the new field for results or not.

 

[THoey]

My data structures teacher would be so proud...

You are right in the idea that you don't look at the number of tables needed based on the number of forms you are going to have. But I will say to try and base the number of tables on the different "types" of data you are going to have. If you are going to keep data with the following field names:

Customer_ID
Customer_Name
Customer_Address
Customer_Phone
Sale_ID
Sale_Item
Item_ID
Item_Description
Item_Cost

You might want to break that out into three tables:

Customer Table
Customer_ID
Customer_Name
Customer_Address
Customer_Phone

Sales Table
Sale_ID
Customer_ID
Sale_Item

Items Table
Item_ID
Item_Description
Item_Cost

Then you could have your different forms pull different data from all of the different tables. This keeps you from having one big ugly table that has a sale record that has the address for ACME Inc. in each record for a sale to them.

Hope this helps...

 

[THoey]

Oops, I caught the following error too late:

Sales Table
Sale_ID
Customer_ID
Sale_Item <--- Change this to Item_ID