Database design for allotments/disbursements

[Long Tom Coffin]

Hello! A little familiar with access, and new to the forums. I’m designing an allotment / fund control database for my employer (a Fed agency). I have a pretty simplistic grasp of Access fundamentals (I’ve designed small, simple databases for reports, other than that I’m very green), and this is going to be a pretty decent sized database. Since my boss knows that I know at least a little bit of Access, this got dumped in my lap. Unfortunately for me, there is no option to say “no” or try to pass the buck onto someone else. It gets done or there will probably be problems.



Problem I’m running into to start is I’m kind of unsure as to how to set this up to start. The allotment process, and the fields I will be using will go like this:

First, we get an allotment from the Feds. Let’s say $50,000.

I will need to be able to record this Allotment #, Allotment Amount, Allotment Date, The Funding Year, and a brief description.

After getting the allotment funding in step 1, it has to be input using the information above. After that happens, we move on to step two, or obligating part of the allotment funds to various vendors/borrowers. This may happen weeks or months after we get the allotment. When funds are obligated, it is recorded by Vendor/Borrower Name or ID, Obligation amount, Obligation Date, Obligation Document #, and so on. Pretty simple.

Here’s where we get to the part that is throwing me off, the disbursements of the obligated amounts. See, if we received an allotment for $50,000, obligate $25,000 of it to Vendor/Borrower XYZ, XYZ may take all of the obligation at once, or bits and pieces of it at a time. So they may get $5000 one month, $6000 the next, and so on, until the total obligation amount is fulfilled. These disbursements are made on a contract basis so there may be multiple line item disbursements per obligation/vendor, each one under a different contract for the same vendor and obligation.

So, in summation, we get an allotment. Under this obligation there will be a series of unique obligations to different vendors. Under each of these unique obligations there is going to be multiple different contracts resulting in line item disbursements of the obligated funds. There could be 1, 5, 10 or 15 contracts per vendor. Each contractual disbursement is recorded via a unique invoice. I need to be able to record all this activity and calculate the unliquidated/liquidated contract line items, obligations, and allotments (although I pretty much know how to do that with the queries).

I just don’t know how to go about setting this thing up. Should I do one massive table? Should I do several smaller tables using relationships?

I’m stuck, and don’t know where to go from here.

Thanks!
M

[jzwp11]

Welcome to the forum!

One massive table is not the way to handle this.

Since each allotment can have many obligations, that describes a one-to-many relationship which requires a separate but related table. Further an obligation has many dispersements, so another one-to-many relationship.

So first, you will need a table for the allotments

tblAllotments
-pkAllotID primary key, autonumber
-AllotNo
-AllotAmt
-AllotDate
-FundYear
-AllotDesc

Now for each allotment, you have many obligations & each obligation is tied to a vendor

tblObligations
-pkObligID primary key, autonumber
-fkAllotID foreign key to tblAllotments, must be a long integer number datatype field
-fkVendorID foreign key to tblVendors, must be a long integer number datatype field
-ObligAmt
-ObligDate
etc.

tblVendors
-pkVendorID primary key, autonumber
-txtVendorName
etc.

Since each obligation has many dispersments:

tblDisbursements
-pkDisburseID primary key, autonumber
-fkObligID foreign key to tblObligations, must be a long integer number datatype field
-ContractNo
-DisburseDate
etc.

Now if a contract # can be related to many disbursements, then I would recommend having a separate table to hold the contract info and relate it to the disbursements table with a foreign key. Like this:

tblDisbursements
-pkDisburseID primary key, autonumber
-fkObligID foreign key to tblObligations, must be a long integer number datatype field
-fkContractID foreign key to tblContracts, must be a long integer number datatype field
-DisburseDate
etc.

tblContracts
-pkContractID primary key, autonumber
-contractno
etc.

[pbaldy]

FYI, I changed the title of your thread to something more meaningful than "A DB project got dumped on me, and I'm stuck".

[Long Tom Coffin]

Welcome to the forum!

Thank you for that summary! That was enormously helpful and gave me just what I was looking for.

[jzwp11]

You're welcome. Good luck with your project.