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

[Ken Sheridan]

If we start with a table Parts with columns for it's attributes, each of which must be functionally determined solely by the key:

Parts
....PartID (PK)
....PartName
....Tag

Parts are combined to make assemblies, which is another entity type modelled by a table:

Assemblies
....AssemblyID
....AssemblyName

In each of the above other columns can be added to represent attributes functionally determined by the key.

Each assembly comprises multiple parts and each part (i.e. the type of part not each physical entity of the type) can be a component of multiple assemblies, so the relationship type is many to many. The relationship type is modelled by a table which resolves it into two one to many relationship types:

AssemblyParts
....AssemblyID (FK)
....PartID
....Quantity

When the three tables are joined in a query, each assembly would return multiple rows, one for each constituent part. You say, however, that you want the tags returned as a single string expression. This can be done by returning distinct assemblies in a query and a string of all tags for each assembly by means of a VBA concatenation function. The following is one of my own which uses the highly efficient GetString property of the ADO recordset object. Other similar functions will use DAO, and iterate through the referencing rows in the Parts table to build a string. You'll easily be able to find such functions online:

Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String

    Const NOCURRENTRECORD = 3021
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strList As String
  
    strSQL = "SELECT " & strColumn & " FROM " & strTable & " WHERE " & strFilter & " ORDER BY " & strSortColumn
 
    Set rst = New ADODB.Recordset
  
    With rst
        Set .ActiveConnection = CurrentProject.Connection
        .Open _
            Source:=strSQL, _
            CursorType:=adOpenForwardOnly, _
            Options:=adCmdText
      
        On Error Resume Next
        strList = .GetString(adClipString, , strDelim, strDelim)
        .Close
        Select Case Err.Number
            Case 0
            ' no error so remove trailing delimiter
            ' and return string
            GetList = Left(strList, Len(strList) - Len(strDelim))
            Case NOCURRENTRECORD
            ' no rows in table so return
            ' zero length string
            Case Else
            ' unknown error
            GetList = "Error"
        End Select
    End With
  
End Function

The above assumes that for each assembly you want to record a simple parts list. However, assemblies are generally made of sub-assemblies which are themselves made up of other assemblies and/or atomic base parts. The classic model for this comprises two tables:

Parts
....PartID
....PartName
....Tag

This table covers includes all assemblies, sub-assemblies, and atomic base parts. I've added the last column to satisfy your requirements.

PartStructure
....MajorPart (FK)
....MinorPart (FK)
....Quantity

The two foreign key columns each reference the primary key of Parts. The primary key is a composite of the two foreign keys. This very simple model can define any assembly, whatever its complexity, as, in theory, the recursive nature of the joins allows for an infinite number of levels. To generate a bill of materials for an assembly therefore requires recursive querying. Unlike client server database systems like Oracle, SQL Server etc. this is not supported by Access per se. It can be simulated, however, and I long ago created an Access file which does this by progressively writing rows to a table in an external database created at runtime. I won't go down that road at present, however, as I don't think it's what you are looking for. If I'm wrong let me know and I'll attach the file here.

 

[RonPaii]

If we start with a table Parts with columns for it's attributes, each of which must be functionally determined solely by the key:

Parts
....PartID (PK)
....PartName
....Tag

Parts are combined to make assemblies, which is another entity type modelled by a table:

Assemblies
....AssemblyID
....AssemblyName

In each of the above other columns can be added to represent attributes functionally determined by the key.

Each assembly comprises multiple parts and each part (i.e. the type of part not each physical entity of the type) can be a component of multiple assemblies, so the relationship type is many to many. The relationship type is modelled by a table which resolves it into two one to many relationship types:

AssemblyParts
....AssemblyID (FK)
....PartID
....Quantity

When the three tables are joined in a query, each assembly would return multiple rows, one for each constituent part. You say, however, that you want the tags returned as a single string expression. This can be done by returning distinct assemblies in a query and a string of all tags for each assembly by means of a VBA concatenation function. The following is one of my own which uses the highly efficient GetString property of the ADO recordset object. Other similar functions will use DAO, and iterate through the referencing rows in the Parts table to build a string. You'll easily be able to find such functions online:

Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String

    Const NOCURRENTRECORD = 3021
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strList As String
 
    strSQL = "SELECT " & strColumn & " FROM " & strTable & " WHERE " & strFilter & " ORDER BY " & strSortColumn
 
    Set rst = New ADODB.Recordset
 
    With rst
        Set .ActiveConnection = CurrentProject.Connection
        .Open _
            Source:=strSQL, _
            CursorType:=adOpenForwardOnly, _
            Options:=adCmdText
     
        On Error Resume Next
        strList = .GetString(adClipString, , strDelim, strDelim)
        .Close
        Select Case Err.Number
            Case 0
            ' no error so remove trailing delimiter
            ' and return string
            GetList = Left(strList, Len(strList) - Len(strDelim))
            Case NOCURRENTRECORD
            ' no rows in table so return
            ' zero length string
            Case Else
            ' unknown error
            GetList = "Error"
        End Select
    End With
 
