'Lord Help Me' Project

[laura reid]

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 spreadsheet 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 datasheet 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

 

[BruceM]

If you stuck to what you know you would not be working in your current job,
or for that matter you would never have learned to read or write.

Perhaps somebody else will be able to follow your description, but I may not
be the only one who is puzzled as to your exact aims. Let me suggest a few
things so that the problem is clearly stated.

None of us can be counted on to know the real-world situation you are trying
to resolve. A good starting place is to describe that situation in
non-database terms. When you do so, some explanation of terms may help. I
don't know what to make of "our command's budget", for instance. Then,
describe briefly how the spreadsheet handled the situation. From what I can
tell it captured the necessary information, but was cumbersome.

It does help to describe what you have tried. For one thing, you may be on
the right track, but just need to do some tweaking. For another, people
will see that you have made an effort, and are not just asking that somebody
design the project for you. It can be helpful to outline the structure of
tables, and to describe relationships. It is not necessary to add every
field; a personnel table may look something like this:

tblPersonnel
PersonnelID (autonumber primary key)
FirstName
LastName
etc.

Is [funded] a running total? What are [rqmts] and [valrqmts]? These are
the sorts of things you will probably need to define. I'm not sure how much
I will be able to help with the project, but it may be possible to increase
your chances of getting a targeted response.

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 spreadsheet 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 datasheet 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

 

[laura reid]

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
spreadsheet 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

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 spreadsheet 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 datasheet 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

 

[David F Cox]

If by autofill you mean default value:

format field as integer

default value: Format(Date(),"yyyy")

 

[BruceM]

Laura,

There are a lot of details and considerations here. Sometimes an
off-the-shelf product is the most efficient way to go. If you're in a hurry
it is almost certainly the only way to go. Access is a powerful and
versatile program, but its learning curve is steep.

An oft-recommended book is Database design for Mere Mortals. I think the
author is named Hernandez (Michael Hernandez, maybe). I suspect there are
some anomalies in your design, but I doubt I have the aptitude to advise you
on the details of the project, nor could I take the time even if I knew
exactly how to proceed. I can point out a few things.

You mention the "Main table that holds the 'many' side of the relationship".
Typically the main table is on the 'one' side. For instance, the main table
is the command table (I will call it tblCommand, and will use this naming
convention for all tables), and is on the 'one' side of a one-to-many
relationship between tblCommand and tblSubCommand.

tblCommand
CommandID (primary key, or PK)
CommandName
other fields for information specific to the command

tblSubCommand
SubCommandID (PK)
CommandID (foreign key, or FK)
SubCommandName
other fields for information specific to the subcommand

Each command may have several subcommands, so there is a one-to-many
relationship between CommandID in tblCommand and tblSubcommand. A foreign
key is established through a relationship; it is not defined in table design
view as is a PK. Once you have established the relationship you can build a
form (frmCommand) based on tblCommand and a subform (fsubSubCommand) based
on tblSubCommand. One way to do that is to drag the icon for fsubSubCommand
onto frmCommand in form design view. Click the edge of the subform control
(the thing you just added to frmCommand), click View > Properties, and
verify that Link Parent Field and Link Child Field properties are set to
CommandID. If they are not, click the three dots on the property sheet next
to those properties to make the change.
You can build several layers of forms in this way, but starting with just
these two will give you an idea of how this works.

I think you need to make a distinction between a lookup table (to choose
items for a field -- tblFunction, for instance, seems to serve this purpose,
if I understand correctly) and a table that may be the basis for a form
(such as tblCommand).

I think the way to handle the funding categories is to have a table with
fields for Category, Requirement, ValRequirement, Year, and so forth; in
other words, a record for each category, with fields for information
relative to that category. Next year you would create new records and start
the process again; you can filter so the records from last year do not
appear.

Let me suggest that if you are determined to do this yourself you do some
reading, and try just the Command/SubCommand thing I suggested. Also, while
I believe my approach is sound, I am not completely confident it is the best
approach.

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
spreadsheet 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

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 spreadsheet 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 datasheet 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

 

[laura reid]

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.

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'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.

Is that clear as mud?

Appreciate your help.
Laura

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
spreadsheet 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

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 spreadsheet 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 datasheet 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

 

[BruceM]

A few more thoughts inline.

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

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
spreadsheet 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

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 spreadsheet 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 datasheet
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

 

[laura reid]

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

A few more thoughts inline.

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

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
spreadsheet 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

:

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 spreadsheet 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 datasheet
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

 

[BruceM]

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.

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

A few more thoughts inline.

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


:

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
spreadsheet 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

:

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 spreadsheet 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 datasheet
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

 

[laura reid]

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

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.

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

A few more thoughts inline.

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


:

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
spreadsheet 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

:

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 spreadsheet 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

 

[John Vinson]

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.

Just FWIW, you can put a line

Parent.Requery

or possibly also or instead

Parent.Recalc

in the AfterUpdate event of each Subform which contributes to the form
total; this will make the button press unnecessary.

John W. Vinson[MVP]

 

[laura reid]

You people ROCK! Thanks so much, I'll give this a try at work tomorrow.
Should you get bored here, I've posted a request for help with a query I want
to design in the query newsgroup

with much gratitude,
Laura

 

[strive4peace]

Hi Laura,

as an add-on to what John said, if you are using SUM in a control, it
acts on SAVED records... ever notice the pencil icon in the gray record
selector area to the left of the record? That means that data is not
yet written... a solid right-pointed triangle signifies no changes have
been made to the record since it was saved

if a field is used in a calculation, on the AfterUpdate event of its
control:

me.dirty = false

will save the record

if you are moving out of a subform, you might put this on the OnExit
event of the subform control:

if me.dirty then me.dirty = false

btw, my attention was drawn to this thread because of the excellence
that BruceM has with words...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*