Re: 'Lord Help Me' Project

I'm very excited about the program now from when I first posted. I'm sure
I'm not doing everything as efficiently as I should be, but I'm proud of what
I've accomplished so far. And you're to be congratulated because I know I
have problems getting my brain to coherently 'output' what its thinking and
somehow in all that fuzzy logic, we intuited what the other meant. scarey,
huh! ;)

Yes, requery is the way I went. However since the user is never permitted to
change any fields in the main form, I ended up placeing a button on the main
form footer and after all the changes in the various subforms are made, the
user then presses the button to get a new total.

The fields in the main form only serve as account identification information,

Go forth and aid other helpless souls!

Thanks again
Laura

"BruceM" wrote:

Glad to learn I helped, but I have to admit I don't quite see how I managed
to do that.

If the total on the main form is correct if you navigate away from the
record, then back to it, it may be as simple as requerying the main form.

Good luck with your project.

"laura reid" <laurareid@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0BDD8D6C-297F-4327-AFD7-1D35297B40D0@xxxxxxxxxxxxxxxx
Bruce,

Oh joy!!! I changed the db format to have 3 separate colums to catch the
different amount types. Whether right or wrong, I have the damn thing
working now the way I want and my forms work correctly (for the most part)
as
well. I do have a couple glitches involving the record navigation buttons
and the total field on the mainform not updating whenever the totals in
the
subform are changed, but I'll post those issues on the forms community.
Thanks for your help or I wouldn't have made it this far!

Laura

"BruceM" wrote:

A few more thoughts inline.

"laura reid" <laurareid@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F9ACDB71-C882-45F2-8C48-959643CB7E57@xxxxxxxxxxxxxxxx
David, thanks for the tip reference the date. This is exactly what I
was
trying to do, however I was entering the expression in the wrong
property.

Bruce,

I have come too far to quit now, and actually I'm only stuck on one
piece
(although big piece) of the puzzle; dealing with subforms, and even
that's
not quite the problem as I can get those designed so the right records
are
matching. The problem lies with the fact that not all the fields are
filling
in when I add a new record to the subform. I think this has to do with
the
fact that my main and subform are based off different queries vs actual
tables.

With what do you expect to fill in the fields in a new subform record?
If
it is with existing information, are you copying it to the subform
record?
In a one-to-many relationship, with a form-subform user interface, the
main
form and subform are based on the related tables (or queries based on
those
tables).

I will process and practice what you advise, and see if I can trouble
shoot
more. I agree with your idea of creating separate fields for the 3
different
amt categories (rqmt, valrqmt & funded) and had originally set it up
this
way
AND had everything working...but the date. I was advised to change
this
structure and that's when my problems started. Not saying the advice
was
wrong, just that I didn't know how to make the data give me what I
wanted
in
that kind of structure.

I can't say that I fully understand your situation, and I certainly don't
know what you were previously advised (or if that advice was based on a
mistaken or incomplete understanding of your situation), but if a
requirement includes a date field you can filter the records according to
the date (all dates from the current year, all dates within the past
twelve
months, etc.).

I'm not clear on your guidance for the relationships though. (I may
have
mistated in my original post). I have created the relationship of
tblReqirements to the other tables through the use of foreign keys. I
called
this my main table because it's the table that hold each individual
transaction. If you think of your checkbook, it's the "transaction
description" column. All the data (fields) in this table are unique
except
where I've used foreign keys to tie the transaction to a certain
account
line, subcommand, command, program, function etc.

To use your checkbook analogy, the line items would probably be on the
many
side of a relationship. Let's say you have two checking accounts.
Account1
can contain or be associated with many transactions, as can Account2, but
the reverse is not true: a transaction would not be associated with more
than one account. When the bank sends you a statement it contains only
the
transactions associated with your account. Your transactions are not
associated with other accounts, even though you may sometimes wish they
were. One account / many transactions, but not the reverse.
You may be saying that each requirement ("transaction description") may
contain any number of entries (for instance, AmountReceived may arrive in
several installments), in which case a related table may well be the way
to
handle that. . However, if AmountRequested and AmountReceived are
separate
records it may be difficult to perform the necessary comparisons.


Is that clear as mud?

Appreciate your help.
Laura


"laura reid" wrote:

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
.