End Function

The above assumes that for each assembly you want to record a simple parts list. However, assemblies are generally made of sub-assemblies which are themselves made up of other assemblies and/or atomic base parts. The classic model for this comprises two tables:

Parts
....PartID
....PartName
....Tag

This table covers includes all assemblies, sub-assemblies, and atomic base parts. I've added the last column to satisfy your requirements.

PartStructure
....MajorPart (FK)
....MinorPart (FK)
....Quantity

The two foreign key columns each reference the primary key of Parts. The primary key is a composite of the two foreign keys. This very simple model can define any assembly, whatever its complexity, as, in theory, the recursive nature of the joins allows for an infinite number of levels. To generate a bill of materials for an assembly therefore requires recursive querying. Unlike client server database systems like Oracle, SQL Server etc. this is not supported by Access per se. It can be simulated, however, and I long ago created an Access file which does this by progressively writing rows to a table in an external database created at runtime. I won't go down that road at present, however, as I don't think it's what you are looking for. If I'm wrong let me know and I'll attach the file here.

I have seen "Recursive" query done in Access; It's ugly but works. Basically you have a query for each possible level, outputting the quantity to a level column Q1 for level1, Q2 for level 2. So if you allow 10 levels, you need 10 queries. They are then combined in 1 union query.

Using a recursive algorithm and a temp table is far superior.

 

[Ken Sheridan]

Using a recursive algorithm and a temp table is far superior.

Which is exactly what my BoM file does. I first built it around 25 years ago using one of the standard algorithms published by Chris Date in the Introduction to Database Systems, but having generated the BoM I wanted to aggregate the weights of the atomic base parts which made up the sub-assemblies and assemblies. I wrote another algorithm to do this, but realised that this new algorithm also generated the BoM itself, so I was able to discard the original algorithm and do the whole thing in one operation.

 

