Form, Category, Sub Category, Factors and Cascading Relational Tables

[Ephestion]

I was wondering if there was a better way to organise the data that I have. It is part of an App which is in Access 97 and Im redesigning for either VB6 and Access 2000 or going MS Access 2016-21 which I have installed.

Form 1 (All in a cascading one to many relationship)
- Category (Form ID, Category Name, X, Y)
--Sub Category (FormID, Category ID, X, Y)
---Factor 1 (FormID, CategoryID, SubCategoryID, X, Y)
---Factor 2 etc

Now I want to print the Form Data with the factors and have people input values into a Forms_Factors_Jobs Table.
Project
-Jobs
--Forms_Factors_Jobs

But it gets to the point where the Factors used in the form need programming to assign to the Forms_Factors_Jobs.

Any ideas?

[jojowhite]

you must first define the table structure:

Category(table)
CategoryID (autonumber) PK
Category (short string)

SubCategory(table)
SubCategoryID (autonumber) PK
CategoryID (Long) FK to Category table
SubCategory (short string)

Factor (table)
FactorID (autonumber) PK
FactorName (short string)

Factors_Job (table)
FactorID (autonumber)
SubCategoryID (Long) FK to Subcategory table
FactorID (Long) FK to Factor table

you create a Form agains Factor_job and make FactorID and SubCategoryID as combobox to their respective
Foreign table.

[Ephestion]

you must first define the table structure:

Category(table)
CategoryID (autonumber) PK
Category (short string)

SubCategory(table)
SubCategoryID (autonumber) PK
CategoryID (Long) FK to Category table
SubCategory (short string)

Factor (table)
FactorID (autonumber) PK
FactorName (short string)

Factors_Job (table)
FactorID (autonumber)
SubCategoryID (Long) FK to Subcategory table
FactorID (Long) FK to Factor table

you create a Form agains Factor_job and make FactorID and SubCategoryID as combobox to their respective
Foreign table.

Form is a table also so it would be
Form (table)
Category(Table)
Sub Category (Table)
Factor (Table)

4 x one to many relationships cascading.

Also what do you mean by FK

[June7]

PK = Primary Key
FK = Foreign Key

What is nature of this database? What would Form table contain?

[Ephestion]

PK = Primary Key
FK = Foreign Key

What is nature of this database? What would Form table contain?

It's a checklist system.

Checklist Form Table

[June7]

A properly structured schema and properly designed forms should not require code to save data. Why do you think it would be in your case? Is code used in 97 version at the stage you mentioned is issue? If the 97 db works, why don't you just convert to accdb format?

[Ephestion]

A properly structured schema and properly designed forms should not require code to save data. Why do you think it would be in your case? Is code used in 97 version at the stage you mentioned is issue? If the 97 db works, why don't you just convert to accdb format?

When you get to the Factors being scored stage they become part of another cascading data series,

Project
-Jobs
--ChecklistFormJobScoring

So there needs to be a bridge between the Data storing the checklist fields and the fields used to score that checklist or fill it in.

The conversion to accdb is not possible because the code and interface are in a split runtime database. It is entirely branched out to make use of mde files. The original database is gone except an archaic version of which still has all the needed tables.
Also the conversion process would require MS Access 2010 or some version that can do the conversion. I only have Access 2019 and Access 97.

[June7]

Not really following explanation for code requirement. I would have to view database.
Too bad you don't have the mdb, I would convert for you as I have 2010 on a laptop.

[Welshgasman]

I have 2007 still as well. I have converted a good few dbs for people here and on other sites.