Relational Database Design Questions

[jzwp11]

The financial data itself is associated at the lowest possible level (bbbb or project level in our previous example).

Then that tells you how to make the joins for the tables: one project to many financial data items.

The financial data itself contains the full WBS element number, wwww.xxxx.yyyy.zzzz in our original example as well as financial data (types and amounts as we discussed earlier). My structure in the picture is more like wwww.xxxx.yyyy.zzzz.aaaa.bbbb. Sometimes, however, this data is entered incorrectly (into the customer's other database) and when I pull the financial data (my excel import), the incorrect wbs element numbers will show up. I need to have an override table to link the incorrect wbs element numbers to an alternate corrected number.

Actually it would be better to have all of the possible full WBS element numbers in a table and use a flag field to flag the correct one.

tblOverideTable
-pkOverideID primary key, autonumber
-foreign key field to the table that holds information, I'm just not sure which table--the project table (your level 6 table???)
-txtWBS
-logCorrect (yes/no field; checked if the wbs element is the correct one, unchecked for those that are not


You would create a query that joins the incoming data table with the override table via the txtWBS field in both tables. You would select the fields from the incoming data table you want to append and the foreign key field of the override table. Change the query to an append query and select the destination table and run the append query.

[mribnik]

Then that tells you how to make the joins for the tables: one project to many financial data items.

I get that, but the project table only includes that 6th level WBS element, bbbb, whereas the financial data table includes the full element number, wwww.xxxx.yyyy.zzzz.aaaa.bbbb. I'm guessing I would have to create an additional field in the financial data table showing just the bbbb? Of course, in this case, that bbbb could be incorrect and require an override.

Actually it would be better to have all of the possible full WBS element numbers in a table and use a flag field to flag the correct one.

tblOverideTable
-pkOverideID primary key, autonumber
-foreign key field to the table that holds information, I'm just not sure which table--the project table (your level 6 table???)
-txtWBS
-logCorrect (yes/no field; checked if the wbs element is the correct one, unchecked for those that are not


You would create a query that joins the incoming data table with the override table via the txtWBS field in both tables. You would select the fields from the incoming data table you want to append and the foreign key field of the override table. Change the query to an append query and select the destination table and run the append query.

The project table is the level 6 table, yes. That table would hold the correct lvl 6 project number. Concatenating the 1st, 2nd......6th wbs_lvls from the associated tables would result in the full correct wbs number. The financial data table contains a field with full wbs numbers but a few of them were incorrectly entered and need to be modified (using the overrides table) to match the appropriate concatenation from the 6 level tables.

My apologies if this seems really confusing.

[jzwp11]

I get that, but the project table only includes that 6th level WBS element, bbbb, whereas the financial data table includes the full element number, wwww.xxxx.yyyy.zzzz.aaaa.bbbb. I'm guessing I would have to create an additional field in the financial data table showing just the bbbb? Of course, in this case, that bbbb could be incorrect and require an override.

You can extract the bbbb part from the full element using built-in functions within Access. In other words, create a query with all fields of the financial data table and then add a calculated field that extracts the bbbb part and use this query in a second query to match the override table. If the bbbb is always the last 4 characters of the string the Right() function should do the trick.

[mribnik]

You would establish the primary key-->foreign key relationship in the relationship window. From there, when you create forms, your main form would be based on the table (with the primary key) that forms the one side of the one-to-many relationship and your subform would be based on the table making up the many side of the relationship. When you drag the subform into the main form, Access uses the relationship you created to join the subform to the main form. You would then enter a record in the main form (if you are using an autonumber primary key, the value increments) and then as you migrate from the main to the subform, Access uses the relationship to populate the control corresponding to foreign key with the value of the primary key of the record just created in the main form.

I just read this again.

I'd like to be able to add the data by entering in a project # (all 6 levels). The situation you described above is for only 2 levels. Would this mean that I would need to have 1 main form (1st level) with a subform (2nd level). Could that subform have another subform for the 3rd level and so on? Or how else would I do it?

Ideally, I'd like a user to be able to enter a full project number in sections (perhaps as you would enter in a microsoft office product activation key). After entering in all levels, the association information (program name, division name etc.) would be populated somewhere else on the form. There has to be at least one level that is not yet included in the database (the 6th is being the only level in most cases) and there would be some indication on this form that the 6th level represents a new 6th level.

[jzwp11]

Could that subform have another subform for the 3rd level and so on?

Yes a subform can have a subform and that subform can have its own subform etc. Having such a setup may not be very user friendly. There are several different ways to do this. One might be to have a form where the user enters info at level 1, then you move them to a second form with 1 subform where then enter level 2 info, then create a third form with 1 subform. The main form would bound to a query that holds the information from both levels 1 and 2. The user would use the subform to enter level 3 information. Then navigate them to a fourth form: main form based on a query that includes levels 1, 2 and 3 and a subform for level 4 data entry. Then do something similar for levels 5 and 6.

Ideally, I'd like a user to be able to enter a full project number in sections (perhaps as you would enter in a microsoft office product activation key). After entering in all levels, the association information (program name, division name etc.) would be populated somewhere else on the form

You could have an upbound form where the user enters the pertinent information for the first 5 levels and then use code to create the necessary records in the corresponding tables. For routine project data entry, you would create a main form bound to a query that gathers up the first 5 levels with a subform for the user to enter level 6 data. You can present the user with a search form where they can drill down to the project for which they need to enter data (and open the form I mentioned)

[mribnik]

Then that tells you how to make the joins for the tables: one project to many financial data items.



Actually it would be better to have all of the possible full WBS element numbers in a table and use a flag field to flag the correct one.

tblOverideTable
-pkOverideID primary key, autonumber
-foreign key field to the table that holds information, I'm just not sure which table--the project table (your level 6 table???)
-txtWBS
-logCorrect (yes/no field; checked if the wbs element is the correct one, unchecked for those that are not


You would create a query that joins the incoming data table with the override table via the txtWBS field in both tables. You would select the fields from the incoming data table you want to append and the foreign key field of the override table. Change the query to an append query and select the destination table and run the append query.

Just to be clear on this:

Right now I have a database structure with a table for each level (named tblWbsFirstLevel...tblWbsSecondLevel...etc.). The first level table has a one to many relationship to the second level, the second a one to many relationship to the third level and so on...

In addition, each table has a values table associated with. For example, the first level represents the fiscal year and it has a Fiscal Years table with a list of Fiscal Years. The Fiscal Years table has a one to many relationship to the First level table.

This structure is settled based on our conversation and I'm very confident in it.

In addition, I require a financial data table. The data from this table will be imported from excel. The table has a project number field (containing all 6 WBS levels) and associated financial data fields as we discussed earlier.

The project numbers in the financial data table all contain financial information but some of the project numbers are incorrect. I would like to preserve these incorrect values, but have some kind of overrides table that associates the incorrect values with a corrected value (a value that is equal to a full project number stored in the First - Sixth level tables.

I'm kinda confused by your logCorrect field in your proposed overrides table.

Maybe an example will help.

let's say that our First through Sixth Level Tables contain wbs elements that look like this (I excluded the values table fields for this example):

tblWbsFirstLevel
-pkWbsFirstLevelID: 1
-txtWbsFirstLevelNo: 01234

tblWbsSecondLevel
-pkWbsSecondlevelID: 1
-fkWbsFirstLevelID: 1
-txtWbsSecondLevelNo: 56

tblWbsThirdLevel
-pkWbsThirdLevelID: 1
-fkWbsSecondLevelID: 1
-txtWbsThirdLevelNo: 78

tblWbsFourthLevel
-pkWbsFourthLevelID: 1
-fkWbsThirdLevelID: 1
-txtWbsFourthlevelNo: 91

tblWbsFifthLevel
-pkWbsFifthLevelID: 1
-fkWbsFourthLevelID: 1
-txtWbsFifthLevelNo: 23

tblWbsSixthLevel
-pkWbsSixthLevelID: 1
-fkWbsFifthLevelID: 1
-TxtWbsSixthLevelNo: 01

Concatenating all those levels with a period in between each level, our full wbs element no looks like:

01234.56.78.91.23.01

I pull my financial data report in excel and the data looks like:

Proj_no ___________|| Budget_amt ||
01234.56.78.91.23.1 || $100______ ||

This is a common mistake. Someone accidentally entered a "1" instead of an "01" but we can't fix this. So what I would like to do is have some kind of overrides table that associates the incorrect number, "01234.56.78.91.23.1" with the correct one, "01234.56.78.91.23.01."

Ideally, I'd import my financial data into Access, and run a report that shows all project numbers contained in the financial data that don't exists in my First through Sixth level tables and do not have an override in the overrides table yet. The user would then either create an override or add the project number to the level's tables.

I'm still confused what that structure looks like though. How is the Sixth level table connected to the overrides table and/or the financial data table based on my requirements. Hopefully that all makes sense. I think this is the last missing piece for me.

Thank you.

[jzwp11]

Rethinking a little, do not include the correct WBS element number in the override table (contrary to what I said previously).

Assuming that the field pkWbsSixthLevelID in tblWbsSixthLevel is an autonumber primary key, this should work:

tblOverride
-pkOverideID primary key, autonumber
-fkWbsSixthLevelID foreign key to tblWbsSixthLevel
-txtAltWBSNumber (your incorrect wbs number for the same project)

Since fkWBSSixthLelvelID joins to the 5th level table and that joins to the 4th level table and then the 3rd level etc, using the fkWbsSixthLevelID field in tblOverride is all you need to uniquely identify a project and its full WBS number.

Create a query that joins the six level tables and construct the concatenated full WBS number using a calculated field. Include the pkWbsSixthLevelID field in the query as well as the calculated field. Save the query. (For reference, I'll call this Query1)

Create a query that joins your incoming financial data table to the override table. Join the two via the WBS element fields in both. Select the fields from the incoming datatable and the fkWBSSixLevelID field of the override table. Save that query. (I'll call this Query2).

Create a new query that includes both Query1 and Query2, join the two via the fkWBSSixLevelID field in both. Now you financial data is tied to the correct project.

You can the use this query as the basis for appending your financial data.

[mribnik]

Rethinking a little, do not include the correct WBS element number in the override table (contrary to what I said previously).

Assuming that the field pkWbsSixthLevelID in tblWbsSixthLevel is an autonumber primary key, this should work:

tblOverride
-pkOverideID primary key, autonumber
-fkWbsSixthLevelID foreign key to tblWbsSixthLevel
-txtAltWBSNumber (your incorrect wbs number for the same project)

Since fkWBSSixthLelvelID joins to the 5th level table and that joins to the 4th level table and then the 3rd level etc, using the fkWbsSixthLevelID field in tblOverride is all you need to uniquely identify a project and its full WBS number.

Create a query that joins the six level tables and construct the concatenated full WBS number using a calculated field. Include the pkWbsSixthLevelID field in the query as well as the calculated field. Save the query. (For reference, I'll call this Query1)

Create a query that joins your incoming financial data table to the override table. Join the two via the WBS element fields in both. Select the fields from the incoming datatable and the fkWBSSixLevelID field of the override table. Save that query. (I'll call this Query2).

Create a new query that includes both Query1 and Query2, join the two via the fkWBSSixLevelID field in both. Now you financial data is tied to the correct project.

You can the use this query as the basis for appending your financial data.

Two more quick questions.

In the above, am I establishing a one-to-one relationship between the 6th level table and the overrides table? Is it necessary to include one record for every project number or only the ones that require overrides?

Also, if I'm looking at this correctly, it looks like there isn't actually a need to relate the financial data table to any of the other tables in access, it's only done through querying, is that right?

If so that makes sense to me.

[jzwp11]

In the above, am I establishing a one-to-one relationship between the 6th level table and the overrides table? Is it necessary to include one record for every project number or only the ones that require overrides?

Not necessarily, if you have multiple records in your imported financial data related to the same project (but with a mix of multiple erroneous WBS numbers and correct WBS number) a one-to-one relationship will not work.

Also, if I'm looking at this correctly, it looks like there isn't actually a need to relate the financial data table to any of the other tables in access, it's only done through querying, is that right?

You have to migrate (append) the imported financial data into a table that is related (since the financial data is in an Excel structure not Access)

tblFinancialDataTypes
-pkFinDataTypeID primary key, autonumber
-txtFinDataTypeName

tblProjectFinancialData
-pkProjFinDataID primary key, autonumber
-fkWbsSixthLevelID foreign key to tblWbsSixthLevel
-fkFinDataTypeID foreign key to tblFinancialDataTypes
-currAmt
-dteFinData (a date field is probably in order)


Once you have migrated the financial data, the import table can be deleted.

[mribnik]

Not necessarily, if you have multiple records in your imported financial data related to the same project (but with a mix of multiple erroneous WBS numbers and correct WBS number) a one-to-one relationship will not work.

You have to migrate (append) the imported financial data into a table that is related (since the financial data is in an Excel structure not Access)

tblFinancialDataTypes
-pkFinDataTypeID primary key, autonumber
-txtFinDataTypeName

tblProjectFinancialData
-pkProjFinDataID primary key, autonumber
-fkWbsSixthLevelID foreign key to tblWbsSixthLevel
-fkFinDataTypeID foreign key to tblFinancialDataTypes
-currAmt
-dteFinData (a date field is probably in order)


Once you have migrated the financial data, the import table can be deleted.

I can see how this fits together.

It sounds to me like you've more or less described a weekly procedure that's required to determine when new projects or overrides are needed.

1. User inputs the excel financial data into a temp table in Access.
2. Run queries/reports to determine new projects and overrides needed
3. Add new projects and overrides as needed using forms.
4. Run the queries/reports again to make sure the projects overrides were added successfully.
5. Run query associating imported financial data with the correct project number and appends the imported data to the financial data table.
6. Delete temp table

Is that what you had in mind? Thanks again

[jzwp11]

Almost,

1. User or system administrator imports the excel financial data into a temp table in Access.
2. Run queries to determine & add new projects as needed (if all the information is included in the import, if not then it will be entered manually via forms)
3. Add new projects and overrides as needed using forms (unless they can be appended directly from the imported table)
4. Run the queries/reports again to make sure the projects overrides were added successfully.
5. Run query associating imported financial data with the correct project number and appends the imported data to the financial data table.
6. Delete temp table