[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.

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.

Yes, but it's one 'machine' that is made of different parts (LI, PT, TI....etc). and yes, I'm trying to create a final table (TAGS) that lists all the parts and relates their 'tag' id. Later I hope create a form using TAGS table as source to look up all the TAGS to then see all the relevant information of the 'parts'.

 

[malcolm3000]

If we start with a table Parts with columns for it's attributes, each of which must be functionally determined solely by the key:

Parts
....PartID (PK)
....PartName
....Tag

Parts are combined to make assemblies, which is another entity type modelled by a table:

Assemblies
....AssemblyID
....AssemblyName

In each of the above other columns can be added to represent attributes functionally determined by the key.

Each assembly comprises multiple parts and each part (i.e. the type of part not each physical entity of the type) can be a component of multiple assemblies, so the relationship type is many to many. The relationship type is modelled by a table which resolves it into two one to many relationship types:

AssemblyParts
....AssemblyID (FK)
....PartID
....Quantity

When the three tables are joined in a query, each assembly would return multiple rows, one for each constituent part. You say, however, that you want the tags returned as a single string expression. This can be done by returning distinct assemblies in a query and a string of all tags for each assembly by means of a VBA concatenation function. The following is one of my own which uses the highly efficient GetString property of the ADO recordset object. Other similar functions will use DAO, and iterate through the referencing rows in the Parts table to build a string. You'll easily be able to find such functions online:

Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String

    Const NOCURRENTRECORD = 3021
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strList As String
 
    strSQL = "SELECT " & strColumn & " FROM " & strTable & " WHERE " & strFilter & " ORDER BY " & strSortColumn
 
    Set rst = New ADODB.Recordset
 
    With rst
        Set .ActiveConnection = CurrentProject.Connection
        .Open _
            Source:=strSQL, _
            CursorType:=adOpenForwardOnly, _
            Options:=adCmdText
     
        On Error Resume Next
        strList = .GetString(adClipString, , strDelim, strDelim)
        .Close
        Select Case Err.Number
            Case 0
            ' no error so remove trailing delimiter
            ' and return string
            GetList = Left(strList, Len(strList) - Len(strDelim))
            Case NOCURRENTRECORD
            ' no rows in table so return
            ' zero length string
            Case Else
            ' unknown error
            GetList = "Error"
        End Select
    End With
 
End Function

The above assumes that for each assembly you want to record a simple parts list. However, assemblies are generally made of sub-assemblies which are themselves made up of other assemblies and/or atomic base parts. The classic model for this comprises two tables:

Parts
....PartID
....PartName
....Tag

This table covers includes all assemblies, sub-assemblies, and atomic base parts. I've added the last column to satisfy your requirements.

PartStructure
....MajorPart (FK)
....MinorPart (FK)
....Quantity

The two foreign key columns each reference the primary key of Parts. The primary key is a composite of the two foreign keys. This very simple model can define any assembly, whatever its complexity, as, in theory, the recursive nature of the joins allows for an infinite number of levels. To generate a bill of materials for an assembly therefore requires recursive querying. Unlike client server database systems like Oracle, SQL Server etc. this is not supported by Access per se. It can be simulated, however, and I long ago created an Access file which does this by progressively writing rows to a table in an external database created at runtime. I won't go down that road at present, however, as I don't think it's what you are looking for. If I'm wrong let me know and I'll attach the file here.

I'm looking for something close to what you are describing but not as in depth. I don't need to generate a BOM (bill of material) for now but it's good to know for future databases.

I have a plant that generates a final liquid solution. I want a database that breaks the plant's sensors, equipment into their own tables (LI, PT, TI; Level Indicator, Pressure Transducer, Temperature Indicator). I can assign the TAGid in these tables. but then want a final table 'machine' to consolidate all the TAGids so I can create a form from the final table, 'machine' and easily look up the TAGid to then get all the details on the equipment ('parts').

 

[KitaYama]

Parts
....PartID (PK)
....PartName
....Tag

Assemblies
....AssemblyID
....AssemblyName

AssemblyParts
....AssemblyID (FK)
....PartID
....Quantity

In your suggested model, what happens when an assembly can be used as a part in another assembly? (sub assembly)
How do the relationships look like?
For example, a manufacturer can assemble several parts and make a cabrator and sell it as an stand alone part, or can use the caburator as a sub assembly in a car engine. Do you register the caburator in both parts and assembly tables?

 

[RonPaii]

Yes, but it's one 'machine' that is made of different parts (LI, PT, TI....etc). and yes, I'm trying to create a final table (TAGS) that lists all the parts and relates their 'tag' id. Later I hope create a form using TAGS table as source to look up all the TAGS to then see all the relevant information of the 'parts'.

Your (TAGS) list is a bill of material.

It makes no sense to have multiple tables for your parts. You should have a "parts" table listing the parts in a "machine". One of the columns of the table will be the TAG for that part. Assuming multiple parts can have the same TAG, you would also have a "tag" table with a one to many relationship with the "parts" table. You will also have a "as-built" bill of material table with records for each machine in a "as-built BOM" table linked to a "machine" table. You will quickly find that the machines have sub-assemblies, some of which are used in other machines and will need a "BOM" table linked to your "parts" table to simplify creating the "as-built" for a machine.

We have this structure for building customized conveyor equipment. Each new part and assembly is given a part number when the engineering model is first created for the new part. Meta data is pulled from the model or from properties of the part. This allows quick lookup of the part for the machine or reuse with other machines.

 

[Ken Sheridan]

For example, a manufacturer can assemble several parts and make a cabrator and sell it as an stand alone part, or can use the caburator as a sub assembly in a car engine. Do you register the caburator in both parts and assembly tables?

There is no Assemblies table, only the Parts and PartStructure tables. The former contains all 'parts' i.e the complete assembly, all sub-assemblies and all atomic base parts. PartStructure models a binary many to many relationship type between two instances of the parts table:

The following image is of the main form from a little demo file which illustrates the structure of Product1:

Whereas the following illustrates the structure of Assembly2:

ISP = 'Immediate sub-part', i.e. the component is used directly as a part of the selected product or assembly, rather than (or as well as) by virtue of being a part of a sub-assembly in the selected product or assembly.

A function encodes the algorithm which generates the bill of materials from the two tables. The function creates and populates a temporay BoM table in a temporary external database to which the current database is linked. A query then returns the BoM in a format suitable for presentation to the user in the form illustrated above, and in a report:

The SQL for the query is:

SELECT
    Parts.PartNum AS MajorPartNum,
    Parts.PartName AS Majorpart,
    Parts_1.PartName AS MinorPart,
    BoM.Quantity
FROM
    (
        Parts
        LEFT JOIN BoM ON Parts.PartNum = BoM.MajorPartNum
    )
    LEFT JOIN Parts AS Parts_1 ON BoM.MinorPartNum = Parts_1.PartNum;

Having said all that, I don't think this is what the OP wants. As far as I can see they have a simple binary relationship type between assemblies and parts.