I'm sure there is a simple solution asset tracking MY NEW PROJECT. (4 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?