A lot of tables or lots of spaces?

[danimani]

Hey everybody,

This is my first post so, hi everyone

Background to project:

I'm trying to set together a database which is supposed to function as a storage database and which should be able to produce a monthly overview in excel. More specificly, what I want stored in the database is i) user-created rules (e.g. 'x=1<6', '1<x>100' etc.) which the user, through a form, can test against a self-input number (e.g. does x fulfill the rules if its '1') and ii) logs if a user changes a rule after creation (which they should be able to do) or, if the formula is a bit more advanced (if 'x<10 then y = 0,5 or x>10 then y = 0,4'), log if y changes.

X would also be required after a certain amount of time (which differs depending on what the users have choosen when they make the rule and which rule its refering to, as it can be multiple rules). So a user should be able to open a form and see what rules has to be tested against in a given timeframe. Oh, and every rule adhere to a certain object.

So it goes something like this:

ID of object-> User create rule(s) -> User input numbers which are checked against the rules -> program stores everything.

If anyones familiar with economy, its basicly a program for checking if a financial covenant is what its supposed to be. If not, then change in margin or whatever.

The questions:

1. I'm wondering if I should create a different table for every object (company) rules or if I should store them together?
2. Can it be done (I know it can be done, but if your modern at access, is it to big a task)?
3. Really anything else you can come up with; e.g. good books, existing access databases which does the same, what to think about etc.

Thanks in advance guys and girls!

D

 

[georgedwilkinson]

1. Store all similar data together in the same table. Don't store dissimilar data in that table (don't store the names and contact information of the person who created the formula in that same table, for example).
2. a. Yes, b. Yes, it is a big task.
3. First thing I thought about was "why can't he/she just use Excel to create formulas/crunch numbers"? I'm sure you have your reasons, but writing code that parses and interprets code is a very complex task. I tried it once and found I ran out of budget very quickly. Of course people do it all the time, I just don't think it's something you should enter into lightly.

 

[danimani]

georgedwilkinson,

thanks for the input!

I have a feeling it can be quite a lot of work, but I don't know how to solve the problem in another way. As the rules for the financial covenants change over time and are different to each company, I cannot see how you could get an overview in excel? For some companies you have 3 covenants or more, and for some just 1. To add to the complexity, the covenants are to be reported at different intervals for most companies and the system has to keep check of this (the time-problem should'nt be a issue though, it can be done in excel as well as access quite easily I think).

How would you set up such a worksheet in excel? If you have a good solution, I'm definitly all ears!

All thoughts and comments are apreciated!
D

 

[dsigner1]

Giving the user complete freedom for the rules equates to a complete migraine for you. Can you negotiate a compromise? If you can store a reasonably comprehensive set of rules to choose from then your task becomes possible in reasonable time.

 

[gemma-the-husky]

another way of putting it ....

are you just saying you want the user to be able to filter information based on a set of as yet unknown parameters?

within reason this is achievable

--------
however there are sensible limits - the thing is, access (like excel) was expected to be used and operated by users. We are effectively REMOVING much of this functionality from the users, to simplify their use of the system, and it is not practical or even possible to replace all this functionality, but still maintain the dbs in a robust way

so either

train the users, and let them do whatever they want (not recommended - which is why we manage their interaction with the database via forms etc)

or

provide a sensible set of options, but then
also allow them to extract a (substantial)/full set of data to excel, then they can answer oneoff/esoteric dbs enquiries on their own

 

[dsigner1]

Gemma,
I am not sure that I unstood the original question that well. I did not think that thew rules were just filters. I am not advocating that the users be deprived of choice in general. Just here it seems that a little bit of work would provide a fixed rule set which would give them 90% of what they reasonably need. if so this is a cost effective design particularly when compared with the absolute freedom to set rules some of which may not make sense.

 

[gemma-the-husky]

dsigner1

yep i agree entirely

80-20 rule at work probably, you can meet 80% of requests with 20% of the work - and its then a question of whether its worth or even possible to achieve the other 20%

 

