Query Trouble - Nested Query?

[iDeals]

Okay so I have four tables.



tblCustomer
tblFeeStructure
tblProjectDetails
tblProjectHours

Each customer has a different fee structure, for example:

fkCustID | Underwriting | Documentation | Records
.....1.............$150............... $250............ $52

I'm trying to tie it all together so I can generate an invoice, so for example in the ProjectHours table there may be entries like this:

ID | fkProjID | Employee | Type of Work | Billable Hours | Description
.1........ 1 ........... Me........Underwriting............5............Spreading

Herein lies my problem I don't know how to get the fee structure associated with the customer to carry through to the worklog (tblProjectHours). Any ideas?

[June7]

tblFeeStructure is not normalized. As it is, use DLookup() expression in query.

So in a query that joins Customer, ProjectDetails, ProjectHours, calculate a field for the fee: DLookup([Type of Work], "tblFeeStructure", "fkCustID=" & [CustomerID])

Assumes the values in [Type Of Work] coincide with the field names in tblFeeStructure.

[iDeals]

Thank you June, will give that a shot.

To Normalize would each fee need its own table? For some reason I cant wrap my head around this one.

[June7]

No, each fee would be a record. This means the customer ID will be in multiple records.

I just saw another alternative.

Join tblFeeStructure to tblCustomer in query that includes Details and Hours. Then an expression in that query:

Switch([Type of Work]='Underwriting', [Underwriting], [Type of Work]='Documentation', [Documentation], [Type of Work]='Records', [Records])

As you can see, if you have more fee categories, this gets more complicated.