Database structure quick check

[Alexandre P. L.]

Hello,

I have started a database project over at work to record operational data in order to reduce paperwork and increase data analysis possibilities. I am having issues building forms and I think it may be because of an invalid / inefficient database structure / relations. Could someone perhaps just give a quick look to my database diagram and tell me if there is an obvious problem with it ? It is not very large (12 tables, 12 links) It would be awesome and I would be very thankful !

My take on it is that there are a few superfluous fields (Name / Description) and/or that my use of primary keys is not correct. When I try to build forms, if I do not use textual primary keys with a meaningful name ("T-LEAK-DISCHARGE" for example) I cannot use a field with more meaningful values than numbered primary keys as a way to refer to a specific item. One way to achieve this would be to make that field a primary key but it will be the same as what I did. Is it possible to have 2 primary keys ? That does not seem to make sense. Also that would imply that I would need double links between some tables which seems inelegant at best.

A quick translation of a few french words to english (the db is in french):
Bris = Breakage
Donnee = Data
Nom = Last name
Prénom = First name

Many thanks

 

[NickHa]

Hi
Looking at ‘Departement’, I suggest you change the keys to numeric, leaving the data (‘Nom’) as-is. In your example form, the drop-down box should have two fields, rather than the one as coded. Thus the Row Source should be “SELECT IDDepart, Nom FROM Departement;” (thus returning the primary key 'key' and its associated text 'value'. The Bound Column must be 1, which indicates the value used by Access to link to the table row. In the Format tab of Properties, set Column Count to 2 and Column Widths to '0; 3cm'. This causes both columns to be returned, but the first (IDDepart) is not visible because of the zero width. This means the user selects from the data values shown in the box, but Access knows it is the key value which matters. This same principle applies to the relationships between tables as well.
Nick H

 

[tam1310]

I'm not sure exactly what you are asking but you might try using some lookup fields. For example in your TypeTest table you could add an autonumber field for the primary key. Then, in design view, change the IDTypeTest field in the Test table to a lookup and choose to display the IDTypeTest and Description fields and store the primary key field from TypeTest.

 

[boblarson]

I'm not sure exactly what you are asking but you might try using some lookup fields. For example in your TypeTest table you could add an autonumber field for the primary key. Then, in design view, change the IDTypeTest field in the Test table to a lookup and choose to display the IDTypeTest and Description fields and store the primary key field from TypeTest.

Lookups at table level are NOT recommended (for the following reasons:
http://www.mvps.org/access/lookupfields.htm )