auto-lookup function? (query, report?)

[Guest]

I need to run reports that calculate user fees for organizations. The first
two users are $250 each, and after that, it's $120 each.

Tables I have that I think I need:
Organizations
Employees
Fee Chart (1 - 250, 2 - 500, 3 - 620, etc., in two columns)

Queries:
Employees per Organization

If there were a way to do an if() function in a query, I'd be done. (Can
you do an if function in a query?) Is there a way to run a report that looks
at the query result, and looks up the corresponding amount from the Fee Chart?

Thanks!

 

[paul]

The function you are looking for is IIF, it can be used in a query, and the
format is User_Fee: IIF ([countofusers] > 2, "$120","$250")


Paul

 

[Guest]

Try something like this:

tblOrganizations
OrganizationID (PK)
OrganizationName

tblUsers (Employees?)
UserID (PK)
UserName

tblOrgUsers
OrganizationID
UserID
OrgUserNumber <begin from 1, for each organization>

Then you can use a Case statement to determine your fee schedule.

intUserNumbers = DMax("OrgUserNumber", "tblOrgUsers", "OrganizationID =
<tblOrganizations.OrganizationID>")

Select Case intUserNumbers
Case 1, 2
curFees = intUserNumbers * 250
Case Else
curFees = (2 * 250) + ((intUserNumbers-2) * 120)
End Select

HTH

Sharkbyte

 

[Guest]

That doesn't work. It's only pulling up the organizations with fewer than 2
users, and then only listing $250 for both 1 and 2 users/org.

The function you are looking for is IIF, it can be used in a query, and the
format is User_Fee: IIF ([countofusers] > 2, "$120","$250")


Paul

I need to run reports that calculate user fees for organizations. The
first
two users are $250 each, and after that, it's $120 each.

Tables I have that I think I need:
Organizations
Employees
Fee Chart (1 - 250, 2 - 500, 3 - 620, etc., in two columns)

Queries:
Employees per Organization

If there were a way to do an if() function in a query, I'd be done. (Can
you do an if function in a query?) Is there a way to run a report that
looks
at the query result, and looks up the corresponding amount from the Fee
Chart?

Thanks!

 

[Guest]

Sorry - what is a case statement?

Try something like this:

tblOrganizations
OrganizationID (PK)
OrganizationName

tblUsers (Employees?)
UserID (PK)
UserName

tblOrgUsers
OrganizationID
UserID
OrgUserNumber <begin from 1, for each organization>

Then you can use a Case statement to determine your fee schedule.

intUserNumbers = DMax("OrgUserNumber", "tblOrgUsers", "OrganizationID =
<tblOrganizations.OrganizationID>")

Select Case intUserNumbers
Case 1, 2
curFees = intUserNumbers * 250
Case Else
curFees = (2 * 250) + ((intUserNumbers-2) * 120)
End Select

HTH

Sharkbyte

 

[Guest]

Sorry, what is a case statement?

Try something like this:

tblOrganizations
OrganizationID (PK)
OrganizationName

tblUsers (Employees?)
UserID (PK)
UserName

tblOrgUsers
OrganizationID
UserID
OrgUserNumber <begin from 1, for each organization>

Then you can use a Case statement to determine your fee schedule.

intUserNumbers = DMax("OrgUserNumber", "tblOrgUsers", "OrganizationID =
<tblOrganizations.OrganizationID>")

Select Case intUserNumbers
Case 1, 2
curFees = intUserNumbers * 250
Case Else
curFees = (2 * 250) + ((intUserNumbers-2) * 120)
End Select

HTH

Sharkbyte

 

[Guest]

It's a method of evaluating a statement, such as:

intUserNumbers = DMax("OrgUserNumber", "tblOrgUsers", "OrganizationID =
<tblOrganizations.OrganizationID>")

for multiple possible values. Kind of like a super-IF statement. (If users
=1, If users = 2, etc.)

In this case, it only takes 2 Cases to evaluate all possible answers to how
many Users an organization has, and will always return the correct fee amount.

And it eliminates the need to maintain a Fees table. You might need to have
50 entries, in your fee table. If you change your rates, every line needs to
be modified. Here, you just update the 2 lines of the Case statement, and
you have your new rates.

You could take the structure a step further, and add a tblFees that stores
the $250 for the first 2 users, and the $120 for all others. Then just
update these lines, in the table, and your Case statement can update with
them. (This would require a small adjustment to the code used vs. the code
offerred.)

Sharkbyte

 

[Guest]

Thanks - that would make sense. Now, where do I put that case statement?

 

[Guest]

One other thing (okay, two!):

OrgUserNumber <begin from 1, for each organization>
-How do I make that happen?

intUserNumbers = DMax("OrgUserNumber", "tblOrgUsers", "OrganizationID =
<tblOrganizations.OrganizationID>")
-what's a "int", and where do I put this?

Again, thanks. This certainly looks like exactly what I need - I just need
a bit more remedial help.

 

[Guest]

Okay...intNextUser is simply a name for a variable. "int" helps identify the
variable as an Integer, when someone - who may not have experience with your
db - looks at your code.

Assuming you have a form that you are adding Users from... (if you aren't
entering data through a form, of some sort, then none of this will really
work for you...)

In the OnClick event, of the Save button, add code for this:

Dim intNextUser as Integer

intNextUser = IIF(IsNull(DMax("OrgUserNumber", "tblOrgUsers",
"OrganizationID = <organizationID>")), 1, (DMax("OrgUserNumber",
"tblOrgUsers", "OrganizationID = <organizationID>") + 1))

Then when you insert your record, to tblOrgUsers, use intNextUser as the
value for OrgUserNumber. If they are the first user, for that organization,
it will populate 1, else it will give them the next number in line.

As for the Case statement, when you want to calculate the fee amount, you
will run this code. There are a multitude of ways you could do this.

One might be simply clicking a button, after you have added a new user, to
get the current fee amount. So you would simply add this code to the OnClick
event, of that button, and use a MessageBox to display the results.

You will have to forgive me, I am a better 'visual' teacher, than I am at
explaining all the steps...

Sharkbyte

 

[Arvin Meyer [MVP]]

Use the Immediate If function:

IIf(expression, truepart, falsepart)

like:

Expr1: IIf(CountOfEmployees<2,$250,IIf(CountOfEmployees<3,$500,0)

then in a second column:

Expr2: IIf(CountOfEmployees>=3,$500 + (CountOfEmployees - 2) * $120,0)

Then in a third column, add columns 1 and 2:

Expr3: Expr1 + Expr2
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access