[The_Doc_Man]

This is an advanced project in text parsing and finite-state automata theory. Maybe even non-finite automata. {shudder}

Your main issue is the open-ended and potentially complex set of rules you would allow. I would offer this thought. This would be a project worthy of a college senior's high-end database class and would represent at least 50% of the final grade. OR for a finance curriculum, it might even be a worthy part of the project supplemental to a master's thesis. The earlier suggestions to have pre-defined rules that could be applied were other forum members trying to reduce this problem to something that a less experienced Access programmer might accomplish in something less than several months. You have to decide whether you are experienced enough to handle a truly non-trivial problem. I make no assumptions as to yea or nay on that one. I merely point out that this project is not for the faint of heart.

If the suggested kinds of problem reduction are not within your mandate, you are facing a major design effort that in my educated opinion would take an experienced programmer something like 4-6 months solid, dedicated work with candles burning at both ends and probably a serious amount of personal stress.

I'll suggest that you look into the EVAL function (or is it EVALUATE? I'm posting remotely from my primary site and can't look that up right now.) It has the ability to offer you run-time interpretation of expressions. You would have to pay strict attention to the parsing rules that it describes under the Access Help topics related to it.

For a small number of rules per person, EVAL might be fast enough to provide some of what you need. For larger numbers of rules, it will be very inefficient because to do what it does, it must interpret text at run time. I don't envy you this project. Good luck.

 

[danimani]

Thanks everybody for the input!

I feel that I have to explain a bit better what I'm trying to achive. So to clarify, the users are only going to add rules that are in the basis of:

x = some covenant

if x<=10 then rating is Y and if not, then it's not fulfilled (and the system would flag it as unfulfilled). So it's only comparing rules and basicly quite easy one's, like some number< or > or => some other number (I just need the people using it to be able to change the rules or add/remove a rule in the future without me having to to it all the time). The tricky part is that it's sometimes 2 covenants that needs to be checked (for a given company) and sometimes more (for another company). Also, sometimes you get a ratchet, which I'm not sure as how to tackle yet. And the reports with the covenants come in different timeframes, so I have to have a good system for actually doublechecking that something isn't missing.

So, this is how I'm thinking about doing it (I have started a little bit):

Have a frmAddCovenantRule (continues form, not able to add entries directly) where the user should be able to put in a number in a textbox, and the form automaticly gives out the equal amount of records for the user to fill in (I've seen it done with a sql update - add information - to the table below and requerie the form). The user can then:

Name the rule (textbox, type text)
Choose < or > or <= or >= or = (textbox converted to combo, value fields)
Add a number (textbox, number)
Give a startdate (textbox, date)
Choose a period (annually, quarterly etc) (textbox -> combobox, value fields)
And the system would give the next reporting date (dateadd("q" etc))

The user would then go to another form, frmReportCovenants, where the system should automaticly pick up how many rules the user has entered for a specific company, and let the user input the numbers that fall under the current timeframe (for example Q2) and evaluate them against the above rules (perhaps a query that adds the second textbox with the third and evaluates it?). I'm not so sure as of how to make the system populate new records for the number of userinput rules (and how to make it not show older rules or likewise).

Perhaps this have shed a bit of light of my mission

Every thought and comment is appreciated! With that said, if no one can give me a good excuse (and example) of how you would make this in excel (and make it userfriendly too), I'll try my luck with access.

Cheers,
D

 

[The_Doc_Man]

x = some covenant

It is the "some covenant" part of that expression that is the headache. Not to mention

sometimes 2 covenants that needs to be checked (for a given company) and sometimes more (for another company)

Your problem is currently definitional in nature. That is, a design cannot successfully begin without a proper definition of the problem - and in more detail than you have shown us. (No, not necessary to provide it. YOU have to have it in order to succeed. We aren't going to do it for you so we don't actually need to see all of the details.)

As long as these covenants can be complex, your problem is complex.

First bit of advice - look for ways to abstract a problem into its parts.

For example, are ALL individual covenants "X relation some-number" where the relation is a limited number of possible relational operators and the some-number is a constant? Is there ever a more complex formula such as "X^2 > some-number" or "X/2 < Y+12" (Where Y is some other variable)? Define those rules first. If you can do that, you might find that all rules could be managed by a SELECT/CASE statement on the operator as selected by a combo box. In THAT case, you would do something like a running sum or product of all coventant results to see if any came out zero (=false). You would still need some sort of public VBA function to do it, but it WOULD simplify matters.

If it stays open-ended with complex covenant interactions, the above doesn't apply.

 

[danimani]

Thanks for your input The_Doc_Man.

The covenants will never be more complex then:

a) compare a number against another number (e.g. x > 5, where x would be perhaps a 3)
b) compare a number against a margin ratchett, example:
if X is between 4 or 5 then margin 2
if X is beteen 3 or 4 then margin 1,75
etc

