tables design

[luca]

Hello everybody!
I am almost as new to Access as to this forum...
I'm trying to create a database to keep track of some equipment. This is the problem I have (for not thinking yet in Access):
-I have a table with two fields: Model (text) and SerialNo (text);
-For some models I need to store more info - say Time Counter (Integer), Last Error (Integer), etc.
-How do I do this? It looks like a common situation, but I'm very confused at this point...
Not only I have a nonhomogenous table (with different number of fields for different records), but the problem propagates when I create the form: when I select (in a lookup) a certain model I need to be able to be shown and fill in the info according to that model.
An easy solution would be to just create the table with all the extra fields and leave them empty for models that do not have that info (90% of them). Is there a better solution?


Thanks for any input!

[RuralGuy]

Think of those extra fields as records in another table rather than extra fields in the current table. Your current table should have an AutoNumber as a PrimaryKey field and then you put this number in the other table as a ForeignKey field (LongInteger) creating the 1:m relationship. You can now have as many "other" pieces of data for each SerialNumber as you want and use a Form/SubForm to View/Edit/Add to the "Other" table.

[luca]

Thank you, RG for your help. I'm still confused, though. The situation you describe looks like a Order/Order details case, where for each order there is a variable number of records stored in the 'details' table. You might be right, but I'm not sure we talking about the same thing. My situation is this:
Let say the table is tblObjects, with the fields Model and Serial. The models can be A, B, C, D, E, F. Now, if Model=E or Model=F, I have 2 extra fields to add to tblObjects - ex. Error, Time. These 2 fields are specific to those models (E and F). It looks like I need 2 tables: one for models A,B,C,D with fields Model and Serial and one table for models E, F with fields Model, Serial, Error, Time. The problem is that tblObjects is in a 1:m relationship with another table; if I have two tables instead of one, it changes everything...
So my problem is having variable number of fields based on the content of a specific field (Model) and not a variable number of records.
What am I missing here?

[RuralGuy]

Well now I'm completely confused. You can not have a variable number of fields in a table. You can however ignore some fields depending on the value of other fields in the table.

[luca]

Thank you, RG for getting back to me! Confusion is contagious! I'm sure if you'd have had to design the (simple) database I'm working on for over 3 months now (and more than 4 Access 2007 books, including 'The Bible'), you'd be done long ago, without any confusion.
I'm trying to put in order some information I collect from repairing some electronics equipment, not for the posterity, but to make it useful to me.
The question is: what do you do when the objects in your database (or table) have many features (fields) in common, but differ in some? Like, you have a DB of vehicles, some cars, some SUV's, some trucks. They all share some fields - VIN, price, man. year, etc - but differ in others: cars - number of seats, doors, etc, SUV- traction, max. last, tracks-axles, etc. They do belong together (in a table) and they don't.
How do DB designers deal with this? It has to be a very common situation, with a simple (not to me) answer. Creating different tables with different fields and then putting them together with a query? And base the form on that query? Always confused!

[RuralGuy]

If you include ForeignKeys in your tables then you can put everything back together in a query as if it is in one table. That is the power and beauty of a relational database and queries.

[luca]

Thank you, RG.
I've come to realize that putting the extra info (fields) in a separate table and linking this as a child ('many' side) to the main table, allows me to attach the extra info needed to the 'special' cases (records) that need it, but without wasting any space, because the 'parent' can have any number of children, including zero! Problem solved! Thanks again!

[RuralGuy]

That's great! Are you ready to use the Solved thread tool yet?