How to Create Nested Tables using Access?

[Rafegh]

Dear all,
Hi,
I’m new to MS Access. I need to create a Database using Access in 2 days. This database should have 4 levels of data:
1) Different projects
2) Different sections for each project
3) Different layers for each section
4) Different datapoints for each layer
I think I should create nested tables, but the number of tables in each level is calculated from the data given in the previous level. Is there any way to create custom number of similar tables using MS Access?

 

[MarkK]

Hello and welcome to the forum

The nature of your questions suggests that you don't have a ton of experience working with relational databases. I strongly suggest you Google "database normalization" and do a little reading.

In short though, the system you describe should have four tables. Also, I don't think two days is enough time for you to complete the job you describe.

Hope this helps,

 

[CJ_London]

Sounds like you need 4 tables - one for each 'level' and perhaps some subsidiary lookup tables.

Can you clarify what you mean by 'create custom number of similar tables' - provide some examples of how they differ.

 

[Rafegh]

Thank you for your help. The Problem to be solved exactly is this:
"I Work in a highway construction consulting Engineers Company. Our company has about 40 projects right now. The database is needed for managing the pavement of these projects.
1) In the first level we have different projects. These projects has some properties that I want to be entered in the table associated with this level.
2) Each project is divided into a number of sections, that each section could have different pavement properties (length, number of layers, etc.). I want the number of sections to be entered in the previous level (Table) for each project.
3) Each of the sections include different layers with different properties (thickness, material type, etc.). Number of the layers for each section will be entered in the previous table.
4) At the final level we want to enter laboratory test results at incremental distances for each layer of each section of each project! I also want that the database itself define the distances that we need to have test on them (the number of tests needed) based on the properties that have been entered in previous tables.
I hope that I could define the structure well. My question is that how I could create this structure that the number of datasets needed at each level is defined in the previous level (form)?

 

[CJ_London]

you have a major task to get this done in 2 days.

To get you started you will need something like the following tables

1) In the first level we have different projects. These projects has some properties that I want to be entered in the table associated with this level.

Would need further explanation what you mean by properties - if you mean something like location, or completion date it would be added to tblProjects. If you mean number of sections, this will be in the next level of tables

tblProjects
ProjectPK autonumber
ProjectReference text
ProjectName text

Then for

2) Each project is divided into a number of sections, that each section could have different pavement properties (length, number of layers, etc.). I want the number of sections to be entered in the previous level (Table) for each project.
3) Each of the sections include different layers with different properties (thickness, material type, etc.). Number of the layers for each section will be entered in the previous table.

tblSections
SectionPK autonumber
SectionDescription text
ProjectFK long *note count the number of records with the same projectFK to determine the number of sections for the project
SectionLength number
SectionWidth number

tblLayers
LayerPK autonumber
LayerOrder number
SectionFK long - which section the layer belongs to
MaterialFK long - which material it uses
MaterialThickness number

tblMaterials
MaterialPK autonumber
MaterialDescription text

And for this

4) At the final level we want to enter laboratory test results at incremental distances for each layer of each section of each project!

tblTests
TestPK autonumber
LayerFK long
TestIncrementNo long *identify the location of each test for each layer
TestResult number

Finally

I also want that the database itself define the distances that we need to have test on them (the number of tests needed) based on the properties that have been entered in previous tables

This will probably require additional fields in the other tables to determine - for example if the distance is based on material thickness, section length and width, that information is available in tblSections and tblLevels. If it also depends on say material viscosity/cooling times then these would be additional fields required in tblMaterials

 

[jdraw]

Rafegh,

Who put the 2 day limit on this project?
Did you read up on Normalization?
Do you have a complete set of specifications for this project?
What exactly is your background and how did you become responsible for this system to be delivered in 2 days?

 

[WayneRyan]

FYI:

Duplicate of:

http://www.dbforums.com/microsoft-access/1702685-how-create-nested-tables-using-access.html

Wayne

 

[Rafegh]

Thank you for your answers. I have studied a lot about my issue, I have came to a point. For the work that I need to do, I must write an "After Insert" DataMacro. In the actions part of this macro I should select create record option. With this way I could create records for each section in the second table as I enter the "number of sections" field in the first table. Now my problem is that is it possible to create multiple records with different value for one field in the second table using this DataMacro?

 

[CJ_London]

Now my problem is that is it possible to create multiple records with different value for one field in the second table using this DataMacro

I don't see how. Regret I don't use macro's - they are too restrictive. But what you would need to do is have your macro loop for each record insert and insert some value

You would be better off designing a main form for the project with a subform to show each section and create your section records there.