What I mean with there can be many covenants means that one company may have three different covenants (which all are solved by fairly easy rules as above) which should have rules and be checked against future reporting. And I have no idea what programers do on their courses as I don't study programing. I do this because I think it's a good challenge and if I pull it off (which I intend to do), it will make some other person happy (always nice).

I don't really understand why I wouldn't share my work; that's just stupid. Further, I am already working with this, so I don't really understand why that has to be pointed out. I still think it's a good idea to actually exchange tips of how this can be managed, but if you all feel like I'm stealing your idea's or something similar, let me just thank you for your replies and apologize for wasting your time.

Otherwise, as always, all ideas and thoughts are welcome.
Cheers,
D

 

[danimani]

The_Doc_Man,

thanks for the tip about Eval! Had never found out that one myself... I think it could be a quite good way to evaluate the rules, as it I cannot fathom that there will be more than max 5 rules at any given time that will be evaluated (I will of course try to be 100 % on it also...). I'm still not sure how I will lay out the tables (as I will have dates, and as I understood it, its not considered good programing to store dates that will be evaluated?), but I'm currently reading Clare Churcher "Beginning Database Design" and are hoping for more clues.

Anyway, big thanks the_doc_man!

Cheers,
D

 

[gemma-the-husky]

i am not familiar with the term "covenant" - i presume we are talking about some sort of test.

so thinking again i would have a structure type record for a covenant storing things like

active/effective y/n
comparison value
comparison method
etc, whatever you need

and store all these in an array of covenanttypes

then you can set the active functions/covenants in a form somewhere, and just
have a single function that examines all the structures in the array, and determines whether to evaluate them or not, and how they should be evaluated.

does this work?

 

[danimani]

Gemma-the-husky,

I think I understand your reasoning, however I have no idea how that would be done in practice. Could you perhaps direct me to an example (if any) or explain the process a bit more in detail? I'm liking the idea of having a single function going through an array of covenants...

Thanks in advance!
D

 

[gemma-the-husky]

define the generic structure, and dim an array

type tcovenants
  active as boolean  'whether to check this one or not
  checkvalue as string  'what to check against 
     comparisonmethod as long 'how to check it
     'eg 
     '1 = must be the same'
     '2 = must be greater than
     etc
end type

dim mycovenants(10) as tcovenants

then in code, set covenenants as appropriate, and test then

function testcovenants(valuetotest, covenant_to_use) as boolean
with mycovenants(covenant_to_use)
  if .active then
    ...compare value_to_test with .checkvalue using .comparisonmethod and return the function as true or false
  end if
end with

 

[danimani]

Gemma-the-husky,

thank you very much for the example! I really appreciate you taking your time writing it and I will fiddle with the example and see if I can come up with something... Right now I'm struggeling with the database design, so lets see when I feel more comfortable with it...

Cheers,
D

 

[danimani]

Gemma-the-husky,

thank you for your example (I thought I replied earlier but the computer I'm currently on isn't the best so...)! I will test it during the weekend and - most certainly - ask some more questions as the system develops.

Cheers,
D