I'm sure there is a simple solution asset tracking MY NEW PROJECT. (2 Viewers)

[malcolm3000]

hello,

new to access and access forum and i'm looking to create a simple database but can't seem to do it correctly.

my situation;

I want to create a database that has multiple tables for different parts. example, the pressure transducers, level sensors, temperature sensors. all separate, however, they are all installed into one piece of machinery. this machinery has a diagram with each part having it's unique tag. for example PT-1 (pressure transducer 1), TI-1 (temperature indicator 1), LI-1 (level indicator1). Then i wish to create a form or query to be able to look up parts on a drop down list to pick either PT-1 or TI-1. my issue is that i can't get the dropdown list (combobox) to list all 3 tables. i've tried a joint table, but can't seem to get it working. it creates 3 fields. but the tags should only be on one field on the main table. later once i get over this hurdle, i will have the information for all parts tied into the vendor that we buy from. i think i can handle this once i get over how to search all tables for the tags.

I'll take all ideas, suggestions.

Thanks,

Malcolm.

 

[theDBguy]

If you're new to relational database design, you might look into Normalization Theory and Principles. I wouldn't create separate tables for each part. Doing that might help solve your issue.

 

[malcolm3000]

DBGuy,

I'll look into it. But there will be many LIs and PTs in their corresponding tables (it's not for ONE part). LI-1, LI-2, LI-3 etc. and will be located in many places across the plant. The same will happen with the pressure transducers. BTW, here is what I have done, but again, I can't get the TAGs to add up into a single field in the JointTable. So in the JoinTable, It shows two separate fields listing LI and PT numbers--I want to combine them into one field if this is possible. It will be a short text field.

 

[theDBguy]

But there will be many LIs and PTs in their corresponding tables (it's not for ONE part). LI-1, LI-2, LI-3 etc. and will be located in many places across the plant. The same will happen with the pressure transducers.

If I understand that correctly, a normalized structure might be something like this:

tblPartTypes
TypeID, pk
TypeDesc

tblParts
PartID, pk
TypeID, fk

tblEquipment
EquipID, pk
Desc
etc.

tblEquipParts
EquipPartID, pk
EquipID, fk
PartID, fk

 

[tvanstiphout]

Good for you for asking questions early on. Getting the database design correct is an absolutely essential step.

What I am hearing is that you have machines, and each machine has zero or more parts, and those parts can be of different kinds (e.g. LI or PT).
You need a table to list the machines.
Another for parts. It has a CategoryID field.
A table for Categories (such as LI, PT)
And lastly a table to join Machines and Parts in a many-to-many relation. That table has Tag as one of its fields.

 

[Steve R.]

As a follow-up to the concept of "normalization", the link below provides a very good on-line tutorial.
Please be aware that the tutorial below refers to "Base", a Linux database. The "normalization" concepts presented in the tutorial still apply to the designing an MS Access database. Enjoy and good luck.

Base Tutorial: From Newbie to Advocate in a one, two... three!

 

[malcolm3000]

If I understand that correctly, a normalized structure might be something like this:

tblPartTypes
TypeID, pk
TypeDesc

tblParts
PartID, pk
TypeID, fk

tblEquipment
EquipID, pk
Desc
etc.

tblEquipParts
EquipPartID, pk
EquipID, fk
PartID, fk

DB (thanks for quick responses!), sure, Ill breakdown the equipment as you probably have shown. I haven't gotten that far as far as what you are showing (you can have to FK's--foreign keys in a table?).

Here is my first issue I'm trying to overcome, I want the unique TAG (short text) fields in both LT and PT tables to be consolidated into one field in the JointTable. The the tags will be unique (LI-1, PT-1, etc). I'm reviewing videos on Normalizing data but I believe (hope?) there is a quicker, solution than running a query to append the fields into one.

I'm not sure of your background but below is a drawing (P&ID) of the parts that are tagged. So, the facilities will have all the parts, equipment in the JointTable--this will be the TAGS field. The individual tables will be hold the detailed information.

The end result will be that in the JoinTable, I can create a form and simply scroll down the TAG field then have all the part information displayed. Part details, then vendors. I want to be able to separate the parts by their type (Level Indicator, Pressure Transducer, etc).

 

[DHookom]

We had a similar normalized table structure at a former employer. We added an attribute table to store different characteristics about a Part. For instance the attributes common for hard drives might be manufacturer, size, type, etc. A printer would have paper size, brand, model, ink/laser, etc. We could then create a junction table of PartAttributes.

 

[malcolm3000]

Good for you for asking questions early on. Getting the database design correct is an absolutely essential step.

What I am hearing is that you have machines, and each machine has zero or more parts, and those parts can be of different kinds (e.g. LI or PT).
You need a table to list the machines.
Another for parts. It has a CategoryID field.
A table for Categories (such as LI, PT)
And lastly a table to join Machines and Parts in a many-to-many relation. That table has Tag as one of its fields.

Tom, yes. that is exactly what i'm trying to do. Earlier in my attempts I was able to add a vendor table, for the parts. But I'm stuck on having all the Partid (TAGS) in one field--JointTable.

 

[RonPaii]

DB (thanks for quick responses!), sure, Ill breakdown the equipment as you probably have shown. I haven't gotten that far as far as what you are showing (you can have to FK's--foreign keys in a table?).

Here is my first issue I'm trying to overcome, I want the unique TAG (short text) fields in both LT and PT tables to be consolidated into one field in the JointTable. The the tags will be unique (LI-1, PT-1, etc). I'm reviewing videos on Normalizing data but I believe (hope?) there is a quicker, solution than running a query to append the fields into one.

I'm not sure of your background but below is a drawing (P&ID) of the parts that are tagged. So, the facilities will have all the parts, equipment in the JointTable--this will be the TAGS field. The individual tables will be hold the detailed information.

