Issue with Relationships

[Ev0luTioN]

Hi all,

I'm fairly new to Access and am having issues with a database I'm attempting to build.



I'll explain the relationships:

Table 1 (Contractors):

• Field 1: ID (Primary Key)
• Field 2: Contractor Name

Table 2 (Labor Categories):

• Field 1: ID (Primary Key)
• Field 2: Contractor Name (Foreign Key)
• Field 3: Labor Category

Table 3 (Fiscal Year):

• Field 1: ID (Primary Key)
• Field 2: Labor Category (Foreign Key)
• Field 3: Year
• Field 4: Rate

Relationships:

• One-to-Many between T1,F1(ID) and T2,F2(Contractor Name)
• One-to-Many between T2,F1(ID) and T3,F2(Labor Category)

Basically, there is a contractor who has many Labor Categories, and each Labor Category has many Years and Rates.

Now, when I set up the relationships as I've covered, I get an error message after I begin manually filling the Contractors table (manually for testing), then save/close it, then reopen it.

When I reopen it, I click the little '+' to bring up the sub-database (Table 2). However, instead of bringing it up I get the following error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

Is my logic flawed? It seems logical to me, but isn't working. Any help would be appreciated, thanks!

[jzwp11]

You said that a contractor can have many labor categories, but can a labor category apply to many contractors?

I would suggest that you use more descriptive ID field names. Also, it is best to make your joins between tables with the ID fields not the text field. Relational databases are more efficient with numeric fields rather than text fields. Also, it is generally recommended to not have spaces or special characters in your table and field names. Also, the word Year is a reserved word in Access, so it should not be used as a table or field name.

tblContractors
-pkContractorID Primary Key, autonumber
-ContractorName

tblLaborCategories
-pkLaborCatID Primary Key, autonumber
-LaborCategory

tblLaborCategoryRates
-pkLaborCatRatesID primary key, autonumber
-fkLaborCatID foreign key to tblLaborCategories, long number field
-longYear
-currRate

[Ev0luTioN]

You, sir, are a savior.

I guess trying to use a relationship between an Auto Number and Text wasn't going to work. Thank you so much for the help, hopefully I can handle it from here.

[jzwp11]

Glad I was able to help. As an additional think to consider, if a labor category can apply to many contractors, then the current table structure will not work. You will need to add a junction table (shown in blue below)

tblContractors
-pkContractorID Primary Key, autonumber
-ContractorName

tblLaborCategories
-pkLaborCatID Primary Key, autonumber
-LaborCategory

tblContractorsLaborCategories
-pkContLabCatID primary key, autonumber
-fkContractorID foreign key to tblContractors, long number field
-fkLaborCatID foreign key to tblLaborCategories, long number field

tblLaborCategoryRates
-pkLaborCatRatesID primary key, autonumber
-fkLaborCatID foreign key to tblLaborCategories, long number field
-longYear
-currRate

[Ev0luTioN]

I don't think that should be a problem. While identical labor categories can appear with different contractors, each would have a different rate associated with it. Should just be a one-to-many relationship. Thanks again!

[jzwp11]

That sounds different, the rate depends on the combination of contractor and category which would be structured differently. I would recommend this structure:

tblContractors
-pkContractorID Primary Key, autonumber
-ContractorName

tblLaborCategories
-pkLaborCatID Primary Key, autonumber
-LaborCategory

tblContractorsLaborCategories
-pkContLabCatID primary key, autonumber
-fkContractorID foreign key to tblContractors, long number field
-fkLaborCatID foreign key to tblLaborCategories, long number field

tblContractorsLaborCategoryRates
-pkContLabCatRatesID primary key, autonumber
-fkContLabCatID foreign key to tblContractorsLaborCategories
-longYear
-currRate