DLookup in a query (1 Viewer)

WillEllis

Registered User.
Local time
Today, 09:03
Joined
Aug 18, 2005
Messages
20
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?
 

Attachments

  • Access Pic.jpg
    Access Pic.jpg
    75.1 KB · Views: 141

FoFa

Registered User.
Local time
Today, 08:03
Joined
Jan 29, 2003
Messages
3,672
Well for starters, I don't see the field Allocation_Metric in sqry_Actual_Total you are telling DLOOKUP to return (at least not in the JPG you supplied). DLOOKUP("column to return","table/query to use","Criteria to select")
 

WillEllis

Registered User.
Local time
Today, 09:03
Joined
Aug 18, 2005
Messages
20
The field [Allocation_Metric] contains values, which are field names. When the query runs, it needs to lookup the field name in [Allocation_Metric] and find that field in the sqry_Actuals_Totals query and retun the value in that field, based on the [ActualsID] found in the first column of the query. I have attached a pic of the table that contains Allocation_Metric. In this case, I need to find the field "ADA" in sqry_Actuals_Total. I wish I could send the database, but since it has links to such a large database, I can't.
 

Attachments

  • Access Pic2.jpg
    Access Pic2.jpg
    44.8 KB · Views: 119

FoFa

Registered User.
Local time
Today, 08:03
Joined
Jan 29, 2003
Messages
3,672
Yes but the field Allocation_Metric is not in sqry_Actual_Totals and that is one reason your DLOOKUP is not working.
Your criteria (from the form) is selecting a row from sqry_Actual_Totoals (I assume) but what does that have to do with Allocation_Metric which is in tbl_GL_Data? What needs to be done is you need to join tbl_GL_Data to sqry_Actual_Totals to get the proper Allocation_Metric field (at least that is what I am assuming from this thread). So how can you join those two tables? BTW I am not sure you need DLOOKUP, but I don't see the logical relationships at this point.
 

FoFa

Registered User.
Local time
Today, 08:03
Joined
Jan 29, 2003
Messages
3,672
OK, maybe I am getting closer after rereading you last post again. Sounds like what you need is a IIF in your query.
IIF ([tbl_GL_DATA]="ADA",sqry_Actual_Totals.ADA,IIF ([tbl_GL_DATA]="XXX",sqry_Actual_Totals.XXX,"UNKNOWN"))
as an example
 

WillEllis

Registered User.
Local time
Today, 09:03
Joined
Aug 18, 2005
Messages
20
That might work, but it would require alot of manual changes should new metrics be added to the field list. I was hoping to have the query just lookup whatever metric found in Allocation_Metric as a field name in sqry_Actuals_Total. Currently, it does bring back a value from the ADA field(since "ADA" is the value in Allocation_Metric), but it seems the criteria is not looking in the right row, just returning the first one.
 

WillEllis

Registered User.
Local time
Today, 09:03
Joined
Aug 18, 2005
Messages
20
Hopefully this screenshot will help anyone understand it a bit better.
 

WillEllis

Registered User.
Local time
Today, 09:03
Joined
Aug 18, 2005
Messages
20
Here is the screenshot

Hopefully this screenshot will help anyone understand it a bit better.
 

Attachments

  • Access Issue.jpg
    Access Issue.jpg
    93.4 KB · Views: 148

FoFa

Registered User.
Local time
Today, 08:03
Joined
Jan 29, 2003
Messages
3,672
Yes, however you issue is you are storing a column name as a data item. This would work if the ADA column was a data item also. So it really boils down to table design is off. I think at this point what you need to do is create a vba function that can pull the correct data for you (rather than a DLOOKUP, use your function) and pass it as many parameters as required to get the proper column. You can make it generic enough where the function would not need to be changed for any changes to your metrics.
 

WillEllis

Registered User.
Local time
Today, 09:03
Joined
Aug 18, 2005
Messages
20
Not looking for anyone to build it for me, but would you have some suggestions on how I would start a function like that? I haven't really done too much with creating functions.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:03
Joined
Feb 19, 2002
Messages
42,981
The problem is that your tables are not normalized. Each of the columns you are trying to lookup should be a row in a table. The table columns should be:

ActualsID
MetricName (MonthID, ADA, WAOF,....)
MetricValue
 

FoFa

Registered User.
Local time
Today, 08:03
Joined
Jan 29, 2003
Messages
3,672
For a function you would need to pass the function all the data elements it would need to select the proper value for you. At first glance it looks like it would be the allocation_metrics, ActualID and MonthID, so the function can pull the proper value from the table and pass it back.

Proper design however would break the columns down into rows instead, then you could just join the table to your query. So you would have one row for an ADA value and one for a WOAF value.
 

WillEllis

Registered User.
Local time
Today, 09:03
Joined
Aug 18, 2005
Messages
20
Well, because of the way I am calculating metrics, ratios, etc in my Capacity db, having each metric as a separate column works great, as the number of queries I would need to do my calculations would be astonomical(we looked into it prior to building). So, since I need my metrics as a value in a 'Metric" field, and the number of metrics needed for my Expense db are much smaller, I have created a query for each metric, creating a "Metric" field and giving each metric a name in that field. Then I UNION all the metrics and append a table each month with the values. My Expense db will then link to this new table. It's a bit convoluted, but with forms that run everything at the click of a button, it should be transparent to the user. Thanks for all the help. It really gave me a new perspective on this, leading me to my solution.

Just an aside...Perhaps if Access could create month-over-month crosstab reports(as in order by DATE!!! instead of alphabetically), I wouldn't have as much work to do...but that is for another discussion. We are switching to Microstrategies BI Tool for our report generator. so Access would only be for data manipulation, not report creation....now I just have to go back and redo all my databases...lol!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:03
Joined
Feb 19, 2002
Messages
42,981
Well, because of the way I am calculating metrics, ratios, etc in my Capacity db, having each metric as a separate column works great, as the number of queries I would need to do my calculations would be astonomical
It is just the opposite. If your metrics are properly normalized, you will reduce the number of queries rather than expand them.

Just an aside...Perhaps if Access could create month-over-month crosstab reports(as in order by DATE!!! instead of alphabetically), I wouldn't have as much work to do...but that is for another discussion.
Of course it can if you don't format the date in the query. Formatting the date field converts it to a text string and that makes April the first month of the year.
 

WillEllis

Registered User.
Local time
Today, 09:03
Joined
Aug 18, 2005
Messages
20
I am generating new metrics based on the ones in the original tables, but all of them, original and genrated are reported on, month over month. I can create a majority of the new ones in just one query, whereas when I use the 'normalized' version, it requires me to create each new metric in a separate query (metric1 + metric2 + metric3 = New metric). Concerning the month over month crosstab query, it works fine until I need to turn it into a report...I had to use the Dynamic Crosstab Report code I found here, with some modifications, to get it to work.
 

Users who are viewing this thread

Top Bottom