The end result will be that in the JoinTable, I can create a form and simply scroll down the TAG field then have all the part information displayed. Part details, then vendors. I want to be able to separate the parts by their type (Level Indicator, Pressure Transducer, etc).

View attachment 122590

It looks like something you could do in a CAD package like AutoCAD or Draftsight. Both have programming tools that allow you to build in blocks, linking objects together. Access can be used to store history and purchasing data using common object ID. AutoCAD add-on like AutoCAD electrical use MDB files for data.

 

[tvanstiphout]

Tom, yes. that is exactly what i'm trying to do. Earlier in my attempts I was able to add a vendor table, for the parts. But I'm stuck on having all the Partid (TAGS) in one field--JointTable.

The junction table (we could call it MachineParts) with the Tag field will likely have several records, one for each machine/part combination. That is good!
Your desire to have all the tags in one field is likely because you are thinking ahead, to a time where you're creating a report with all tags for each machine.
That is a solvable problem for later. First, we want to organize the data in the way imposed by relational database design rules (i.e. normalization).

 

[mike60smart]

DB (thanks for quick responses!), sure, Ill breakdown the equipment as you probably have shown. I haven't gotten that far as far as what you are showing (you can have to FK's--foreign keys in a table?).

Here is my first issue I'm trying to overcome, I want the unique TAG (short text) fields in both LT and PT tables to be consolidated into one field in the JointTable. The the tags will be unique (LI-1, PT-1, etc). I'm reviewing videos on Normalizing data but I believe (hope?) there is a quicker, solution than running a query to append the fields into one.

I'm not sure of your background but below is a drawing (P&ID) of the parts that are tagged. So, the facilities will have all the parts, equipment in the JointTable--this will be the TAGS field. The individual tables will be hold the detailed information.

The end result will be that in the JoinTable, I can create a form and simply scroll down the TAG field then have all the part information displayed. Part details, then vendors. I want to be able to separate the parts by their type (Level Indicator, Pressure Transducer, etc).

View attachment 122590

Have you an example in Excel of 1 Piece of machinery with all associated Parts?

 

[malcolm3000]

Have you an example in Excel of 1 Piece of machinery with all associated Parts?

Mike,

Here is a snapshot of the excel file I started with. Note column B has the Tags, TI, PI, LI. These tags have their own tables in access. In access I plan to have other tables for some of the other columns shown. For example (Vendor--not shown below) will be a separate table. The tags 'parts' will be linked to the vendor that we purchase the parts from.

 

[malcolm3000]

Here are the tables, including the JointTable. Notice that the FK for Level, and Pressure have the same ID. I'm trying to get them to have individual IDs. Below, both LTID and PTID all have the same JointTableID.

 

[mike60smart]

Mike,

Here is a snapshot of the excel file I started with. Note column B has the Tags, TI, PI, LI. These tags have their own tables in access. In access I plan to have other tables for some of the other columns shown. For example (Vendor--not shown below) will be a separate table. The tags 'parts' will be linked to the vendor that we purchase the parts from.

View attachment 122611

Hi
So all 3 rows belong to 1 peice of Machinary?

 

[mike60smart]

Here are the tables, including the JointTable. Notice that the FK for Level, and Pressure have the same ID. I'm trying to get them to have individual IDs. Below, both LTID and PTID all have the same JointTableID.



View attachment 122612

Are you able to upload a copy of your actual database?

 

[malcolm3000]

Are you able to upload a copy of your actual database?

Mike,

Here it is. I've been picking at it when I get the chance. I'm reviewing 'normilization' which is what I think is an approach. I'm also playing with queries but none give me the results I need. ONE TAG to ONE PART (Level or Pressure, etc)..

Perhaps I don't need a Joint Table but create a Query that has the Level and Pressure tables referenced. I could then create a form to look the TAG then create subforms to give me the information.

 

[mike60smart]

Mike,

Here it is. I've been picking at it when I get the chance. I'm reviewing 'normilization' which is what I think is an approach. I'm also playing with queries but none give me the results I need. ONE TAG to ONE PART (Level or Pressure, etc)..

Perhaps I don't need a Joint Table but create a Query that has the Level and Pressure tables referenced. I could then create a form to look the TAG then create subforms to give me the information.

From the Relationship diagram it is difficult to determine what in fact you need to do.

Do you want to be able to Select a Tag and then enter details of the Pressure Transducer as well as details of the Level Transmitter?

 

[mike60smart]

Mike,

Here it is. I've been picking at it when I get the chance. I'm reviewing 'normilization' which is what I think is an approach. I'm also playing with queries but none give me the results I need. ONE TAG to ONE PART (Level or Pressure, etc)..

Perhaps I don't need a Joint Table but create a Query that has the Level and Pressure tables referenced. I could then create a form to look the TAG then create subforms to give me the information.

Hi
If I create a Form which allows you to select a Tag what data would you then want to be able to add?
The attached screenshot allows me to select a Tag, and then add details about a specific Level Transmitter and Pressure Transducer associated with the Tag selected.

 

[malcolm3000]

From the Relationship diagram it is difficult to determine what in fact you need to do.

Do you want to be able to Select a Tag and then enter details of the Pressure Transducer as well as details of the Level Transmitter?

No.

What i'm trying to do is have separate tables feed into one table (I'll call it JoinT). PT (Pressure Transducer) and LT (Level Transmitter) are separate tables that have their TAG fields. Each TAG is unique (there is only one TAG number per part). So later I can use the JoinT to look up the TAG value for both PT and LT parts--I will then create form(s) to look up details using the TAG field. Eventually I will add more tables for example the Flow Meters (FL)s.

Hope this makes sense.