I have looked through various posts, but can't seem to find the scenario I am dealing with. I have an expense dtabase I am building. It links to a Capacity dB that contains all of our metrics, such as # of delinquent accounts, # of workable accounts, # calls made, etc. In the expense dB, I need to allocate the actual expenses from the previous month to a specific metric. I have a table that contains the expense ID, description, and the allocation metric. The allocation metric field is populated by a field list based on the table from the Capacity dB, so that the user can assign which metric is to be used in the calculation for that particular expense GLID (Unit Cost:[Expense]/[Allocation Metric]). I have tried to use the DLookup function, and it is looking in the right column based on the allocation metric, but it returns the first value, not the one based on the criteria I entered.
Metric: DLookUp([Allocation_Metric],"sqry_Actual_Total",[sqry_Actual_Total]![ActualsID]=[tbl_Expense_Download]![ActualsID])
I have seen numerous comments on the fact that DLookup is slow and that I should just join a query in my query to acheive it, but how would I join a value in one table to a field on another?
Metric: DLookUp([Allocation_Metric],"sqry_Actual_Total",[sqry_Actual_Total]![ActualsID]=[tbl_Expense_Download]![ActualsID])
I have seen numerous comments on the fact that DLookup is slow and that I should just join a query in my query to acheive it, but how would I join a value in one table to a field on another?