Loop Through Values-Rules Checking

[Steve B via AccessMonster.com]

Hi,

I am building an application in which people will be required to approve an
entry based upon dollar amount and type of entry. These approvals will be
predefined by the users on a rule setup screen. Thus if type of entry = x
and dollar amount is > 0 and dollar amount < 50000 then group xyz needs to
approve.

My question would be: Once the transaction has been input and saved I would
like to go through a routine validating what type and what dollar amount it
is for and then compare these values to the rules that had been previously
setup.

I'm am struggling with the best way to do this, both in terms of how I should
save the initial rules(I'm leaning to complete strings in the appropriate VBA
format)and in terms of how to do what the aforementioned paragraph described.

Any help or a push in the right direction would be much appreciated.

Thanks in advance.

 

[Steve B via AccessMonster.com]

As a further point...My current setup would be two tables for the rules.
Main would be a table with Rule ID and the Approval Group.
The second would be a subtable with the actual rules...ie $$$(field1)

(field2) 100,000(field3)...All additional records would be treated as ANDS(No ORS).

Thanks in advance.

 

[David C. Holley]

If I follow things correctly, you want to be able to control who has the
ability to approve certain transactions by dollar amount? If so, its not
entirely neccessary to do the validation afterward. The trick is to
stop the person in their tracks when the try to approve the transaction,
or better yet, only show them the transactions that they're can approve.

In order for this to succeed, you'll first need to have the users in a
position where they have to login to use the database. If you don't have
this in place, everything else is pretty much useless.

Also, you'll need to decide on how you want to have the rules setup. Do
you want a situation where the authorization amounts can be customized
for each person? Do want it setup where each person belongs to a group
and the authorization amounts apply to each person in the group? Do you
want a combination of the two - where the group to which the person
belongs has specific amounts that they can authorize with the person
having set amounts within that range? (The group can authorize $10,000 -
$50,000, but the person can only authorize up to $25,000.)

let me know and then post back

David H

 

[Steve B via AccessMonster.com]

Hi David,

How it needs to work is that you have a low level person entering the data
most likely...They will probably never be approving it. There will be groups
setup of existing users. The rules will be completely customizable and when
a rule is made up they would assign a group to correspond to that rule. When
members of this group next logged in they would be shown there action items-
ie items needing there approval. As a side twist you could have a group of
six people in which any 4 for example can approve it. Also within a group
you have the option to specify that certain people must approve it. So for
the previous example perhaps 2 people have to approve but any 2 of the other
4 can complete it making up the 4 total.

Hope this helps and isn't too convoluted.

thanks.

 

[David C. Holley]

So not only are you dealing with having SOMEONE approve a transaction
there will be certain number people in the group the MUST approve it?

 

[Steve B via AccessMonster.com]

That is correct...Ideally once the routine figured out which group must
approve something there names would be populated into a seperate table until
they approved it...Once they approved it they would show up as approved and
date stamp it. Once all of the necessary approvals were there the item would
be approved and the table for that item would be cleared.

 

[David C. Holley]

What are the items to be approved - checks, line items on an invoice,
etc.? Where/how is the information for the items stored? I should be
able to give you hard and fast direction after I understand that last
component.

 

[Steve B via AccessMonster.com]

It would be an AFE, so they would actually be approving the whole form. The
information will be stored on a seperate table and when the person inputting
feels that it is good they will click a button to process it which will then
run the rules that had been setup and evaluate which rules are true.

 

[Steve B via AccessMonster.com]

Hi David,

I believe I have actually got this to work...Maybe not in the most eloquent
way(took 4 queries), but it appears to work...

What I did was use the first query to evaluate if each rule was true or false.
...So for each afe you may have had 1 half of the rule True and one false or
both True on my testing. I then split up all of the trues to one query and
the false two another. The fourth query was a find unmatched query comparing
the trues to false...Thus if a rule was half true and half false it would not
show up on the unmatched query...


That's it in a nutshell...Leads me to other problems(ensuring rules are
mutually exclusive with no overlap when first setup), but what can you do...
lol.

Not sure if you had a better solution and I would definitely be willing to
change if you had a better solution in mind.

Irregardless thanks for your help.

 

[David C. Holley]

AFE?

 

[Steve B via AccessMonster.com]

Authorization for Expenditure

 

[David C. Holley]

Well if its working, its working