How many similar fields should be in each table?

[access2010]

We are developing a database that will perform four comparisons each day.

My idea was that each table would contain about 36 different fields except for one common field, the part number (identifier / common Key).

Table A would contain the part number and other product information.
Table B would contain numeric values related to the part number for the first comparison.
Table C would contain numeric values related to the part number for the second comparison.
Table D would contain numeric values related to the part number for the third comparison.

In the future other comparisons may be needed.

My question is, should we have one table containing approximately 540 fields or 4 separate tables linked to a common field?

The information about the item would be held in 4 separate tables.

The information is shared on a form for viewing by part number or for printing.

May I please have your comments?
Nicole

 

[mike60smart]

Hi
Well for a start you cannot have "one table containing approximately 540 fields".
The limit is 255 fields for each Table.

Can you give us an example of the data for each of the 4 suggested tables?

 

[GPGeorge]

As a matter of fact, no Access table can contain more than 255 fields anyway. And any table with more than 36 fields would be suspect.

You need three tables, not four.

The first table is the Part table, a good name might be "tblPart", or perhaps something more descriptive. It contains only details used to identify and describe the part. No information about a comparison is ever in this table.

The second table is the Comparison table. It can contain as many fields as are required to identify the results of the comparison. That may be 36, or it may be many fewer than 36; we don't know because we don't know what goes into a comparison. It requires a Foreign Key field for the PartID so that the Part involved in that comparison is identified.

In addition to those fields, this Comparison table, called something like "tblPartComparison", needs a field to identify which comparison was completed. It can can values 1, 2 or 3, given that you allow for up to 3 comparisons per part.

The final table --which is recommended but not required -- is the table for Number of Comparisons. It has 3 records to begin, but could be expanded if you ever add a fourth, or fifth, comparison.

Missing from this initial description is whether each part in your operation is uniquely identified by a PartID, or whether you have multiple instances of each PartID. If the comparison is on a unit basis (each separate Part is tested individually) you have one table design. If you have multiple units with a common PartID, such as interchangeable bottle caps, or screws or shelves, it may be that comparisons are only done between Part types. All of that has to go into the design decision as well.

 

[plog]

My guess is 'none of the above'.

Even your low estimate of 4 tables with 36 fields each is way too many fields. I suggest you post what you think one of those tables would look like. Give us those 36 fields along with some sample data and we can suggest a proper structure

 

[Pat Hartman]

@access2010 as the others have mentioned, you are on a wrong track. You need to do some reading about normalization. Then post your schema here BEFORE you start building anything and we'll help you to refine it.

I agree with @plog The answer to your specific question is NONE. Having multiple similar fields implies that you are creating a repeating group which violates first normal form.

 

[Mark_]

Hi Nicole,

I think you've got too many tables.
Tables B, C, and D are just table B with a "Comparison#' at the beginning. This can be a field in the table so you can add additional comparisons as you go.

 

[The_Doc_Man]

Nicole, that data set you mentioned is a prime example of why you absolutely MUST study normalization because otherwise you are going to try to lock yourself into an impossible design. Access has limits on the number fields that can appear in a table OR in a query. Having that many fields in a table is an example of "horizontal" design where you widen the tables to hold more data. But if you make your comparisons be isolated events (actions?) and have as many of them as you need with a foreign key back to your part number table, you can have as many of these comparisons as you need.

Normalizing, your B, C, and D tables would yield one table where one field will say it was a "B" or a "C" or a "D" type of comparison... or however you do that. But once you make the table structure more vertical - i.e. fewer fields per table but a LOT more records - it becomes much easier to handle. Then if you have more of these comparisons down the line, "E", "F", and "G" aren't that hard to implement. And besides, even if you had everything in 540-field table, you would still have a single-processor CPU doing work on a few fields at a time anyway.

Do yourself a favor. You need to study Access and relational databases a bit more. We've talked to you about this before. I repeat a question for you. If you don't have time to do it right in the first place, how will you EVER find time to fix it later?

 

[Pat Hartman]

If you don't have time to do it right in the first place, how will you EVER find time to fix it later?

One of my most influential early bosses had something similar as a sign outside his office door. He was right. It is a hard lesson to learn and I have the scars to prove it but I did learn.

 

[Mark_]

Nicole,

I've been thinking a bit about what you've posted. Sounds like your parent file is "Parts" that holds your part numbers.
Child holds you "about 36 different fields" as the parent ID, the value you save, Date/Time, and a link to what ever lookup is needed for what would have been the labels for these fields.

Yes, you'd also need a separate table to hold the labels.

This means you'd use a query to return all child records for a parent based on WHEN the entries were made, as well as lookup up their labels.

You could then add extra comparisons, add extra values (and their labels) and display the results on a report with little difficulty.

Does this sound like what you are really trying to do?

It also means if you need to add extra comparisons or extra "fields for results" you can do so very easily.