Table Relationship Question!

[mbake085]

I am working on a system to request funding. Yesterday I made a database with three tables and a form to enter the data. I had table relationships where you could enter data into the form and the relationship would populate the corresponding tables.

Here is an example:

CfrCost table which includes the following fields:

Cfr#(Primary Key, autonumber), ProjectName, InstallCost, ExpenseRequested,...

CfrProject table

ProjectName (Primary Key, text), CostCenter#, Requestor,...

CfrExplain table

Cfr#(Primary Key, text), ExplainPurchase, DateCompletion,...

I linked the tables where the fields have the same name. When completing my form there were fields in the field list that were the combination of the two related fields. Such as: Cfr#CfrCost_tableCfr#CfrProject_table

I had my form laid out in such a way that the Cfr# would populate through the tables and the ProjectName would do the same. And it was working perfectly.



I accidently deleted this form and in the process of doing so I also changed some names of tables and fields. I tried to redo everything but I can't get it to work. I no longer have the similar concatenated options in the field list I had when I originally made the form. I have no idea how I did this and I am extremely frustrated. If anyone could help I would really appreciate it.

I am a beginner to access so I did this before without any code.

[jzwp11]

Welcome to the forum!

I'm not sure I quite understand your table structure. I would recommend having an autonumber, primary key in every table. Also, it is generally best not to have spaces or special characters (#,%,$,& etc.) in your table or field names.

Translating your tables, I see the following, but I don't understand the relationships between the tables. Could you explain the relationships in words?

CfrCost
-CfrNoID (Primary Key, autonumber)
-ProjectName
-InstallCost
-ExpenseRequested

CfrProject
-pkProjectID primary key, autonumber
-ProjectName
-CostCenterNo
-Requestor

CfrExplain
-pkCfrNoID primary key, autonumber
-ExplainPurchase
-DateCompletion

[mbake085]

Project name in cfrcost is related to project name in cfrproject. I did this because a project may have the same payment information related to it but could be used on many requests. So instead of typing the same info in everytime I had it set up where if an existing project name was entered it would return the related payment information to the fields in the form.

The other relationship was a one to one relation between the cfr# in cfrcost table and the cfr# in the cfrexplain table. I did this because all the explaination information will not be used often so I wanted it in a seperate table.

All these fields are in a single form. For example if you start typing the project name into the form the form auto numbers itself and the project name carries over into both related tables, and the cfr# carries over into both tables. So information in three tables will populate from one form.

I found an old version of this database which kind of works how I want it but I can't get it to completely work like the old one I had. The reason it is working is: when I am building a form there is an extra field in the cfrproject table that shows the relationship. There is the original field projectname and the relationship field projectname_cfrproject (the projectname from cfrcost table_the cfrproject table). I have tired everything and I cannot replicate this field. I have no idea how I did it to begin with.

Thanks

[jzwp11]

OK, I understand some of what you are saying. You say that a project can have many costs, so I would modify CfrCost with the projectID rather than the project name. Relational databases are more efficient when you join tables with numeric fields rather than text fields.

CfrCost
-CfrNoID (Primary Key, autonumber)
-fkProjectID foreign key to CfrProject, long number field
-InstallCost
-ExpenseRequested

CfrProject
-pkProjectID primary key, autonumber
-ProjectName
-CostCenterNo
-Requestor

I still don't understand the CfrExplain table. If you just want an explanation of each cost incurred for a project, then it should go in CfrCost. However, if you have a set of explanations that can be used over and over again, then it would be best to set those explanations up in a separate table and then link them to your CfrCost table.

So a table to hold the possible explanations:

tblCostExplanations
-pkCostExplainID primary key, autonumber
-txtCostExplanationText


Then modify CfrCost to hold the appropriate explanation for the cost incurred.

CfrCost
-CfrNoID (Primary Key, autonumber)
-fkProjectID foreign key to CfrProject, long number field
-InstallCost
-ExpenseRequested
-fkCostExplainID foreign key to tblCostExplanations, long number field

In terms of forms, you would have a main form based on CfrProject and on that form you will have a subform based on CfrCost. If you set up your relationships via the relationship window before you do the form, Access will automatically join the main form to the subform and thus will autopopulate the fkProjectID field in the subform when a new record is added. Within that subform, you would use a combo box based on tblCostExplanations to populate the explanation for each cost.

[mbake085]

Got it to work. Thank you!

[jzwp11]

You're welcome, & I'm glad to hear that you got it worked out.