Solved: Setting up "rules" in Access

[philfer]

Hello,

Just curious if I am on the right track.

I have a database which has 3 tables which holds normalised extracts from an accounting system.

I have some static data tables with relationships to the above that hold general ledger codes and the names, employee numbers and names and department numbers and names.

I use queries to summarise the extracted data by general ledger code and employee.

The users want to be able to set up some "rules" in the database i.e. move 50% of revenue from 1 department to the other, move 100% of the profit from one department to the other.

This transfer would then be shown on the report output under an "adjustments" section.

I am trying to provide this functionality.

I am thinking about setting up a table with the following fields :-

1) DeptFrom
2) DeptTo
3) Amount (i.e. %)
4) Value
5) DateSetUp
6) UserName
7) DateRemoved

The fourth field (Value) would be populated by VBA. I thought about getting VBA to run through each row of the table and calculate the value that is to be moved and hold it in this field (VBA may have to run a parameter query for this)

Then I will have to try and find a way to show these adjustments on the reports. Some departments may have 1 adjustment/rule others may have 10 others may have none.

My question is twofold :-

1) Am I on the right track
2) How can I take this to completion

I understand that this is a complicated question but any help/advice/ideas would be greatly appreciated

Thanks
Phil

[OBP]

What other Rules are there, for instance how do you decide that you need to "move 50% of revenue from 1 department to the other"?

[philfer]

Hi,

The rules will be determined by the business side managers arguing among themselves about how profit/costs should be split up!!!!

I will store the rules in the table I suggested but eventually create a form so rules can be viewed, deleted or added, as rules can and probably would be changed in the future

[OBP]

Well you should be able to set it up Ok.
The Query woudl have to refer to the Table/Form of rules to determine if the rule is met and then display the value.
The report would then display the subreport based on that query if there is a Record present.

[philfer]

Hi,

Thanks for that.

Im not sure I explained it correctly.

The user would want to set up a rule for example :-

1) transfer 50% of revenue from dept 1 to dept 3
2) transfer 100% of profit from dept 2 to dept 4

I will set up a table to hold the details of queries.

The database will have to do two things for each rule :-

1) work out how much the amount being transferred is
2) somehow show this on the report
3) work out an adjusted profit for the department

I was going to do this as :-

1) VBA to run a parameterised query to calculate the amount and put it in the table that holds the rules. This would be more difficult for profit as it is not a general ledger item but a calculated amount

2) add the table as a data source for the form. not sure how to do this and also how to hide the section if there are no rules. not sure how to show the amount as positive if it is "to" and negative as it is "from" as one rule will appear on two reports

3) calculated control ?!??

Sorry if I didnt explain it properly......my brain is a bit like jelly!!!

How's the weather in Wales!

Phil

[OBP]

Phil, the weather was clear but with a cold wind today.

Yes you explained it clearly, but I am not sure that your bosses did.
I would have thought that one of the rules would be if Department XYZ has a revenue>£XXX then transfer 50%. The reason for this is that they could have a negative revenue or Profit. So I would have thought that there must be some kind of Trigger to say yes or no.

You can set up the transfer amount by having a new column.
transfer: [Revenue] * Forms![Formname]![Fieldname]
Where Forms![Formname]![Fieldname] is a Decimal value like 0.5 for 50%
Or that value could be taken from the Rules table.

[OBP]

Any chance you can set up a dummy database fro me to look at tomorrow?