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

 

[KitaYama]

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:

View attachment 122622

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

View attachment 122623

Whereas the following illustrates the structure of Assembly2:

View attachment 122624

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:

View attachment 122625

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.

To prevent this thread going off track and being highjacked, I've put my thoughts in spoiler section to let you know how I think. Please don't reply. I will add a new thread for my case and will ask for your advice there.
Thanks.

Spoiler: hidden reply

I was more wondering how your order table looks like. In case of your above images, if you receive an order for Product1, how the OrderDetails looks like?
Does it contain only 5 records (Assembly2 , Assembly3, Assembly4, Part1, Part2)
Or
(All parts in Assembly2) and (All parts in Assembly3) and (All parts in Assembly4) and Part1 and Part2.

If it's the second method, What happens if Assembly2 contains Part1 too. Do you save two records for Part1 or do you save one record and change its quantity.

 

[Ken Sheridan]

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').

It's important to remember that, in a relational database there are two types of tables, base tables and the result tables of queries. It's also important to understand that a fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way. By having separate tables for different classes of equipment data would be encoded as table names, in contravention of the Information Principle.

Tables model entity types. As far as I can see you have a simple binary many to many relationship between two entity types, lets call them Assemblies and Parts, so you just need those two tables. Tag is an attribute of Parts, so is a column in the Parts table. The relationship type between Assemblies and Parts is modelled by a 'junction' table which resolves it into two one to many relational types by having two foreign keys columns, referencing the primary keys of Assemblies and Parts respectively. This table can also include non-key columns referencing attributes of the relationship type.

The usual user interface for a binary relationship type is a form with a subform embedded in it. In your case the form would be based on Assemblies and the subform on a query which joins the 'junction' table and the Parts table. The subform would contain a combo box bound to the PartID foreign key column, along with a text box bound to Tag column from the referenced Parts table. The text box would be made read-only by setting their Locked property to True (Yes). The combo box would be set up so that its RowSource's first column (PartID) is hidden and the user would select from a list of PartName values returned in its second column.

I'm attaching a little demo file which illustrates this sort of basic many to many relationship type. The demo illustrates a number of different interfaces, but you should use that of a form/subform. It is code free apart from that for the Course combo box's NotInList event procedure, so is very easy to implement:

Private Sub CourseID_NotInList(NewData As String, Response As Integer)

    Dim ctrl As Control
    Dim strSQL As String, strMessage As String
    
    Set ctrl = Me.ActiveControl
    strMessage = "Add " & NewData & " to list?"
    
    strSQL = "INSERT INTO Courses(CourseName) VALUES(""" & _
            NewData & """)"
  
    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
        ctrl.Undo
    End If
    
End Sub

With this physical model it is very easy to build interfaces for retrieving data. For instance you might have an unbound dialogue form in which one or more tags can be selected in a multi-select index, on the basis of which all assemblies which include parts with any or all of the selected tags can be returned. I'm attaching another little demo file which illustrates this.