views:

105

answers:

3

Access 2007 is telling me that my new expression is to complex. It used to work when we had 10 service levels, but now we have 19! Great! I've asked this question in SuperUser and someone suggested I try it over here. Suggestions are I turn it in to a function - but I'm not sure where to begin and what the function would look like.

My expression is checking the COST of our services in the [PriceCharged] field and then assigning the appropriate HOURS [Servicelevel] when I perform a calculation to work out how much REVENUE each colleague has made when working for a client. The [EstimatedTime] field stores the actual hours each colleague has worked.

[EstimatedTime]/[ServiceLevel]*[PriceCharged]

Below is the breakdown of my COST to HOURS expression. I've put them on different lines to make it easier to read - please do not be put off by the length of this post, it's all the same info in the end.

Many thanks,Mike

ServiceLevel: IIf([pricecharged]=100(COST),6(HOURS),
IIf([pricecharged]=200 Or [pricecharged]=210,12.5,
IIf([pricecharged]=300,19,
IIf([pricecharged]=400 Or [pricecharged]=410,25,    
IIf([pricecharged]=500,31,
IIf([pricecharged]=600,37.5,
IIf([pricecharged]=700,43,
IIf([pricecharged]=800 Or [pricecharged]=810,50,
IIf([pricecharged]=900,56,
IIf([pricecharged]=1000,62.5,
IIf([pricecharged]=1100,69,
IIf([pricecharged]=1200 Or [pricecharged]=1210,75,
IIf([pricecharged]=1300 Or [pricecharged]=1310,100,
IIf([pricecharged]=1400,125,
IIf([pricecharged]=1500,150,
IIf([pricecharged]=1600,175,
IIf([pricecharged]=1700,200,
IIf([pricecharged]=1800,225,
IIf([pricecharged]=1900,250,0)))))))))))))))))))

UPDATE (16/04/10 14:46 GMT)

I've built a new table as recommended below. Now it's matter of removing my expression and making my original SELECT query use the new table. However, I'm not at all clear on how this is done.

Using the DLookUP example I imagine I keep the original query where I have my PRICECHARGED field, include a new DLookUp field that uses the example below to refer to the PRICECHARGED and populate the rows with the appropriate SERVICELEVEL (hours).

I become confused at this point because in my original SELECT query all the calculation happened in that query (expressions, after expression), which meant that the final result of my query was a list of colleagues with HOURS, and REVENUE (Cost) against their name. I have a feeling that I'm in need of another query in the middle to manage the DlookUp process.

`Any directions or road map very much appreciated.

Final update. Added another table as mentioned above, created the appropriate links from the new table to the field in the 'fixed'table. Voila. It worked a charm, and without me doing anything that caused it to crash or give me an error warning. It was easier than I thought. I don't understand why I didn't manage it this way before.

Thanks again, Mike

+1  A: 

Put all the data into a table and do a dLookup! Here is a table called CostHours

Cost  Hours
----  ------
100 6
200 12.5
210 12.5
300 19
400 25
410 25
500 31
600 37.5
700 43
800 50
810 50
900 56
1000    62.5
1100    69
1200    75
1210    75
1300    100
1310    100
1400    125
1500    150
1600    175
1700    200
1800    225
1900    250

And to get the hours from the cost, here is a dLookup

dLookup ("Hours", "CostHours", "Cost=" & 100)

This will return the hours where the Cost is 100. Use that value in your calculations.

The beauty of MS Access is that dLookup can sit in the same place you do your calculations - in code, in a report, in a query.

Instead of a DLookup, you can do this in a SQL Statement as well

"SELECT Hours From CostHours Where Cost" = & CostVariable

Raj More
Where does the DLookUp function sit? In a table linked to the COSTHOURS table? or can it be used within a SELECT query? Stupid question maybe but I have to ask.
Mike
answer edited based on comment
Raj More
if you downvote, please explain why you did so
Raj More
Why are you suggesting DLookup instead of just using a join and appropriate criteris in SQL? (I am not your downvoter, BTW)
David-W-Fenton
@David-W-Fenton: The `SQL Join` would be the best answer if the question was about using it in a query. The question was about modifying the expression. That's why I went with a DLookup or a `SELECT` approach.
Raj More
@Mike: Please give us details on where you are using the expressions currently so we can tell you how you can effectively use the dlookup.
Raj More
@Raj More: the expression *is* being used in a query, which can be seen from the way it's being aliased, i.e., "ServiceLevel: IIf([pricecharged]=100...", which is copied straight out of the QBE. That's not going to be obvious to a non-Access user, I guess, but neither is the idea of using an "expression" outside of SQL, I think.
David-W-Fenton
@David-W-Fenton: I glossed over the aliasing there. A SQL Join would be much more effective.
Raj More
@Raj More - you're right it's in a query. My update above is where I'm at now. I'm trying to think how I could get the appropriate hours from my table when there is no real way to join the fields. Currently they are simple currency fields (no PK/FK) only the values would be similar. Is that enough for the Dlookup to work?
Mike
+2  A: 

You're making life really, really, really hard for yourself.

Create a table PriceToHours with columns PriceCharged and Hours. Put the appropriate rows into it so it matches the tabular data in your expression. Now JOIN that table to your main data table to get the Hours from the PriceCharged. When this information changes in the future, simply update the data in the table. This is much more transparent (no data hidden in a query expression) and requires no additional programmer time when the values change in the future.

Larry Lustig
I know. See comment above. The fields used would allow someone to put 'foo' in the hours fields, and 100,000 in the PriceCharged field. I can't restrict/force much, so that is why I have arrived where I have- and it is a mess...or should that be challenge!
Mike
+2  A: 

I would suggest changing the way this works and keeping the service level to price charged logic in a separate table. This would allow you to change it quickly if the business need changed.

The table would just be as simple as two columns and “ServiceLevel” and “PriceCharged”. Then in your query link that to your table.

For a quick check however what would happen in the price charged was say 1105 i.e. not in the list? The function would possibly return a null which would then cause the expression to fail maybe that is causing it?

EDIT::

After a bit of searching around it looks like the maximum number of nested IIF statements in access is 10 hence why it worked fine before but now does not. Looks like your only option is to redesign it to how it should have been in the first place!

Kevin Ross
Another table would be great, but unfortunately this is a 'locked' database that I'm wrangling as best I can - hence the horrible expression. The fields I have to work with are text fields for Hours) or at best currency fields. Linking my new table to them could be temperamental. Any ideas?
Mike