Hello Everyone
I am looking for some major help here. Been trying to struggle with
this for over three days but no resolution and am about to break my
head on this.
I have been asked to automate a process that takes place manually
around here. This is how the process works.
The purpose of the application is to monitor the expenditure of the
company and before a request is approved, the manager intends to see
how much of the budget has already been approved and based on that
he will approve or reject the request. For example, if the budget
set for Marketing is Rs. 10,000, then there can be mltiple requests
that can come to him from the department at various times during the
month. There may be a request for Rs. 2500 at one time, Rs. 3000 at
another time, Rs. 1000 at another time, Rs. 5000 at another time and
Rs. 2000 at another time. But the last request is over the budget
set, so that manager will have to reject it. Right now, the requests
he receives are manually checked each day and the approval process
takes a lot longer which can be addressed by this application.
The manager wants to check primarily the running total of the
marketing expenditure. The request comes from the executives and
then the manager approves it and sends it to the accounts team. The
accountant then generates a purchase order and then gives it to the
vendor. The vendor then delivers the items along with the invoice.
Then the invoice is paid in multiple installments to the vendor. So
the management wants to check the running total of the puchase
orders raised for the expense type marketing and and amount paid as
well. The total amount raised on the POs can be Rs. 50,000 but the
amount paid could be Rs. 25000, which shows a balance due of Rs.
25000 at that time. Within this expense type, there will be sub
expense types as well to get down to more level of details.
There will also be expenses that will be raised without a PO. It can
be telephone bills or electricity bill or water bill or anything
that the amounts cannot be estimated for. This track is needed to
check the total for any expense type in a given month. There is no
budget set here.
Finally there is something called a suspense account. An employee of
vendor is given a certain amount of money ahead of time and after
the actual expense occurs, the person is supposed to return with the
invoice and if the money given in advance is lesser, then the
accountant will give the person the balance amount and if the money
given is greater than the expense, then that person will need to
return the balance amount to the accountant. As an example an
employee may be given Rs. 500 but the expense may have been Rs. 600
in which case the employee will be given the extra amount of Rs.
100. If the expense amount is only Rs. 400, then the employee will
return the extra amount given to him back to the accountant.
So, to summarize there are three expense types. One with a PO,
another without a PO and lastly one where the amount is given ahead
of the expense occurrence and an additional amount may be given or
the amount given may be refunded back. I am confident I can design
the table and develop the application for the later two expense
types. But where I need help is the expense where a PO is involved.
I am not good at DB designing and that is where i need help. I
created over 6 databases, all of them failed and I am not getting
the right numbers. This one I attached here is the latest one and I
put in data without creating any forms to check if my reports would
come out alright but they are not. So looking for some assistance
from some of the gurus here.
tblBudget is the table designed to enter the budget information. I
created the table tblMonthYear with an intention to set the budget
for a given month for a given expense type. So tblMonth year is
connected to tblBudget. Then tblBudget is connected to tblET which
is the expense Type. And each center will be assigned a budget for
each expense type, so center table tblCenter is connected to tblET.
Then tblET is connected to tblPO which is the purchase order table.
Here the POnumber is manually entered after generating a PO from
another file which is in excel. A purchase order is given to a
vendor and hence vendor table tblVendor is connected to the tblPO.
Once the work in the PO is complete, then the vendor gives the
invoice and tblPOInvoices is where the invoice are stored. After the
PO is received, then the payments information is stored in
tblPOPayments and that is in turn connected to tblPaymentMode to
determine the mode of payment which is either by check or cash or
credit card.
I can send the mdb file if necessary but the problem I am facing is
as follows. When I look up the expense total by the expense type,
the budget is getting added as many times as the records in the
tblPO. If one center is given a budget or Rs. 100, and if the POs
raised are for that center and if there are 5 such records, then the
budget amount is shown as Rs. 500. So I removed the sum and put in
group by for the budget and did a sum on POEstAmount and this time,
the POEstAmount gets added up as many times.
So I think there is a problem in the DB design. I can do the forms,
coding and logic that is needed there.
But an expert help on how the tables should be structured will save
the day for me. Like I said, I can take care of the other two
expense types. But this one is really killing me.
Thanks for your help
Sunny
I am looking for some major help here. Been trying to struggle with
this for over three days but no resolution and am about to break my
head on this.
I have been asked to automate a process that takes place manually
around here. This is how the process works.
The purpose of the application is to monitor the expenditure of the
company and before a request is approved, the manager intends to see
how much of the budget has already been approved and based on that
he will approve or reject the request. For example, if the budget
set for Marketing is Rs. 10,000, then there can be mltiple requests
that can come to him from the department at various times during the
month. There may be a request for Rs. 2500 at one time, Rs. 3000 at
another time, Rs. 1000 at another time, Rs. 5000 at another time and
Rs. 2000 at another time. But the last request is over the budget
set, so that manager will have to reject it. Right now, the requests
he receives are manually checked each day and the approval process
takes a lot longer which can be addressed by this application.
The manager wants to check primarily the running total of the
marketing expenditure. The request comes from the executives and
then the manager approves it and sends it to the accounts team. The
accountant then generates a purchase order and then gives it to the
vendor. The vendor then delivers the items along with the invoice.
Then the invoice is paid in multiple installments to the vendor. So
the management wants to check the running total of the puchase
orders raised for the expense type marketing and and amount paid as
well. The total amount raised on the POs can be Rs. 50,000 but the
amount paid could be Rs. 25000, which shows a balance due of Rs.
25000 at that time. Within this expense type, there will be sub
expense types as well to get down to more level of details.
There will also be expenses that will be raised without a PO. It can
be telephone bills or electricity bill or water bill or anything
that the amounts cannot be estimated for. This track is needed to
check the total for any expense type in a given month. There is no
budget set here.
Finally there is something called a suspense account. An employee of
vendor is given a certain amount of money ahead of time and after
the actual expense occurs, the person is supposed to return with the
invoice and if the money given in advance is lesser, then the
accountant will give the person the balance amount and if the money
given is greater than the expense, then that person will need to
return the balance amount to the accountant. As an example an
employee may be given Rs. 500 but the expense may have been Rs. 600
in which case the employee will be given the extra amount of Rs.
100. If the expense amount is only Rs. 400, then the employee will
return the extra amount given to him back to the accountant.
So, to summarize there are three expense types. One with a PO,
another without a PO and lastly one where the amount is given ahead
of the expense occurrence and an additional amount may be given or
the amount given may be refunded back. I am confident I can design
the table and develop the application for the later two expense
types. But where I need help is the expense where a PO is involved.
I am not good at DB designing and that is where i need help. I
created over 6 databases, all of them failed and I am not getting
the right numbers. This one I attached here is the latest one and I
put in data without creating any forms to check if my reports would
come out alright but they are not. So looking for some assistance
from some of the gurus here.
tblBudget is the table designed to enter the budget information. I
created the table tblMonthYear with an intention to set the budget
for a given month for a given expense type. So tblMonth year is
connected to tblBudget. Then tblBudget is connected to tblET which
is the expense Type. And each center will be assigned a budget for
each expense type, so center table tblCenter is connected to tblET.
Then tblET is connected to tblPO which is the purchase order table.
Here the POnumber is manually entered after generating a PO from
another file which is in excel. A purchase order is given to a
vendor and hence vendor table tblVendor is connected to the tblPO.
Once the work in the PO is complete, then the vendor gives the
invoice and tblPOInvoices is where the invoice are stored. After the
PO is received, then the payments information is stored in
tblPOPayments and that is in turn connected to tblPaymentMode to
determine the mode of payment which is either by check or cash or
credit card.
I can send the mdb file if necessary but the problem I am facing is
as follows. When I look up the expense total by the expense type,
the budget is getting added as many times as the records in the
tblPO. If one center is given a budget or Rs. 100, and if the POs
raised are for that center and if there are 5 such records, then the
budget amount is shown as Rs. 500. So I removed the sum and put in
group by for the budget and did a sum on POEstAmount and this time,
the POEstAmount gets added up as many times.
So I think there is a problem in the DB design. I can do the forms,
coding and logic that is needed there.
But an expert help on how the tables should be structured will save
the day for me. Like I said, I can take care of the other two
expense types. But this one is really killing me.
Thanks for your help
Sunny
