Different tables depending in value

[Thim]

Hi,



I would like to know if it would je possible to link different table depending on a value?

For example:
Keep track of machinery properties
Pump & piping
I think in the first table you give general data and say which type. Depending on the type you link to the correct table with the properties for specific machinery.

Is this in someway possible?

Greetings

[ranman256]

Link both, then when you pick the type, load that query/table.
put it in a combo box

combo box fields, bound column = 2: caption, tbl
hammer, tblHammers
nails, tblNails

in the combo , user picks Hammer in combo box,
then it connects to the tbl
me.recordsource = cboBox

[Thim]

Thanks for the respons.
Could you explain it a bit more? I’m rather new to access.

[Micron]

Equipment/Machinery types should not be allocated to different tables. If you have pumps, motors, valves, etc. it may be that each should be housed in tblEquipment which has a field to denote equipment type - and the "type" field values likely should be foreign key values from tblEquipmentTypes. If that makes no sense you might need to review database normalization.

[Thim]

Oke
I'm not so familiair with the terms you use.
So do you mean that you make for each equipment type a different colloum? And then link the different colloums?
Or do you mean something else?

[Thim]

Could you conform that I understand you well?
I have a tblData in which I have general data + the type defined.

Then I have a tbl for each equipment type.

In between I have a tblequipmentType. Correct?
How schould this last table look like? Field(coulom) per type?

[mike60smart]

Hi Thim

Are you saying that when you select a specific Type using a Combobox then the 2nd Combobox would display a list of Equipments related to the Type selected?

If this is the case then you need to look at Cascading Combobox's

[Thim]

maybe i have to give some more background.
First my english isn't that great :-)
I work in a Chemical plant. And i want to store data from all equipments. (Assets, >10000)
At the moment we store some data from different equipments in different excels.
This makes that you need to input often double data in the different excels and when you change something you need to change it in the different excels. I think it is not users friendly to maintain the data.
So i was looking in Access if it would be possible to store data there and just work with querys and links to link the different data.
The idea is to start with a table in which is stored general data (tblData), like tagnumbering, SAP links (function location), cost input, Asset Replacement value, description, etc.
So i started with 1 type of asset, storage tank(tblTank), in which i store data from that type of equipment, and from there on i linked it to RBI (Risk based inspections) for corrosion follow up.
This works great.
Next step would be for example to add heat exchangers in this database, offcourse for an heat exchanger the data i want to store is different as for a storage tank. So i was wondering if i would be able to make in the first table (tblData) some sort of selection, whatever it looks like, that i can choose which type of equipment this is, so it will select the corresponding table for that specific type of equipment.
In the end i will probably end up with some 30-50 types of equipment.
The problem is that i don't have the experiance in Access to make this work, or i don't see it yet how it is been done.
Maybe there is an other way that i'm not familiar with and can this be archived in a very easy way in acces.

[Micron]

I think what you might be describing is a situation where you have various equipment types (piping, tanks, pumps, etc.). When you want to record inspection data the problem arises where you must do testing or take readings that don't apply to all of them. For example, you would not take sonic measurements for thickness on a pump but you would on a tank. So that leaves you with the notion that you should have a table for every type and include its measurement requirements in each of those tables. I'd say that's wrong if for no other reason that there would be duplicated measurement fields all over the place. That is to say, several tables would have fields for temperature, and several for vibration and so on - not good. Also, generally speaking, any table should be about the entity as you define it and its fields should be about the attributes. This means that inspection data does not belong in equipment tables since inspections should be regarded as their own entity. One reason for this is that you would repeat all the equipment data for every inspection record (serial number, location, capacities, etc.) - also not good.

You should research db normalization and make sure you understand as much as possible because you're starting with one of the more difficult normalization tasks. What you will likely end up with is a bunch of tables that break things out into various entities so that you can get them into rows instead of columns. Then you'll need junction table(s) to bring things together. This is commonly explained using student/class examples. In your case I think the challenge won't be equipment vs equipment types, but rather inspections that include measurements for some equip types but not others. You'd need a table for inspections (date, where, by who, etc.) and measurement types (pressure, temp, thickness, etc.) and a junction to bring them together in rows.

Start with normalization, don't start building tables right away. Then read how to name things. Then get pencil, eraser and large sheets of paper and plan table/field design and relationships and try testing whether or not it looks good in your mind. Then maybe build tables and relationships and post a pic of your relationships for feedback. Expect questions.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html

[Thim]

Thx. it is sort of correct what you say. The inspection reports are in a simulair problem that will happen.
I was think of really working in Asset types but like you explain you work on properties.
But don't you risk then of ending up with all tables with only 1 coulom in it?
Is that a problem?
Because for example.
Volume can be a propertie of an heat exchanger and a storage tank and a reactor and a....
So if i understand it well, you really work in the tables, and then you will try to make reports or whatever through the query's?
And don't matter where the data is only use the querys to consultate the data?

Thx for the links, i will read them coming days.

