How to incorporate prior year expenses and maintain current balance

[MNNovice]

I am designing a db to maintain grant related budget and expenses.

tblGrants and tblGrantBudget include data such as grant title, grant revenue amount etc.

Each grant has an authorized amount from the Fed. Each time we submit an expense report, we get reimbursed - this is called Echo draw. We need to keep track of these draws. So basically if a grant has a total of $100 and we submit $20 expense the remaining balance will be $80.

To record these draws I have 3 separate tables (tblAP, tblPayroll and tblAllocation). These give me reports on each Echo Draw for each category etc.

This is rather simplistic way of putting it. What happened is that there are expenses incurred prior to my taking up on this DB. Now my questions are:

1. To keep track of these draws, do I need a separate table like tblEchoDraw or tblGrantExpense (it does not matter what we call it)?

2. How do I tie tblEcho, tblAP, tblPayroll, tblAllocation (these are expenses) with tblGrantBudget? With a new table called tblEchoDraw and show a balance?

3. Since there are expenses from prior year do I hard code these amounts somewhere and add to on going expenses. If yes, where do I hard code this data?

Can someone please help me with some directions? Many thanks.

[hjozinovic]

Hi MNNovice,
I sent you PM on this matter, check it out!

[ChipR]

I think you know that the cycle of repeatedly reaching a working database, then adding new structure requirements is not a good practice. Consider doing all of the designing first so that you don't waste your time, or that of people trying to help you.

[NeoPa]

I'm with Chip on this one M. I think I mentioned a while back that, complicated though it may be, this is something you can only really do for yourself I'm afraid. You've already seen how hard it is to understand questions unless they are explained very clearly. Apart from this though, unless you go through this process yourself, personally, you will not have the understanding you will need to progress further.

It is also very necessary (as I also said) to get the fundamental design right before continuing to develop the project. Otherwise you will likely end up going around in uncomfortable circles, and probably taking others here with you for the ride.

I would say a good rule of thumb for a project is that if someone is unable to do the fundamental design, then they are probably not suited to take on the project.

This probably sounds quite harsh to you when you read it. It's not meant to. These are just fundamental ideas that I think you need to get to grips with.

As far as directions go, I can only post a link to Mary's Normalisation and Table structures article which I know you've already seen.

[MNNovice]

ChipR / NeoPa:

These are all very good suggestions, concrete critique and valuable lessons. I sincerely appreciate.

However, in real world, it cannot be expected to gather "all the possible" data before launching on a project. It becomes even more impossible when the end users don't have the thorough knowledge of their needs nor can they comprehend the depth of knowledge required to satisfy those needs.

But one still ventures to do something she/he has never done before. If we don't take a risk we will never gain anything. We may not have a complete perspective of the whole process but we can discover things along the way. It is possible to learn things as one takes up on a task. An engineer who always worked for a company may end up being one of the best businessman working for himself. He may not know accounting. Or he may be completely unaware of tax implications of a business decision. But with the help of a CPA he will learn these tools and can become very successful businessman.

I am looking at my project as a much simpler task than finding cure for a desease. It may take me longer since I don't have the technical knowhow of doing it. But I am willing to learn as I go forward.

Thank you all for your time and effort in helping me. It's greatly appreciated.

[NeoPa]

All very valid points M, and well expressed too.

It is generally not possible to set the spec in stone when users (and those requesting the project) do not understand the importance of such things. The important thing is always though, to get this design phase of the project set up after any changes are made to the spec and before proceeding with any other aspects. I suspect you are already doing this.

As far as help is concerned on the actual structure after a change is made. This is where it gets very complicated. In a way you're on your own. We can provide general pointers, but we can't (too complicated to do remotely for all but simple projects) and shouldn't (you would not be in a position to continue properly unless you are fundamentally driving and fully understanding this stage) do more.

Conceptual questions in this area we can, and are happy to, answer.

[MNNovice]

NeoPa:

Thanks for your comments. I believe, I am still at the design phase of the task. But I am unable to understand the full scope of it since I am not the end user. As such, I must, use some sample data to find out the details of the task. What kind of reports are needed, what are the fields required, what forms I will need to enter data ...etc.

I have been able to generate various reports that are needed. Right now I am trying to figure out how to generate reports to show balance of budget left for a grant. Some of these grants were awarded long before I got here. The analyst who maintained these expenses did everything in Excel which you know can be tedious for the scope of work. The end users spend hours as the data entry were repeated for several spreadsheets (one for fund, one for ECHO, one for grant etc.). With Access it takes me 20-25 minutes on average. The same task takes them more than half a day...

I know I must hard code the prior year expenses for each grant. I just cannot figure out which table? Perhaps tblGrantBudget. And then do a union query with tblAP, tblPayroll and tblExpenses. Today I am playing with this idea.

Anyway thanks for your time and offer of help. It is much appreciated.

[FishVal]

Frankly speaking, I couldn't find here any problem at all.
Since your database holds and processes data of some time period, addition data from some precedent period is not expected to require additional design changes.

[MNNovice]

FishVal

Thanks. It gives some hope that I am not deep into troubled water.

[FishVal]

You are welcome.

Sorry, I couldn't be more specific since business rules of your database , to say the least, are not clear for me as well as your database design.

Regards,
Fish.

[MNNovice]

Fish:

If the content or objective of the DB is not clear I can try to briefly explain. Is that what you meant when you said,

I couldn't be more specific since business rules of your database is not clear

?

Thanks.

[FishVal]

Fish:

If the content or objective of the DB is not clear I can try to briefly explain. Is that what you meant when you said, ?

Thanks.

Ok.

I think seeing the layout of your application business rules mentioned in Calculate net amount based on conditions thread would be nice.

Regards,
Fish.

[MNNovice]

Fish:

Here is the DB info in a nutshell. Hope this makes senses. Also posted are the various tables so far I designed. Let me know your thoughts. I am having difficulty calculating Net amount based on the split ratio.

The DB is to record receipt and withdrawal of federal grants. Each grant thus has two parts: Revenue and Expenses.

Both the revenue and expenses has a string of accounts which consists of Account No., Fund No., Grant No., Org No., Program No. Sub Class No., and Project No.

A grant can consist of various projects and a project can be further divided into sub classes. A grant can have one fund number. Also a fund can have more than one grant number.

The tricky part of a grant is that it can have various split ratio. That is, it can have 80% Federal funding or 90% or 95% depending on the terms of a grant. So if we have $100 expenses, we will be submitting reimbursement based on it split ratio.

The reimbursement process of a grant is called ECHO.

There are 3 categories of expenses for a grant: 1) AP expenses (payment on invoices), 2) Payroll expenses and 3) Allocation Costs.

So basically this DB is to record all receipts and expenses of a grant and generate various reports by category of expenses, by funds, by grants etc. And to keep current balance on each grant for a given date.