473,320 Members | 2,177 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to incorporate prior year expenses and maintain current balance

418 256MB
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.
Jun 9 '09 #1
12 3535
hjozinovic
167 100+
Hi MNNovice,
I sent you PM on this matter, check it out!
Jun 9 '09 #2
ChipR
1,287 Expert 1GB
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.
Jun 9 '09 #3
NeoPa
32,556 Expert Mod 16PB
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.
Jun 9 '09 #4
MNNovice
418 256MB
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.
Jun 10 '09 #5
NeoPa
32,556 Expert Mod 16PB
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.
Jun 10 '09 #6
MNNovice
418 256MB
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.
Jun 10 '09 #7
FishVal
2,653 Expert 2GB
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.
Jun 10 '09 #8
MNNovice
418 256MB
FishVal

Thanks. It gives some hope that I am not deep into troubled water.
Jun 10 '09 #9
FishVal
2,653 Expert 2GB
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.
Jun 10 '09 #10
MNNovice
418 256MB
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.
Jun 15 '09 #11
FishVal
2,653 Expert 2GB
@MNNovice
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.
Jun 15 '09 #12
MNNovice
418 256MB
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.
Attached Files
File Type: zip NOTES.zip (5.4 KB, 91 views)
Jun 16 '09 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Ken Fine | last post by:
I want my application to maintain a directory tree based on months and years, e.g.: 2004 January file file file February file
3
by: David Hayes | last post by:
I've made tooltips work in Firefox*, but tooltip doesn't appear at the specified location until the SECOND time that the user passes the mouse over the location with the mouseover event. What I...
3
by: Lloyd Stevens | last post by:
TABLES CustomerTable CustomerTarrifTable TarrifTable WarrantTable CustomerID(P) CustomerTarrifID(P) TarrifNo(P) WarrantID(P) BoatType QuantityPurchased ProductName WarrantDate...
248
by: Generic Usenet Account | last post by:
As per Google's Usenet archives http://groups.google.com/googlegroups/archive_announce_20.html], the first discussion of the Y2K problem on the Usenet was on January 18 1985...
11
by: Kevin | last post by:
I have a problem I'm trying to figure out how to resolve in Access. I have a volunteer database for a project which occurs each year. Currently I have to go in and wipe out the data each year to...
2
by: Bernd Hohmann | last post by:
Dear collegues, small query problem. A table (simplified example)... customer as char(5) inv_date as date amount as double
2
by: doomsday123 | last post by:
I want to create a query that will subtract a given number from a value in the database and store it in the same spot the value was. For example, a customer has a balance of 23.22 which is stored...
1
by: tmcjunkin | last post by:
I'm trying to back into this project by defining what the user needs to see when they open a form. I'm between beginner and intermediate with vba, but have been using access for years. When the...
1
by: darendaren88 | last post by:
I have created this table: mysql> describe acount; +----------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra |...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.