[pbaldy]

Post 5 was moderated, I'm posting to trigger email notifications. It looks like you were trying to attach something:

https://www.accessforums.net/showthread.php?t=70301

[Micron]

But don't you risk then of ending up with all tables with only 1 coulom in it?

Not likely. Study normalization then ask question when you have some background. You might want to write down questions you have when trying to relate your situation to the normalization topics and examples shown. That way, you might be more receptive to adapting the study material.

[ArviLaanemets]

I work in a Chemical plant. And i want to store data from all equipments. (Assets, >10000)
... The idea is to start with a table in which is stored general data (tblData), like tagnumbering, SAP links (function location), cost input, Asset Replacement value, description, etc.

OK so long, but tblData is too general. tblAssets or tblEquipment or etc. is better.
tblAssets: AssetID, AssetType, AssetProducer, AssetMark, AssetModel, SerialNo, AssetDescription, Ownership (e.g. 1 for purchased, 2 for leased, etc.), AquiredDate, OriginalValue, ...;
Then you need to register, where every asset is located, and who is responsible. Usually such info can change over time, so you need log table(s) for such info. E.g.
tblAssetLocations: AssetLocationId, AssetID, LocationId, ValidAtDate;
tblLocations: LocationID, LocationDescription, ...;
tblAssetResponsibles: AssetResponsibleID, AssetID, ResponsibleID, ValidatDate;
tblResponsibles: RespionsibleID, [ResponsibleName]/[Forename, Lastname] (Reponsibles may be either persons or non-persons like departments, or both of them - it depends on your in-site setup. And probably you have to register some 'fake' responsibles, like free, or broken, or trashed, or sold, or whatever else you need too),...;

So i started with 1 type of asset, storage tank(tblTank)...

In tblAssets, you have a field Type. Now you need a table, where all those various types are defined:
tblTypes: Type (I advice to have it an integer value), TypeName;

, in which i store data from that type of equipment...

I assume you have a very wide selection of various equipment, and every type of it is charactierized with different set of various properties. So:
tblAssetProperties: AssetPropertyId, AssetType, PropertyID;
tblProperties: PropertyId, PropertyDescription; PropertyValueType (PropertyValueTypes may be e.g. 1 for integer, 2 for double, 3 for boolean, 4 for short string, 5 for long string, etc.)
tblAssetPropertyValues: AssetPropertyValueID, AssetId, AssetPropertyId, IntegerValue, DoubleValue, BooleanValue, ShortStringValue, LongStringValue, ... (Depending on PropertyValueType of property, one of value fields is filled. Another possible way is to have separate tables for every PropertyValueType. With either of solutions, on form where property value(s) is/are edited or displayed, only value of type determined by PropertyValueType is visible and accessible).

... and from there on i linked it to RBI (Risk based inspections) for corrosion follow up.

So additional tables again:
tblInspections: InspectionId, InspectionDate, AssetId, InspectionComment, InspectionResult, ...;
tblInspectionSteps: InspectionStepID, InspectionID, AssetPropertyId, ..., InspectionResult (When all InspectionResults of all InspectionSteps of given Inspection are positive, the InspectionResult of this Inspection can be set as positive. But of-course you can drop InspectionResult in tblInspections entirely, and calculate the status of any inspection from tblInspectionSteps on fly.)

[Thim]

In tblAssets, you have a field Type. Now you need a table, where all those various types are defined:
tblTypes: Type (I advice to have it an integer value), TypeName;

I assume you have a very wide selection of various equipment, and every type of it is charactierized with different set of various properties. So:
tblAssetProperties: AssetPropertyId, AssetType, PropertyID;
tblProperties: PropertyId, PropertyDescription; PropertyValueType (PropertyValueTypes may be e.g. 1 for integer, 2 for double, 3 for boolean, 4 for short string, 5 for long string, etc.)
tblAssetPropertyValues: AssetPropertyValueID, AssetId, AssetPropertyId, IntegerValue, DoubleValue, BooleanValue, ShortStringValue, LongStringValue, ... (Depending on PropertyValueType of property, one of value fields is filled. Another possible way is to have separate tables for every PropertyValueType. With either of solutions, on form where property value(s) is/are edited or displayed, only value of type determined by PropertyValueType is visible and accessible).

Hi,
Thanks alot for all the reponses. I didn't had much time for continueing this database during the end of last year.
I have done some reading and i figured it out. It is setup now as Arvilaanements explains. Wasn't so clear when i readed it at first.
I restarted with this and i was wondering if there were benefits when using one or the other for PropertyValueTypes.
Why do you advice an integer for tblTypes:Types? This i don't see yet.

Greetings

[mike60smart]

Hi
Whenever you have a table that contains a list of names, in your case Types, then the recommended table structure is as follows:-

tblTypes
-TypeID - Autonumber - PK
-Type - Text

Then on your Data Input Form you create a Combobox that looks up the values from tblTypes and you Store the PK Value in the Bound Table field.