Calculations in Queries

[jcruzAME]

Okay, so I've not done much with calculating a value as a query value. I'm currently doing some research but I figured I would also ask here.

So this query needs to include a fee that is calculated in the following way:

• 0.4% for the first $5M
• 0.2% for the second $5M
• 0.1% for everything else over $10M

What would be the best way to go about calculating this in a query (if that's possible at all).

I kind of have an idea on how to calculate it in general, just not sure how to put it in a query.

 

[MSAccessRookie]

I would think that a User Defined VBA Function that uses the amount as the value to test, and returns the calculated fee would be suitable for your needs.

 

[jcruzAME]

So I would just call the function as that column?

 

[MSAccessRookie]

Yes. The definition of that column would be an alias that calls the function

 

[jcruzAME]

Cool, thanks for the info!

 

[MSAccessRookie]

glad to assist. post back if you have further questions

 

[jcruzAME]

Okay one more thing. Since it shows the column as an expression, how can I set decimal places? It doesn't show up in the property sheet for the column.

 

[MSAccessRookie]

Okay one more thing. Since it shows the column as an expression, how can I set decimal places? It doesn't show up in the property sheet for the column.

I believe that you can format the result of your User Defined Function to be the type of number that you want.