I have grown fond of utilizing Microsoft Access 2010 and SharePoint 2010 Enterprise edition to develop fast and effective applications which can be enhanced by Excel's awesome pivot tables for reporting.
I am aware of the Parent - Child relationship which can be established using lookup fields.
I am trying to understand if it is possible to create a Parent - Child - Grandchild relationship which can be utilized within Access 2010? the Problem I run into is the Grandchild relationship. I need to be able to see all fields in one table to export into Excel. Right now I can not see the grandchildren.
Can some one let me know if this is even possible and if so any suggestions?
The most usual way to implement a liked list in an Acess table consist of using two columns: one is an Identity column (Autonumber), let's call it ID and the second works as a Foreign Key column, except that its values (the keys) refer to the ID column of the same table instead of referring to a colum in another table, let's call it P_ID. When P_ID is Null or equals zero (depending on how the table was defined), the row is a parent. When P_ID contains a number greater than zero, the row is a child and it's P_ID value points to the ID value of its parent.
The class Cls_LinkedList in the database in the atached file handles such linked list tables. It has 3 initialization properties:
- TableName that allows to specify the name of the table to be processed.
- PKName that allowws to specify the name of the ID column.
- FKName that allows to specify the name of the P_ID column.
The class Cls_LinkedList has 4 methods that return data from the table, data based on a value from the ID column:
- GetQryChild returns the name of a query that the method creates then it is called. It receives the ID value of a parent row and the query contains that parent row and all the child rows.
- GetQryParent does the same as GetQryChild but it receives the ID value of a child row and returns the name of a query that contains all the parent rows.
- GetRstChild returns a DAO Recordset that is populated with all the parent rows for an ID value.
- GetRstParent returns a DAO Recordset that is populated with all the child rows for an ID value.
The class can be used easily:
Dim cls As Cls_LinkedList
Set cls = New Cls_LinkedList
.TableName(False) = "Tbl_List"
.PKName = "ID"
.FKName = "P_ID"
DoCmd.OpenQuery .GetQryParent(RowID:=29, Persistent:=True)
Set cls = Nothing
The database in the attachment also contains a module that allows to test the class. It has a procedure to create a sample table and two procedures to test the class one using a Recordset and one using a query.
The other file in the attachment is a pdf document that describes the properties and methods of the class Cls_LinkedList with their arguments.