CODEWITHSUNDEEP
sqldatabasems-access
W0lfw00ds
W0lfw00ds

Reputation: 2096

Microsoft Access: many tables and keys

I'm not much of an database expert, and our company wants to optimize our database usage if it's possible. The problem is; we have many tables that create a long road to the correct information, like this:

tblPIECES -> tblMATERIAL -> tblTEMPERATURES

tblPIECES has a unique id, which is connected to tblMATERIAL's same unique id. tblMATERIAL includes many columns, which include new keys and relations to other tables, such as tblTEMPERATURES. So the key is different than before, but the keys are related to each other.

The question is; is there any way to shorten the select command for this kind of situation? Now I need to:

  1. Select temperaID from tblMATERIAL where pieceID is.. pieceID
  2. Select maxtemperature from tblTEMPERATURES where temperaId is temperaID

This is how I access the temperature information. We use relationships to delete things, like: if you remove the piecesID, it also removes the materials references to that key, and then they are removed from temperatures due to material removal.

Is there any shorter reference to all temperatures which are connected to a given pieceID's temperaID?

Some of the items contain only one match, some contain multiple items while using the same key. The problem here is the key which changes between these tables, and we need an additional query to get it first.

We also thought about inner joining, and it was one of the answers to this problem, but it's sometimes too heavy of a command since somethimes we have more than 3+ tables linked together this way, and the key changes.

They are hardly to change the MS ACCESS database's structure.

Upvotes: 0

Views: 106

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8404

This sounds like proper data normalization to me. It also sounds like your tables are in proper relationships, and those relationships enforce referrential integrity. In short; this sounds like a model database. Unfortunately, if you want to keep data in its optimal format, querying it does require a little work. However, once you set up a few basic queries, all future queries should only be pointing to these "base" queries and not to tables.

Upvotes: 3

Related Questions