Table of Tables?

[kman42]

I have a bunch of tables (lets call them modules) that each contain different types of data (except for a couple of shared fields, like Date_of_Report and Report_Type). I then have a table of projects, which have associated records in subsets of the various module tables. So for instance, project 1 could have records in module tables A, C, E; project 2 could have records in module tables A, B, C; project 3 could have records in module tables D, E, F; etc.

New module tables can be added at any time. Within a project, how do I get a single list of Date_of_Report and Report_Type on a subform of all the data records associated with that project, regardless of which module they are in?

It occurred to me that I would need a table associating the projects with the Modules they are linked to, so I created a table called Project_Modules that has project IDs and Module IDs. Now I can get a subform on a project record that shows which modules it is associated with, but I can't figure out how to get the actual data records displayed.

Thanks!
kman

[kennejd]

If I read this correctly...you're trying to display the data field that is associated with the Project and Module IDs?

I can think of a couple of options:
1 option is to use a combo box instead of a text field....just follow the wizar.

Another option is to use a query for the recordsource of your subform. Then you link to from the Project_Module table to the associated Project and Module tables. Select the data fields you want and add to your subform.

Jim

[jzwp11]

Are the structures of the "module" tables similar? Can you explain what differentiates module A from module B from module C from module D etc.? What basic type of data is held in these modular tables?

[kman42]

The Modules contain progress report data, but the progress reports differ substantially depending on the type of project it is. In the end, there may be several hundred different types of progress reports (Modules) because the metrics for each differ significantly. Each project will probably be associated with 3-5 different types of progress report (Modules) and there will be between 1-10 reports made for each type per project.

However, every progress report has Report_Date and Report_Type in common, regardless of which module it belongs to.

So when I am on a main form for a project, I want to have a subform that lists all of the progress reports for that project, regardless of which module it belongs to. Each project main form also has a subform listing the progress report types (Modules) that are associated with it). I have this last part working as it is just a subform related to a table with the fields Project_ID and Module_ID.

Thanks!

[kennejd]

You have to be able to relate the project to the progress report data in some way. I think there must be be a relation from project to module to progress report?

Sounds like you need to create a query that links projects and modules (same query as subform 2). Then add the progress report table based on whatever id field is relates the report to the module.

[jzwp11]

I think that you will have a very difficult time managing the tables associated with "several hundred types of progress reports"

Could you not have the progress report items as records and identify those report items that pertain to certain project types?

tblProjectTypes
-pkProjectTypeID primary key, autonumber
-txtProjectTypes


tblProgressReportItems
-pkProgRptItemID primary key, autonumber
-txtProgReportItem
-fkProjectTypeID foreign key to tblProjectTypes(Identifies the type of project with which this progress report item is associated.)


Then relate the items with the specific information to the individual projects

tblProjectProgress
-pkProjProgressRptItemID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkProgRptItemID foreign key to tblProgressReportItems

You could actually then track the progress of each item through time

tblProjectProgressDetail
-pkProjProgDetailID primary key, autonumber
-fkProjProgressRptItemID foreign key to tblProjectProgress
-dteProgress (date of entry)
-ProgressActionTaken

[kman42]

I'm new to Access, so I'm trying to follow your response. Let's say I have two modules now, one called "Business data" and one called "Intellectual property".

"Business data" tracks many fields, but let's just use a few: NumberEmployees, Revenue, Licenses, Sales

"Intellectual property" tracks: Patents, Trademarks, Publications

So if I understand you, you're suggesting that "Business data" and "Intellectual property" would be entries in tblProjectTypes?

tblProgressReportItems would then contain records for the names of the data fields and which project type they are associated with?
NumberEmployees, "Business data"
Revenue, "Business data"
Licenses, "Business data"
Sales, "Business data"
Patents, "Intellectual property"
Trademarks, "Intellectual property"
Publications, "Intellectual property"

Or do you mean that tblProgressReportItems would also contain the actual data?
Revenue, $60k, "Business data"
etc?

[jzwp11]

tblProgressReportItems would contain all of the possible items you would track, so essentially a list of items as you describe

NumberEmployees, "Business data"
Revenue, "Business data"
Licenses, "Business data"
Sales, "Business data"
Patents, "Intellectual property"
Trademarks, "Intellectual property"
Publications, "Intellectual property"


Now to capture the actual data depends on how you work with it. If you only capture the data once, then it would be captured in the following table. It sounds like the things you are capturing are numbers, so you would have a numeric field probably a single precision number.

tblProjectProgress
-pkProjProgressRptItemID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkProgRptItemID foreign key to tblProgressReportItems
-DataValue (field to capture the value)


Now if you plan on capturing this data at various times (to look at trends or whatever), you would have a one-to-many relationship which requires two tables:

tblProjectProgress (the same as above but without the datavalue field)
-pkProjProgressRptItemID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkProgRptItemID foreign key to tblProgressReportItems

tblProjectProgressDetail
-pkProjProgDetailID primary key, autonumber
-fkProjProgressRptItemID foreign key to tblProjectProgress
-dteProgress (date of entry)
-DataValue

[kman42]

That's very helpful. My data types will actually very pret widely, including yes/no checkboxes, text, numbers, and memos. Any recommendations?

[jzwp11]

In tblProgressReportItems you could have another field that defines the type of data (yes/no, text, number etc.) for that particular item then in the detail table have the appropriate fields each of the particular data type

tblProjectProgressDetail
-pkProjProgDetailID primary key, autonumber
-fkProjProgressRptItemID foreign key to tblProjectProgress
-dteProgress (date of entry)
-spDataValue (single precision number)
-logDataValue (yes/no)
-txtDataValue (text)
-memDataValue (memo)

For each record only 1 of the 4 data value fields will have a inputted value.

In your form, you would have a control for each but you would only display the applicable one to the user. You might actually overlap the 4 controls on the form, so it is transparent to the user.