RE: 'Lord Help Me' Project

First, thanks for helping me put this in bite size pieces. 2d, I apologize
in advance if my ‘db lingo’ is incorrect.

Database Purpose: Our office (Military command) has a $99M budget that
funds payroll, various contracts, and office daily operations (to include
training, travel and supply expenses). This budget used to be captured on a
spread*** that had rows for each office followed by rows for the fund
category (ie contract, payroll, supply, travel) and then columns to show the
initial funding startpoint amount, a remarks column, a column for funding
changes (increases/decreases) and a remarks changes column, and a total
column. Then with each successive year, the columns were recreated and the
old ones hidden.

I’ve successfully recreated this into a relational database with the
following tables to capture unique information.

Definitions:
Requirement - $ amount the office says is required perform a certain
function at 100% with bells and whistles.

ValRqmt - $ amount that has been validated by leadership to say “no you
can’t have have $50,000 for all your travel needs, eliminate conferences and
you can have $40,000”

Funded – $ amount that usually matches Val Rqmt, but not always. This is
the amount that can actually be afforded, based on what we receive from our
higher headquarters.

Description – gives a title text description of what the requirement is buying

Here are my tables & fields:

Requirements (Main table that holds the ‘many’ side of the relationship)
Description - txt
Amount – number field
Year – date
RqmtRmks – txt to provide short info on the amounts entered (used for
historical purposes and document decision making)
ValRmks – s/a
FunRmks – s/a
WorkYears – number (tells how may people can be funded for payroll)
Rqmts ID – PK
CmdID – FK
SubCmdID – FK
ObjClassID – FK
AcctID – FK
AuditID – FK
PriorityID – FK
FunctionID – FK
AmountID - FK

Naturally each of the FKs above has their own tables (that provide the one
side of the relationship) with descriptions provided below. (Note, some of
these tables could/should probably be combined, but since I wasn’t sure, I
just broke everything out)

Command Table – contains only 3 records for the 3 commands we’re responsible
for.
SubCommand Table – each of the above 3 commands have separate offices (some
up to 12)
ObjClass Table– defines up to 12 different main funding categories, ie pay,
travel, supplies etc.
Acct Tble – provides the customer accounting information
AuditTable – describes the change to the funded amt field. There are 6
options to choose from to document a change
(baseline/increase/decrease/reprogram/unfund/transfer)
PriorityTable – each requirement description is either a level 1, 2 or 3
priority
Function Table – describes what mission function the requirement supports.
Anount Table – 3 records only to describes if the amount is a Requirement,
Validated Requirement or Funded amount.

I then built a base ‘budget query’ that sums and groups with the only change
being how the amount field is captured. I know Rqmt and Val Rqmt amounts
will not change throughout out the year, but Funded will. So in my query I
added three additional columns (one for each amount type) with the following
expression:

Requested: Sum(IIf([amtaction]="requested",[amount],0))

It’s this query that I use as an underlying base for additional
queries/forms/reports. The only field that is missing is the date field
because the date is a mm/dd/yyyy auto fill field and it messes up when I
group. I really only need the date field to capture the year, not the mm/dd
but I don’t know how to do this as an autofill.

So I’ll stop here and ask if this design sounds acceptable?
Thanks
Laura

"laura reid" wrote:

I am SO lost. I started out on something I thought would be easy and have
dug myself into a hole of no return. I promise if I can get this program to
work, I'll stick to what I know and leave the programming to the experts.

I'm in charge of building and tracking our command's budget and converted an
old spread*** that had columns out to infinity into a relational database.
There are other tables that contain account code information, office/unit
information etc, but the main table captures requirements, validated
requirements, and amount funded. Orignally, I had three sep. columns to
capture this data and had my reports, queries, subforms, etc...all working
fine, but realized I had a problem when it came time to switch into the new
year and start over, but still retain prior year info for historical
purposes. After extensive posting on this community site I was later advised
my design was flawed and the best way was to have one column called amount
and then another called amount type where I would choose [rqmt, valrqmt, or
funded]. So I've done this and recreated my reports, relationships, forms
etc...but now I can't get a critical form to operate and I'm so down in the
weeds that I don't even know where to start explaining it.

My goal is to create a form (audit trail) that let's me capture funding
changes that occur through out the year, thus increasing the number of
records to the main table. While the amounts for Rqmt and valrqmt will not
change, funding amts will change based on budget increases/decreases or
reprogramming actions. So in theory, I'd like a main form that identifies
the unit, and accting information, and then a subforms in data*** view
showing the requirement description along with the amount funded as a
startpoint. In the subform I would then be able to add additional records by
duplicating the requirement description and either increasing or decreasing
the funded amount and attach a remark.

Then through the awesome magic of automation, the other fields in the record
would automatically populate with the fields in the main form. See that's
not too much to ask, is it?

I have created a query off the maintable that 'restructures' my database so
I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is
my main query I use to create other queries that sum the amounts and group my
info in a variety of ways. And this is where I get stuck because when I add
a record that changes the fund amount, it never makes it back to the main
table correctly because I never enter in the amt type. The field doesn't
exist in my query and if I put it in, it messes up my groupings.

So my design is still flawed (i just don't understand how) and I think I
have too many tables, but I tried to keep info grouped to avoid a lot of
duplication.

And now I'm rambling...so if anyone out there has an urge to do a random act
of kindness, I'm a willing (and somewhat able) recipient.

Thanks!
Laura
.

Loading