Reasoning behind TWO combo boxes for the same field?

[ChrisSedgwick]

Good morning,

I've been asked to take a look at a database to look for areas which could be improved. It's not a database I've built myself so I've started by taking a 'walk-through' of the system to see how it works.

What I did notice amongst other things which confused me a little was that, on a specific form, called 'Tenders Sub Form' (tenders stands for builders), there are two combo boxes, each of which is used to enter the same kind of data back to the 'Tenders' table.

I've uploaded a screenshot for you to see. As we work down the 'Tenders Sub Form', we're asked to enter the 'CustomerID', which is simply the ID for the Customer and you'll see from the Relationships I've also uploaded, that this is the Primary Key in the 'CustomersMain' table. This is also an AutoNumber data type. Further down just under 'QuoteID', there is another combo box, which asks for the 'CustomerName'. This is using all the same data as the first combo box, only the first combo box is bound to column 1, the CustomerID and the second combo box is bound to column 2, the Customer Name.

If we then look in the 'Tenders' table - we can see that it's storing the Customer ID and CustomerName is there own fields.

My question really is, would there be a more efficient way to store both the CustomerID and CustomerName in the Tenders table, without the need to use two combo boxes and effectively enter the same data twice.

I'm sure that this was set up with the purpose of being able to see the Customer Name in the table rather than just the Customer ID.

There surely has to be a more efficient way? I'm hoping that someone could shed some light on this and offer some advice. :banghead:

I've uploaded

Relationships
Tenders Sub Form - to see how the combo boxes look
Tenders Table Properties

Please let me know if you need anything else.

Thanks in advance.
Chris.

 

[plog]

This all comes down to normalization. The issue you mention has to do with storing redundant data in Tenders. There's no reason that Tenders should have a CustomerName field. You have a CustomerID in it which allows you to link back to CustomersMain and retrieve the name.

A drop down can be configured to display one thing (CustomerName), but save another (CustomerID). This is what you should do on your forms.

I also see other normalization issues:

1. Multiple paths among your tables. There should only be one way to traverse from one table to another. For example, there are 2 ways to get from Project to Tenders: directly and indirectly via Communications. Those tables shouldn't form a relationship loop. Quotes is part of this issue too.

2. Numerated field names. When you start adding number suffixes to field names, its time for a new table. Activities needs a sub-table to hold all its Activities.