Conditional Statements

[Brig Siton]

We are trying to create a reporting function for dues based on the sample
below.

Tables:
Employee
Client
Billing

Employee table has a field/control that holds number of hours worked within
a month

Each employee belongs to a certain client.

Each client has its own level of fee based on number of hours worked.
Example, From 0 - 25 Hours - Fee is 10$, 26 - 50 hours - Fee is $15, 51 -
75 hours - Fee is $20 and so on.

Now we have a billing table that has a firld/control called TotalDue. This
will hold the actual amount due based on the the sliding scale per client.

Can someone guide me on how to automate this calculation either through
query or visual basic on the form.

Thank you very much in advance.

Brigham

 

[Arvin Meyer [MVP]]

The most effective way I know is to build a query to get the total hours,
then use that query in another that uses the Partition () function to
separate the hourly groups. Look up the Partition () function in the Visual
Basic Language Reference in the help files. There is an example there that
you can run in the Northwind sample database that came with Access.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Ron Weiner]

Arvin

I have been doing this stuff for a long time and I was completely unaware of
this function. I have had to solve these kind of problems in the past
typically using a bunch of nested IIF()'s. I can hardly wait until the next
time I have to solve one of these again. Thanks!

Ron W

 

[Arvin Meyer [MVP]]

Partition() is a crosstab on steroids <g>. The one drawback that I've found
in it is that you almost always need to base it on another query which
aggregates the data. For instance, it won't work on dates at all, but if you
use the DateDiff () function to count the number of days between 2 days, it
works fabulously. I use it for aging open service orders, invoices, etc.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access