Need advice - how should I set this up?

[justin]

Hello again,

The boss has me working on another database. I guess they liked the first
one I created. Anyways, I'm pretty sure this one is more complicated than the
last.

I'm creating a database to track grant monies. Specifically, the amount of
money, the projects associated with each grant, what was achieved in the
project, and the payments made. Then in the end I need to able to pull
reports from the data showing final figures for the work accomplished, the
total project cost, and any remaining funds.

There can be several projects associated with an account number. Each grant
is associated with an account number. So, for each grant, there can be
multiple projects.

Each project is awarded a dollar amount of the grant, which is then paid in
installments with matching amounts coming from the recipient, ending in a
total project dollar amount.

Below I have listed out what I've created so far.

AccomplishmentsTbl
ProjectID <-- primary key
Acres
DS
Thin
FuelBreaks
Slash
Prun
RxBurn
Plans&Assessment
I&E

GrantInfoTbl
ProjectID <-- primary key
Account# <-- primary key
Project# <-- primary key
FFY
DistrictPriority
District
CongDist
GrantName
Request
Awarded
DateAgreementSigned
POReceived
Practice
Extensions
Notes
WestGrantApp#
BLMGrants
WSFMGrantAmount
GrantAdmin
AmountforIndirect
AdminAdded
AmountofAdminforImp
AmountforIndirect(%foradmin)

PaymentsTbl
ProjectID <-- primary key
Payment1
Payment1Date
Payment2
Payment2Date
Payment3
Payment3Date
Payment4
Payment4Date
Payment5
Payment5Date
Match
TotalProject
AdditionalFundsRequested
Balance

RecipientsTbl
ProjectID <-- primary key
Name
StreetAddress
City
State
Zipcode
County
Community
Phonenumber
Fax
E-mail

This is the current setup. I'm thinking I might need to have a table for
grants and a table for projects? Not sure though. Also, all the projectID
fields are linked to each other in a one-to-one relationship with the
projectID field in the GrantInfoTbl. Is this correct?

So, I guess I'd like to know if this looks correct. I know some of the
fields near the bottom of the GrantInfoTbl won't have data in them for some
of the grants and projects. Should I put those miscellaneous fields into
their own table? Also, some of those fields listed are calculations, should I
remove them completely?

Basically I'm confused on how many tables I should be using, assigning
proper primary keys, and relationships with this database. If someone could
help me out it would be greatly appreciated. Thanks in advance for any and
all advice.

 

[Rick B]

One thing I noticed is your payments table. You have it set up to store 5
payments. I would think this would be a one-to-many relationship where you
could create one or more records for the payments. What if you get the
entire money in one payment? What if you get it in 10?

 

[justin]

We make the payments to the recipient after they've completed some of the
project. Over the course of 3 years of spreadsheets currently keeping all
this information, the greatest number of payments has only been 4.

what do you mean by "a one-to-many relationship where you could create one
or more records for the payments." ?

 

[Vincent Johns]

We make the payments to the recipient after they've completed some of the
project. Over the course of 3 years of spreadsheets currently keeping all
this information, the greatest number of payments has only been 4.

what do you mean by "a one-to-many relationship where you could create one
or more records for the payments." ?

The "many" would be records in a [Payments] Table, in which each record
would contain, for example, a [Date] field, [Amount] field, etc., and a
[ProjectID] link (called a "foreign key") to your [PaymentsTbl] Table.

Incidentally (this is a stylistic comment), even though you can give a
key any valid field name, I think calling the primary key in
[AccomplishmentsTbl] the same thing you call [PaymentsTbl]'s primary key
is confusing. That way, any time you refer to it, you must include the
name of the Table as well as the field. And I think it doesn't make it
very easy to remember what that key means. (End of soapbox.)

The "one" would be the (unique) record in [PaymentsTbl], which you
identify via [PaymentsTbl].[ProjectID], to which all of the payments apply.

Even if you have only two such payments, keeping them in a separate
Table would allow you to simplify maintenance. For example, if you
needed to change your [PaymentsTbl].[Payment2] field in some way, such
as the way it's formatted, right now you'd also need to change
[PaymentsTbl].[Payment3] and others. If you had just one such field,
for example [PaymentTransactions].[Payment], you'd need to change it
only once and all affected fields would be corrected at the same time.

Even if you don't plan to make any changes, having fewer fields avoids
wasting space, and there are fewer fields to document. For example, I
almost always include an explanation, in the Table Design View's
"Description" field, of what each field in a Table is supposed to
contain. Access doesn't care and would work just as well if I left it
blank, but it helps me to remember what is supposed to be stored there.

 

[justin]

Ok, so you're saying I can simplify my [paymentsTbl] by removing all but one
payments field, and all but one date field? Then making the projectID field a
one to many relationship with the projectID field on [GrantInfoTbl]?


I have another question also.

Like I mentioned in my original post, each grant has many projects
associated with it. So, with my current design, will I be able to enter in
multiple projects under each grant without having to re-enter all the grant
information each time? Or do I need a separate table for project information
and a separate table for grant information?
--

The "many" would be records in a [Payments] Table, in which each record
would contain, for example, a [Date] field, [Amount] field, etc., and a
[ProjectID] link (called a "foreign key") to your [PaymentsTbl] Table.

Incidentally (this is a stylistic comment), even though you can give a
key any valid field name, I think calling the primary key in
[AccomplishmentsTbl] the same thing you call [PaymentsTbl]'s primary key
is confusing. That way, any time you refer to it, you must include the
name of the Table as well as the field. And I think it doesn't make it
very easy to remember what that key means. (End of soapbox.)

The "one" would be the (unique) record in [PaymentsTbl], which you
identify via [PaymentsTbl].[ProjectID], to which all of the payments apply.

Even if you have only two such payments, keeping them in a separate
Table would allow you to simplify maintenance. For example, if you
needed to change your [PaymentsTbl].[Payment2] field in some way, such
as the way it's formatted, right now you'd also need to change
[PaymentsTbl].[Payment3] and others. If you had just one such field,
for example [PaymentTransactions].[Payment], you'd need to change it
only once and all affected fields would be corrected at the same time.

Even if you don't plan to make any changes, having fewer fields avoids
wasting space, and there are fewer fields to document. For example, I
almost always include an explanation, in the Table Design View's
"Description" field, of what each field in a Table is supposed to
contain. Access doesn't care and would work just as well if I left it
blank, but it helps me to remember what is supposed to be stored there.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[justin]

bah, I forgot to mention.

All of those projectID fields are autonumber types. Is this right, or should
only one of them be autonumber and rest number type?
--
I've never used Access before. HELP!

Ok, so you're saying I can simplify my [paymentsTbl] by removing all but one
payments field, and all but one date field? Then making the projectID field a
one to many relationship with the projectID field on [GrantInfoTbl]?


I have another question also.

Like I mentioned in my original post, each grant has many projects
associated with it. So, with my current design, will I be able to enter in
multiple projects under each grant without having to re-enter all the grant
information each time? Or do I need a separate table for project information
and a separate table for grant information?
--

The "many" would be records in a [Payments] Table, in which each record
would contain, for example, a [Date] field, [Amount] field, etc., and a
[ProjectID] link (called a "foreign key") to your [PaymentsTbl] Table.

Incidentally (this is a stylistic comment), even though you can give a
key any valid field name, I think calling the primary key in
[AccomplishmentsTbl] the same thing you call [PaymentsTbl]'s primary key
is confusing. That way, any time you refer to it, you must include the
name of the Table as well as the field. And I think it doesn't make it
very easy to remember what that key means. (End of soapbox.)

The "one" would be the (unique) record in [PaymentsTbl], which you
identify via [PaymentsTbl].[ProjectID], to which all of the payments apply.

Even if you have only two such payments, keeping them in a separate
Table would allow you to simplify maintenance. For example, if you
needed to change your [PaymentsTbl].[Payment2] field in some way, such
as the way it's formatted, right now you'd also need to change
[PaymentsTbl].[Payment3] and others. If you had just one such field,
for example [PaymentTransactions].[Payment], you'd need to change it
only once and all affected fields would be corrected at the same time.

Even if you don't plan to make any changes, having fewer fields avoids
wasting space, and there are fewer fields to document. For example, I
almost always include an explanation, in the Table Design View's
"Description" field, of what each field in a Table is supposed to
contain. Access doesn't care and would work just as well if I left it
blank, but it helps me to remember what is supposed to be stored there.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Rick B wrote:
One thing I noticed is your payments table. You have it set up to store 5
payments. I would think this would be a one-to-many relationship where you
could create one or more records for the payments. What if you get the
entire money in one payment? What if you get it in 10?

-- Rick B


Hello again,

The boss has me working on another database. I guess they liked the first
one I created. Anyways, I'm pretty sure this one is more complicated than
the
last.

I'm creating a database to track grant monies. Specifically, the amount of
money, the projects associated with each grant, what was achieved in the
project, and the payments made. Then in the end I need to able to pull
reports from the data showing final figures for the work accomplished, the
total project cost, and any remaining funds.

There can be several projects associated with an account number. Each
grant
is associated with an account number. So, for each grant, there can be
multiple projects.

Each project is awarded a dollar amount of the grant, which is then paid
in
installments with matching amounts coming from the recipient, ending in a
total project dollar amount.

Below I have listed out what I've created so far.

AccomplishmentsTbl
ProjectID <-- primary key
Acres
DS
Thin
FuelBreaks
Slash
Prun
RxBurn
Plans&Assessment
I&E

GrantInfoTbl
ProjectID <-- primary key
Account# <-- primary key
Project# <-- primary key
FFY
DistrictPriority
District
CongDist
GrantName
Request
Awarded
DateAgreementSigned
POReceived
Practice
Extensions
Notes
WestGrantApp#
BLMGrants
WSFMGrantAmount
GrantAdmin
AmountforIndirect
AdminAdded
AmountofAdminforImp
AmountforIndirect(%foradmin)

PaymentsTbl
ProjectID <-- primary key
Payment1
Payment1Date
Payment2
Payment2Date
Payment3
Payment3Date
Payment4
Payment4Date
Payment5
Payment5Date
Match
TotalProject
AdditionalFundsRequested
Balance

RecipientsTbl
ProjectID <-- primary key
Name
StreetAddress
City
State
Zipcode
County
Community
Phonenumber
Fax
E-mail

This is the current setup. I'm thinking I might need to have a table for
grants and a table for projects? Not sure though. Also, all the projectID
fields are linked to each other in a one-to-one relationship with the
projectID field in the GrantInfoTbl. Is this correct?

So, I guess I'd like to know if this looks correct. I know some of the
fields near the bottom of the GrantInfoTbl won't have data in them for
some
of the grants and projects. Should I put those miscellaneous fields into
their own table? Also, some of those fields listed are calculations,
should I
remove them completely?

Basically I'm confused on how many tables I should be using, assigning
proper primary keys, and relationships with this database. If someone
could
help me out it would be greatly appreciated. Thanks in advance for any and
all advice.

 

[John Vinson]

All of those projectID fields are autonumber types. Is this right, or should
only one of them be autonumber and rest number type?

NONE of them should be Autonumber, except the Primary Key of the
Project table. The rest should be Number... Long Integer.

John W. Vinson[MVP]

 

[Vincent Johns]

NONE of them should be Autonumber, except the Primary Key of the
Project table. The rest should be Number... Long Integer.

John W. Vinson[MVP]

Having looked at your Table structure a bit more carefully, I agree.
Most of those keys that you called "primary key" were only "foreign
keys". You use a primary key to uniquely identify a record, and
Autonumber type is usually a good choice. Indeed, I often make my
Autonumber fields random, to avoid the appearance that they have some
meaning beyond that of identifying records. You use a foreign key to
refer to a record in some (usually other) Table, for example by making
it a copy of that other record's primary key, and it doesn't have to be
unique. I usually give a foreign key the same name that the
corresponding primary key has; they are distinguished by the context.

If you define a foreign key to be of Autonumber type, you'll pretty
quickly see that it doesn't work, as when you try to set it to match the
value of a matching record's primary key, Access will correctly tell you
that you can't change it.

Ok, so you're saying I can simplify my [paymentsTbl] by removing all but one
payments field, and all but one date field? Then making the projectID field a
one to many relationship with the projectID field on [GrantInfoTbl]?

Yes, and if you need to distinguish between [Payment1] and [Payment3]
you might also include a field to tell which one (1 or 3, for example)
it is.

You could link them all to a record in some other Table, possibly
[GrantInfoTbl], via a key value specified by the primary key in that
Table. But I'd call the primary key something else -- for example, I'd
call [GrantInfoTbl]'s primary key [GrantInfoTblID], in which case that
would become the reference to a [GrantInfoTbl] record.

Since I don't know much about your fields besides their names, I have to
guess about what they contain, so my advice is likely to be erroneous.
(I can figure out some, such as [RecipientsTbl].[StreetAddress], but not
[AccomplishmentsTbl].[Slash]. Descriptions would help. Of course, in
many cases, I don't even need to know that the fields exist, as they
don't affect the relationships of your Tables.)

I have another question also.

Like I mentioned in my original post, each grant has many projects
associated with it.

One of the first things I would have done, besides setting up a
[GrantInfoTbl] Table to describe a grant, would be to define a
[Projects] Table, in which each record would describe a project. You
didn't mention that you have a Table like that, though you did suggest
that you were considering including one.

So, with my current design, will I be able to enter in
multiple projects under each grant without having to re-enter all the grant
information each time? Or do I need a separate table for project information
and a separate table for grant information?

Yes, and since you have "multiple projects under each grant", this calls
for including in your [Projects] Table a [GrantInfoTblID] foreign key
whose value is copied from the primary key in [GrantInfoTbl], which I
would call [GrantInfoTbl].[GrantInfoTblID] and would declare as
Autonumber type (random values), though any unique values will do.

[...]

If there's just one such amount per grant, it can just be a field in
[GrantInfoTbl]. If more than one, they belong in a separate Table with
links to the relevant record in [GrantInfoTbl].

These could be represented as records in [Projects], linked to
[GrantInfoTbl].

This could be a memo field in [Projects], containing paragraphs
describing the achievements; or a link to another Table containing a
list of achievements, one per record; or a link to a Table with just one
record per project (1:1 link; is this what [AccomplishmentsTbl] is
supposed to do?); or a set of fields within [Projects] describing the
achievements.

I assume that [PaymentsTbl] contains these. Are these grants or
disbursements? Anyway, if a payment is associated with a project, link
each record to [Projects]; if with a grant, then link to [GrantInfoTbl];
if with an account, then link to [Accounts], etc.

This will be a lot easier if you actually record these figures somewhere
in your Tables. My guess is that they'd be in a Table with links to
[AccomplishmentsTbl] or a subset thereof. Subsets might include work
done by an individual or company under a specific goal number in
[AccomplishmentsTbl]. Your level of detail in this would be driven by
the detail required in the reports you must render.

available IF you have tracked individual costs...

But you didn't say what an "account number" is associated with. You
referred to it in [GrantInfoTbl] (you called it part of the primary key
there), but I didn't see any [Accounts] Table. If one account number is
attached to only one grant, I suggest keeping its field, for reporting
purposes, but using an Autonumber field, [GrantInfoTblID], for reference
purposes (using the [GrantInfoTblID] value as a foreign key in other
Tables that refer to this record).

You didn't say if each account number is associated with only one grant,
as opposed to being shared by multiple grants.

These could be represented by records in [Projects] linked to the
[GrantInfoTbl] Table.

These installments look like candidats for a [Receipts] Table, with each
record documenting one such payment (date received, amount, project,
source, etc.).

Total amount you'd calculate by adding the installment amounts; you
might not need to store it, unless for error-checking purposes or to
determine how much is still owed.

As I mentioned, I find it convenient to make the primary key for each
Table be an Autonumber field (random values) with a name formed by
appending "ID" to the Table's name, such as [AccomplishmentsTblID].

This could use a foreign key [GrantInfoTblID] or [ProjectsID] to
identify the grant or project to which the accomplishment belongs.

Plural number on this name (instead of "BLMGrant") makes me suspicious
that there could be more than one of these. If so, set up another
Table, [BLMGrants], in which each record links to this record via this
record's [GrantInfoTblID] value. (Same comment applies to any other
plural-number field names.)

The 4 fields at the end probably do not apply to individual payments, if
so, they should be moved to the parent Table. [Balance], if I guess
correctly as to its nature, should perhaps not be in a Table at all, but
rather calculated from other stored information.

You didn't say what this Table does. If it characterizes persons to
whom disbursements are made, then the records describing disbursements
should contain a link to this Table.

Whether it's "correct" depends on what you're trying to do. But I don't
understand the purpose of a 1:1 link, as that's logically equivalent to
just stuffing those fields into one combined Table. (It can make sense
in terms of grouping fields that have related meanings and distribution.)

Perhaps, but not for that reason. For now, I'd set up Tables based on
how their information is related. Later, I might split up Tables based
on their contents, but not for a while.

I certainly would. Calculated values in a Table usually don't help much
(unless, for example, you're using them because the calculations take
too long), and they can cause headaches if you misjudge when they have
to be updated.

I think the number of Tables is far less important than their functional
relationships. What do you intend each record to represent? In some
cases, a human being; in others, a payment; in others, a project.

That's pretty easy. If you're not sure, just give every Table a primary
key (as I described above). Access will do this automagically for you,
but I like to modify Access's suggestion by changing the type to random
and including the Table's name in the field's name.

This part is trickier. You set a relationship wherever the records need
to be linked, such as a disbursement with a payee and with a project.
The link takes the form of a field whose value (and, I suggest, whose
name) duplicates that of the associated record in the other Table.

-- Vincent Johns

 

[justin]

Ok, so after reading everyones advice/suggestions, I've changed things up a
bit, but I'm still having some troubles.

Thanks for the help by the way, it helps; a lot!

Anyway, here is how I have my tables set up currently.

[AccomplishmentsTbl]
ProjectID <-- Foreign key number field linked to
[ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship
Acres
DS
Thin
FuelBreaks
Slash
Prun
RxBurn
Plans&Assessment
I&E

All of those fields are number fields except the bottom two. users enter in
the amount of each, if any was completed for that project.

[CommentsTbl]
ID <-- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a
one to many relationship. the many being [CommentsTbl]
Comments <-- another Foreign Key
Date
Name

This table is here to leave comments about the project.

[GrantInfoTbl]
GrantID <-- Primary Key autonumber field linked to
[ProjectInfoTbl].[GrantID] in an indeterminate relationship (not sure what
that means exactly, or how to change it)
ProjectID <-- Foreign Key (Not sure if I need this here or not?)
Account#
Project#
FFY
CongDist
GrantName
Request
Awarded
DateAgreementSigned
POReceived <-- (might get moved to [ProjectInfoTbl] not sure on that yet)
Notes
WestGrantApp#
BLMGrants
WSFMGrantAmount
GrantAdmin
AmountforIndirect
AdminAdded
AmountofAdminforImp
AmountforIndirect(%forAdmin)

Some of these fields at the end are calculations (i think), so I'll probably
end up deleting them.

[PaymentsTbl]
ProjectID <-- Foreign Key number field linked to
[ProjectInfoTbl].[ProjectID] in a one to many relationship. The many side
being [PaymentsTbl].
Payment <-- Foreign Key
PaymentDate
Match
AdditionalFundsRequested

This table tracks the payments we make to a project, as the project
progresses, and the match from the recipient. This will be used to calculate
totals for the reports to be made.

[ProjectInfoTbl]
GrantID <-- Foreign Key number field linked to [GrantInfoTbl].[GrantID] in
an indeterminate relationship.
ProjectID <-- Primary Key autonumber field linked as noted above.
Project# <-- Primary Key
District
DistrictPriority
ProjectName
Extensions
Request
Awarded
Practice

This table stores the info related to the individual projects.

[RecipientsTbl]
ProjectID <-- Foreign Key number field linked to
[ProjectInfoTbl].[ProjectID] in a one to one relationship
Name
StreetAddress
City
State
ZipCode
County
Community
PhoneNumber
Fax
E-Mail

This table stores the contact info. and location info. related to the
project/recipient.

So these are all the tables I have created thus far. I might be adding a
lookup table or two in the future, but that is still undecided.

I made a form, to try and capture all this for ease of data entry, but I'm
running into problems when I try to input some data to see if things work
correctly.

Briefly, I'll explain my form setup. I created a form with [ProjectInfoTbl],
then I created a tab thing, with 4 folders in it. Each folder has the
following subform in it; [RecipientsTbl], [PaymentsTbl],
[AccomplishmentsTbl], [CommentsTbl]. Then I saved this.
I then created a new form with [GrantInfoTbl]. Within this form I inserted
my [ProjectInfoTbl form] as a subform. Then Saved it. So now i have the one
form with all the tables in it.

Now, the problems.
When I start entering data into the fields in [GrantInfoTbl] things seem to
work fine. And, if I click into the subform to enter data about the project,
things also seem fine. Now if I click on the first Tab page to put recipient
info in, I get Error 3201; You cannot add or change a record because a
related record is required in table 'AccomplishmentsTbl'

and if I try to click back to something in the outer most form the
[GrantsInfoTbl] part, I get the same error message.

Can someone please shed some light onto this problem? Thanks.

I know I had another problem, but I can't remember what it was right now. It
will come up soon I imagine.

Sorry this post is so long, but I tried to paint a good picture of what my
database currently looks like.

Again, thanks for any help in advance.

-Justin
--
I''ve never used Access before. HELP!

NONE of them should be Autonumber, except the Primary Key of the
Project table. The rest should be Number... Long Integer.

John W. Vinson[MVP]

Having looked at your Table structure a bit more carefully, I agree.
Most of those keys that you called "primary key" were only "foreign
keys". You use a primary key to uniquely identify a record, and
Autonumber type is usually a good choice. Indeed, I often make my
Autonumber fields random, to avoid the appearance that they have some
meaning beyond that of identifying records. You use a foreign key to
refer to a record in some (usually other) Table, for example by making
it a copy of that other record's primary key, and it doesn't have to be
unique. I usually give a foreign key the same name that the
corresponding primary key has; they are distinguished by the context.

If you define a foreign key to be of Autonumber type, you'll pretty
quickly see that it doesn't work, as when you try to set it to match the
value of a matching record's primary key, Access will correctly tell you
that you can't change it.

Ok, so you're saying I can simplify my [paymentsTbl] by removing all but one
payments field, and all but one date field? Then making the projectID field a
one to many relationship with the projectID field on [GrantInfoTbl]?

Yes, and if you need to distinguish between [Payment1] and [Payment3]
you might also include a field to tell which one (1 or 3, for example)
it is.

You could link them all to a record in some other Table, possibly
[GrantInfoTbl], via a key value specified by the primary key in that
Table. But I'd call the primary key something else -- for example, I'd
call [GrantInfoTbl]'s primary key [GrantInfoTblID], in which case that
would become the reference to a [GrantInfoTbl] record.

Since I don't know much about your fields besides their names, I have to
guess about what they contain, so my advice is likely to be erroneous.
(I can figure out some, such as [RecipientsTbl].[StreetAddress], but not
[AccomplishmentsTbl].[Slash]. Descriptions would help. Of course, in
many cases, I don't even need to know that the fields exist, as they
don't affect the relationships of your Tables.)

I have another question also.

Like I mentioned in my original post, each grant has many projects
associated with it.

One of the first things I would have done, besides setting up a
[GrantInfoTbl] Table to describe a grant, would be to define a
[Projects] Table, in which each record would describe a project. You
didn't mention that you have a Table like that, though you did suggest
that you were considering including one.

So, with my current design, will I be able to enter in
multiple projects under each grant without having to re-enter all the grant
information each time? Or do I need a separate table for project information
and a separate table for grant information?

Yes, and since you have "multiple projects under each grant", this calls
for including in your [Projects] Table a [GrantInfoTblID] foreign key
whose value is copied from the primary key in [GrantInfoTbl], which I
would call [GrantInfoTbl].[GrantInfoTblID] and would declare as
Autonumber type (random values), though any unique values will do.

[...]

If there's just one such amount per grant, it can just be a field in
[GrantInfoTbl]. If more than one, they belong in a separate Table with
links to the relevant record in [GrantInfoTbl].

These could be represented as records in [Projects], linked to
[GrantInfoTbl].

This could be a memo field in [Projects], containing paragraphs
describing the achievements; or a link to another Table containing a
list of achievements, one per record; or a link to a Table with just one
record per project (1:1 link; is this what [AccomplishmentsTbl] is
supposed to do?); or a set of fields within [Projects] describing the
achievements.

I assume that [PaymentsTbl] contains these. Are these grants or
disbursements? Anyway, if a payment is associated with a project, link
each record to [Projects]; if with a grant, then link to [GrantInfoTbl];
if with an account, then link to [Accounts], etc.

This will be a lot easier if you actually record these figures somewhere
in your Tables. My guess is that they'd be in a Table with links to
[AccomplishmentsTbl] or a subset thereof. Subsets might include work
done by an individual or company under a specific goal number in
[AccomplishmentsTbl]. Your level of detail in this would be driven by
the detail required in the reports you must render.

available IF you have tracked individual costs...

But you didn't say what an "account number" is associated with. You
referred to it in [GrantInfoTbl] (you called it part of the primary key
there), but I didn't see any [Accounts] Table. If one account number is
attached to only one grant, I suggest keeping its field, for reporting
purposes, but using an Autonumber field, [GrantInfoTblID], for reference
purposes (using the [GrantInfoTblID] value as a foreign key in other
Tables that refer to this record).

You didn't say if each account number is associated with only one grant,
as opposed to being shared by multiple grants.

These could be represented by records in [Projects] linked to the
[GrantInfoTbl] Table.

These installments look like candidats for a [Receipts] Table, with each
record documenting one such payment (date received, amount, project,
source, etc.).

Total amount you'd calculate by adding the installment amounts; you
might not need to store it, unless for error-checking purposes or to
determine how much is still owed.

As I mentioned, I find it convenient to make the primary key for each
Table be an Autonumber field (random values) with a name formed by
appending "ID" to the Table's name, such as [AccomplishmentsTblID].

This could use a foreign key [GrantInfoTblID] or [ProjectsID] to
identify the grant or project to which the accomplishment belongs.

Plural number on this name (instead of "BLMGrant") makes me suspicious
that there could be more than one of these. If so, set up another
Table, [BLMGrants], in which each record links to this record via this
record's [GrantInfoTblID] value. (Same comment applies to any other
plural-number field names.)

The 4 fields at the end probably do not apply to individual payments, if
so, they should be moved to the parent Table. [Balance], if I guess
correctly as to its nature, should perhaps not be in a Table at all, but
rather calculated from other stored information.

You didn't say what this Table does. If it characterizes persons to
whom disbursements are made, then the records describing disbursements
should contain a link to this Table.

Whether it's "correct" depends on what you're trying to do. But I don't
understand the purpose of a 1:1 link, as that's logically equivalent to
just stuffing those fields into one combined Table. (It can make sense
in terms of grouping fields that have related meanings and distribution.)

Perhaps, but not for that reason. For now, I'd set up Tables based on
how their information is related. Later, I might split up Tables based
on their contents, but not for a while.

I certainly would. Calculated values in a Table usually don't help much
(unless, for example, you're using them because the calculations take
too long), and they can cause headaches if you misjudge when they have
to be updated.

I think the number of Tables is far less important than their functional

 

[John Vinson]

Ok, so after reading everyones advice/suggestions, I've changed things up a
bit, but I'm still having some troubles.

Thanks for the help by the way, it helps; a lot!

Anyway, here is how I have my tables set up currently.

[AccomplishmentsTbl]
ProjectID <-- Foreign key number field linked to
[ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship

So each Project can have exactly zero or one accomplishments? Never a
second one?

One to one relationships are VERY rare. Generally if it's truly a one
to one, you can simply include the fields into the parent table.

Acres
DS
Thin
FuelBreaks
Slash
Prun
RxBurn
Plans&Assessment
I&E

And this looks like you're storing data in fieldnames.

All of those fields are number fields except the bottom two. users enter in
the amount of each, if any was completed for that project.

A better design might be a many to many relationship. You'ld have a
small table of Activities:

Activities
ActivityID Autonumber Primary Key
Activity Text <e.g. "Thin", "RxBurn"

and a resolver table ProjectAccomplishments:

ProjectAccomplishments
ProjectID Long Integer <link to Projects>
ActivityID Long Integer <link to Activities>

[CommentsTbl]
ID <-- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a
one to many relationship. the many being [CommentsTbl]
Comments <-- another Foreign Key

Foreign key to... what? Shouldn't this just be a Text (or Memo if the
comments will run over 255 bytes) field containing the text of the
comment?

Date
Name

Rename these fields: both Date and Name are reserved words.

This table is here to leave comments about the project.

[GrantInfoTbl]
GrantID <-- Primary Key autonumber field linked to
[ProjectInfoTbl].[GrantID] in an indeterminate relationship (not sure what
that means exactly, or how to change it)

You cannot ***EVER*** use an Autonumber as a foreign key. Primary key,
yes - but it *SIMPLY WILL NOT WORK* as a foreign key, or the field
that you link *to*. An Autonumber is uncontrollable and arbitrary.

How are Grants related to Projects, in the real world? Will one Grant
cover multiple projects, or will each Project use multiple grants, or
both, or neither?

ProjectID <-- Foreign Key (Not sure if I need this here or not?)
Account#
Project#
FFY
CongDist
GrantName
Request
Awarded
DateAgreementSigned
POReceived <-- (might get moved to [ProjectInfoTbl] not sure on that yet)
Notes
WestGrantApp#
BLMGrants
WSFMGrantAmount
GrantAdmin
AmountforIndirect
AdminAdded
AmountofAdminforImp
AmountforIndirect(%forAdmin)

Some of these fields at the end are calculations (i think), so I'll probably
end up deleting them.
Yep.

[PaymentsTbl]
ProjectID <-- Foreign Key number field linked to
[ProjectInfoTbl].[ProjectID] in a one to many relationship. The many side
being [PaymentsTbl].
Payment <-- Foreign Key

Again... NO. The payment is a data value, NOT a linking field and not
a foreign key.

Just to be clear: a foreign key is a field whose function is to
provide a link to a Primary Key value in some other table. If the
Primary Key in the "one" side table is Text, the foreign key must be
Text; if it's Autonumber, it should be Long Integer. You're not
storing *data* per se in foreign key fields, you're storing pointers
to other tables.

PaymentDate
Match
AdditionalFundsRequested

This table tracks the payments we make to a project, as the project
progresses, and the match from the recipient. This will be used to calculate
totals for the reports to be made.

[ProjectInfoTbl]
GrantID <-- Foreign Key number field linked to [GrantInfoTbl].[GrantID] in
an indeterminate relationship.
ProjectID <-- Primary Key autonumber field linked as noted above.
Project# <-- Primary Key

Stop.

A table can have

ONLY ONE PRIMARY KEY.

If the Project# is stable (won't be edited often or ever) and can be
counted on to be unique, use *it* as the Primary Key and as the
foreign key in every table which needs a link; using an autonumber is
necessary only if you don't have a "natural" key. I'm guessing that
each Project will have a unique Project# - so that would be your
natural key.

District
DistrictPriority
ProjectName
Extensions
Request
Awarded
Practice

This table stores the info related to the individual projects.

[RecipientsTbl]
ProjectID <-- Foreign Key number field linked to
[ProjectInfoTbl].[ProjectID] in a one to one relationship\

Again... you want each Project to have one, and only one, never any
more, contacts? I doubt it! I think you want a one to many
relationship.

Name
StreetAddress
City
State
ZipCode
County
Community
PhoneNumber
Fax
E-Mail

This table stores the contact info. and location info. related to the
project/recipient.

So these are all the tables I have created thus far. I might be adding a
lookup table or two in the future, but that is still undecided.

You'll probably want tables of Cities, States, Counties (perhaps all
in one Zip table), and doubtless more.

I made a form, to try and capture all this for ease of data entry, but I'm
running into problems when I try to input some data to see if things work
correctly.

Yep. You're jumping the gun. Let's get these incorrect relationships
fixed first.

Briefly, I'll explain my form setup. I created a form with [ProjectInfoTbl],
then I created a tab thing, with 4 folders in it. Each folder has the
following subform in it; [RecipientsTbl], [PaymentsTbl],
[AccomplishmentsTbl], [CommentsTbl]. Then I saved this.
I then created a new form with [GrantInfoTbl]. Within this form I inserted
my [ProjectInfoTbl form] as a subform. Then Saved it. So now i have the one
form with all the tables in it.

Now, the problems.
When I start entering data into the fields in [GrantInfoTbl] things seem to
work fine. And, if I click into the subform to enter data about the project,
things also seem fine. Now if I click on the first Tab page to put recipient
info in, I get Error 3201; You cannot add or change a record because a
related record is required in table 'AccomplishmentsTbl'

Right. Because you have a one to one relationship (an incorrect one
I'm guessing) and haven't filled in a record in AccomplishmentsTbl.

and if I try to click back to something in the outer most form the
[GrantsInfoTbl] part, I get the same error message.

Can someone please shed some light onto this problem? Thanks.

I think the first thing to do is to get clear in your mind how
relationships work. THEN start thinking about the form!

John W. Vinson[MVP]

 

[Vincent Johns]

Ok, so after reading everyones advice/suggestions, I've changed things up a
bit, but I'm still having some troubles.

Thanks for the help by the way, it helps; a lot!

Anyway, here is how I have my tables set up currently.

[AccomplishmentsTbl]
ProjectID <-- Foreign key number field linked to
[ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship

So each Project can have exactly zero or one accomplishments? Never a
second one?

One to one relationships are VERY rare. Generally if it's truly a one
to one, you can simply include the fields into the parent table.

Acres
DS
Thin
FuelBreaks
Slash
Prun
RxBurn
Plans&Assessment
I&E

And this looks like you're storing data in fieldnames.

.... which may be OK if the fields are Boolean (yes/no) type, but you
said they were number fields.

All of those fields are number fields except the bottom two. users enter in
the amount of each, if any was completed for that project.

[...]

Date
Name

Rename these fields: both Date and Name are reserved words.

Although I've never run into problems with Access over using these names
for fields (but there might be valid technical reasons to avoid them),
another reason to rename them is that they are not very informative.
Name of what? Date when what happened? Making the names suggestive of
what data the fields contain will help you avoid putting the wrong
information in there, or trying to link dissimilar items together.

[...]

Just to be clear: a foreign key is a field whose function is to
provide a link to a Primary Key value in some other table. If the
Primary Key in the "one" side table is Text, the foreign key must be
Text; if it's Autonumber, it should be Long Integer. You're not
storing *data* per se in foreign key fields, you're storing pointers
to other tables.

Sometimes people do store data in foreign key fields (things like part
numbers, student ID numbers, etc.), on the basis that it saves a Table
reference and allows you to avoid maintaining an extra field in the
referenced Table, but I think it's a confusing practice, and I very
rarely do it myself. Access makes it easy to set up keys in which the
ONLY purpose of the foreign key is to point to the other Table, and I
think you'll avoid lots of headaches if you use it only for that purpose.

[...] I'm guessing that
each Project will have a unique Project# - so that would be your
natural key.

.... and this would be the kind of situation in which I, too, would have
the primary key perform two functions (identify a record, and identify a
Project#). But try not to overdo it. Autonumber fields don't cost much
(only 4 bytes per record).

I think the first thing to do is to get clear in your mind how
relationships work. THEN start thinking about the form!

John W. Vinson[MVP]

Before working on the Form, you can do a lot with Queries, even though
the layout won't be as pretty as with a Form. Define Queries to display
the kinds of information you want to get out of the database. If you
can't get the Queries to work, maybe there's a problem with Table
design. Actually, working with the Queries may guide you toward making
improvements in your Tables (such as getting rid of redundant information).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[justin]

Well thanks go out to both J.V and V.J, and R.B for that matter. I have spent
all day reading up on relationships and normalization, along with these forum
posts. Thanks for the info. Now I need to spend some time processing all of
this.

I will have more questions, so stay tuned. thanks again!

-Justin

 

[justin]

[AccomplishmentsTbl]
ProjectID <-- Foreign key number field linked to
[ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship

So each Project can have exactly zero or one accomplishments? Never a
second one?

No, Projects can have various accomplishments, depending on the project.
This table is intended to record how many of each were accomplished during
the project.

One to one relationships are VERY rare. Generally if it's truly a one
to one, you can simply include the fields into the parent table.

And this looks like you're storing data in fieldnames.

Not exactly, those are the field names, and the data that is to be stored is
quantities of each. For example, a project was a total of 10 acres, and there
were 5 DS achieved, and 5 thin achieved.


[...]

A better design might be a many to many relationship. You'ld have a
small table of Activities:

Activities
ActivityID Autonumber Primary Key
Activity Text <e.g. "Thin", "RxBurn"

and a resolver table ProjectAccomplishments:

ProjectAccomplishments
ProjectID Long Integer <link to Projects>
ActivityID Long Integer <link to Activities>
Amount Number <where you fill in the amount>

Is something like this still necessary?

[CommentsTbl]
ID <-- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a
one to many relationship. the many being [CommentsTbl]
Comments <-- another Foreign Key

Foreign key to... what? Shouldn't this just be a Text (or Memo if the
comments will run over 255 bytes) field containing the text of the
comment?

Yes, it probably should be.

Date
Name

Rename these fields: both Date and Name are reserved words.

OK

[GrantInfoTbl]
GrantID <-- Primary Key autonumber field linked to
[ProjectInfoTbl].[GrantID] in an indeterminate relationship (not sure what
that means exactly, or how to change it)

You cannot ***EVER*** use an Autonumber as a foreign key. Primary key,
yes - but it *SIMPLY WILL NOT WORK* as a foreign key, or the field
that you link *to*. An Autonumber is uncontrollable and arbitrary.

How are Grants related to Projects, in the real world? Will one Grant
cover multiple projects, or will each Project use multiple grants, or
both, or neither?

As far as I know, one grant can have multiple projects associated with it,
or just one, or none (the grant itself is the project).

[...]

[ProjectInfoTbl]
GrantID <-- Foreign Key number field linked to [GrantInfoTbl].[GrantID] in
an indeterminate relationship.
ProjectID <-- Primary Key autonumber field linked as noted above.
Project# <-- Primary Key

Stop. Hammer Time.

A table can have

ONLY ONE PRIMARY KEY.

If the Project# is stable (won't be edited often or ever) and can be
counted on to be unique, use *it* as the Primary Key and as the
foreign key in every table which needs a link; using an autonumber is
necessary only if you don't have a "natural" key. I'm guessing that
each Project will have a unique Project# - so that would be your
natural key.

I think I'll need an autonumber, because sometimes projects get cancelled,
or merged with other projects, so I can't rely on the project# field.

District
DistrictPriority
ProjectName
Extensions
Request
Awarded
Practice

This table stores the info related to the individual projects.

[RecipientsTbl]
ProjectID <-- Foreign Key number field linked to
[ProjectInfoTbl].[ProjectID] in a one to one relationship\

Again... you want each Project to have one, and only one, never any
more, contacts? I doubt it! I think you want a one to many
relationship.

I guess projects can have more than one contact. It's not usually the case,
but I've seen some with multiple addresses or names. Basically the recipient
is the project. Meaning the recipient can either be an individual person
recieving assistance on their land, or the recipient is a Home Owner's
Association for example. This information is typically not only the contact
persons info, but also the location of the project.

Name
StreetAddress
City
State
ZipCode
County
Community
PhoneNumber
Fax
E-Mail

[...]

You'll probably want tables of Cities, States, Counties (perhaps all
in one Zip table), and doubtless more.

I don't know if that is really going to be necessary.


[...]

I think the first thing to do is to get clear in your mind how
relationships work. THEN start thinking about the form!

John W. Vinson[MVP]

I agree, I'm going to go back into the db now and start working on these
corrections. I'll post again once I move things around a bit, and reach
another road block.

Thanks again for the help.

-Justin

 

[John Vinson]

[AccomplishmentsTbl]
ProjectID <-- Foreign key number field linked to
[ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship

So each Project can have exactly zero or one accomplishments? Never a
second one?

No, Projects can have various accomplishments, depending on the project.
This table is intended to record how many of each were accomplished during
the project.

But a one to one relationship, BY DEFINITION, means that you can have
either zero or one record in the AccomplishmentsTbl for any record in
Projects. See below...

Not exactly, those are the field names, and the data that is to be stored is
quantities of each. For example, a project was a total of 10 acres, and there
were 5 DS achieved, and 5 thin achieved.

And if, six months down the road, the big boss says that you need to
include "Public education", "Powerline Protection", and three other
things as accomplishments, what do you do? Redesign your table, all
your queries, all your forms, all your reports?

These values ARE DATA. They are not attributes of an accomplishment
entity! An Accomplishment has two attributes (fields), in my opinion:
what type of accomplishment it was (say creating a FuelBreak), and how
many of them there were.

"Fields are expensive, records are cheap". You'll have much better
luck storing one count of accomplishments per record, rather than one
count per field in a wide-flat table.

[...]

A better design might be a many to many relationship. You'ld have a
small table of Activities:

Activities
ActivityID Autonumber Primary Key
Activity Text <e.g. "Thin", "RxBurn"

and a resolver table ProjectAccomplishments:

ProjectAccomplishments
ProjectID Long Integer <link to Projects>
ActivityID Long Integer <link to Activities>
Amount Number <where you fill in the amount>

Is something like this still necessary?

I'd say... yes, it is. See above.

[GrantInfoTbl]
GrantID <-- Primary Key autonumber field linked to
[ProjectInfoTbl].[GrantID] in an indeterminate relationship (not sure what
that means exactly, or how to change it)

You cannot ***EVER*** use an Autonumber as a foreign key. Primary key,
yes - but it *SIMPLY WILL NOT WORK* as a foreign key, or the field
that you link *to*. An Autonumber is uncontrollable and arbitrary.

How are Grants related to Projects, in the real world? Will one Grant
cover multiple projects, or will each Project use multiple grants, or
both, or neither?

As far as I know, one grant can have multiple projects associated with it,
or just one, or none (the grant itself is the project).

OK, then you should have a GrantID primary key in the GrantInfoTbl,
and a GrantID foreign key in that grant's associated Projects. There
should not be (and cannot be) a ProjectID field in the Grants table
since the field can only have one value, and you need multiple ones.
If GrantInfoTbl.GrantID is an Autonumber, use a Long Integer GrantID
in the projects table.

[ProjectInfoTbl]
GrantID <-- Foreign Key number field linked to [GrantInfoTbl].[GrantID] in
an indeterminate relationship.
ProjectID <-- Primary Key autonumber field linked as noted above.
Project# <-- Primary Key

Stop. Hammer Time.

A table can have

ONLY ONE PRIMARY KEY.

If the Project# is stable (won't be edited often or ever) and can be
counted on to be unique, use *it* as the Primary Key and as the
foreign key in every table which needs a link; using an autonumber is
necessary only if you don't have a "natural" key. I'm guessing that
each Project will have a unique Project# - so that would be your
natural key.

I think I'll need an autonumber, because sometimes projects get cancelled,
or merged with other projects, so I can't rely on the project# field.

ok... make the autonumber the primary key then, and put a unique
Index on Project# to prevent having two records with the same value.

District
DistrictPriority
ProjectName
Extensions
Request
Awarded
Practice

This table stores the info related to the individual projects.

[RecipientsTbl]
ProjectID <-- Foreign Key number field linked to
[ProjectInfoTbl].[ProjectID] in a one to one relationship\

Again... you want each Project to have one, and only one, never any
more, contacts? I doubt it! I think you want a one to many
relationship.

I guess projects can have more than one contact. It's not usually the case,
but I've seen some with multiple addresses or names. Basically the recipient
is the project. Meaning the recipient can either be an individual person
recieving assistance on their land, or the recipient is a Home Owner's
Association for example. This information is typically not only the contact
persons info, but also the location of the project.

It only takes one multiple address to cause big headaches if you
enforce a one to one. Simply use an autonumber ContactID primary key,
and ProjectID as the foreign key.

Name
StreetAddress
City
State
ZipCode
County
Community
PhoneNumber
Fax
E-Mail

[...]

You'll probably want tables of Cities, States, Counties (perhaps all
in one Zip table), and doubtless more.

I don't know if that is really going to be necessary.

Handy... but you're right, probably not necessary. If you don't mind
your users typing "Multnomah" twenty times, and maybe "Multnoma" or
"Mulnomah" a couple of times, you can just use text fields and
textboxes. It's very easy though to just pick from a list if you have
one.

I agree, I'm going to go back into the db now and start working on these
corrections. I'll post again once I move things around a bit, and reach
another road block.

Look forward to seeing you back!

John W. Vinson[MVP]

 

[justin]

[AccomplishmentsTbl]
ProjectID <-- Foreign key number field linked to
[ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship

So each Project can have exactly zero or one accomplishments? Never a
second one?

No, Projects can have various accomplishments, depending on the project.
This table is intended to record how many of each were accomplished during
the project.

But a one to one relationship, BY DEFINITION, means that you can have
either zero or one record in the AccomplishmentsTbl for any record in
Projects. See below...

I see now. I was confused with primary keys and foreign keys. I think I've
sorted things out now. See below...

And if, six months down the road, the big boss says that you need to
include "Public education", "Powerline Protection", and three other
things as accomplishments, what do you do? Redesign your table, all
your queries, all your forms, all your reports?

Touché. Ok, I've conceded and I created the two tables like you suggested
below.

[...]

Form related question: when users are entering in the data for the project,
how do I change the view so they see the actual Text (ie, "Thin") instead of
the ActivityID?

I'd say... yes, it is. See above.

So, after doing this I no longer need my original AccomplishmentsTbl correct?

[GrantInfoTbl]
GrantID <-- Primary Key autonumber field linked to
[ProjectInfoTbl].[GrantID] in an indeterminate relationship (not sure what
that means exactly, or how to change it)

You cannot ***EVER*** use an Autonumber as a foreign key. Primary key,
yes - but it *SIMPLY WILL NOT WORK* as a foreign key, or the field
that you link *to*. An Autonumber is uncontrollable and arbitrary.

How are Grants related to Projects, in the real world? Will one Grant
cover multiple projects, or will each Project use multiple grants, or
both, or neither?

As far as I know, one grant can have multiple projects associated with it,
or just one, or none (the grant itself is the project).

OK, then you should have a GrantID primary key in the GrantInfoTbl,
and a GrantID foreign key in that grant's associated Projects. There
should not be (and cannot be) a ProjectID field in the Grants table
since the field can only have one value, and you need multiple ones.
If GrantInfoTbl.GrantID is an Autonumber, use a Long Integer GrantID
in the projects table.

Done and done.

[ProjectInfoTbl]
GrantID <-- Foreign Key number field linked to [GrantInfoTbl].[GrantID] in
an indeterminate relationship.
ProjectID <-- Primary Key autonumber field linked as noted above.
Project# <-- Primary Key

Stop.

A table can have

ONLY ONE PRIMARY KEY.

If the Project# is stable (won't be edited often or ever) and can be
counted on to be unique, use *it* as the Primary Key and as the
foreign key in every table which needs a link; using an autonumber is
necessary only if you don't have a "natural" key. I'm guessing that
each Project will have a unique Project# - so that would be your
natural key.

I think I'll need an autonumber, because sometimes projects get cancelled,
or merged with other projects, so I can't rely on the project# field.

ok... make the autonumber the primary key then, and put a unique
Index on Project# to prevent having two records with the same value.

Also done.

[...]

[RecipientsTbl]
ProjectID <-- Foreign Key number field linked to
[ProjectInfoTbl].[ProjectID] in a one to one relationship\

Again... you want each Project to have one, and only one, never any
more, contacts? I doubt it! I think you want a one to many
relationship.

I guess projects can have more than one contact. It's not usually the case,
but I've seen some with multiple addresses or names. Basically the recipient
is the project. Meaning the recipient can either be an individual person
recieving assistance on their land, or the recipient is a Home Owner's
Association for example. This information is typically not only the contact
persons info, but also the location of the project.

It only takes one multiple address to cause big headaches if you
enforce a one to one. Simply use an autonumber ContactID primary key,
and ProjectID as the foreign key.

I agree, and I did.

Name
StreetAddress
City
State
ZipCode
County
Community
PhoneNumber
Fax
E-Mail

[...]

You'll probably want tables of Cities, States, Counties (perhaps all
in one Zip table), and doubtless more.

I don't know if that is really going to be necessary.

Handy... but you're right, probably not necessary. If you don't mind
your users typing "Multnomah" twenty times, and maybe "Multnoma" or
"Mulnomah" a couple of times, you can just use text fields and
textboxes. It's very easy though to just pick from a list if you have
one.

Yea, that is definitely a nice feature to have, but since we are really only
dealing with the state of Colorado, I don't think I'm asking too much of my
fellow employees.

Well, now that I've made these adjustments things seem to be working ok. I
made a couple of test forms, entered some data, and everything looks like
it's working the way I had visioned it.

Although, I tried making just one big form with all the relavant tables
included (just to see if things were working) and it wasn't working properly.
In design view all the various fields were there, but when switching back to
form view it just became a blank form. I assume this is because the way the
relationships are set up doesn't allow for all the fields to be on one form?
Maybe?

When I pick 2 tables to make a form out of, everything works fine.

Thanks again for all the help. I think, as long as my tables are now
normalized, I can start working on the forms and reports. At least I hope I
can.

-Justin

 

[justin]

[AccomplishmentsTbl]
ProjectID <-- Foreign key number field linked to
[ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship

[...]

Acres
DS
Thin
FuelBreaks
Slash
Prun
RxBurn
Plans&Assessment
I&E

[...]

And if, six months down the road, the big boss says that you need to
include "Public education", "Powerline Protection", and three other
things as accomplishments, what do you do? Redesign your table, all
your queries, all your forms, all your reports?

These values ARE DATA. They are not attributes of an accomplishment
entity! An Accomplishment has two attributes (fields), in my opinion:
what type of accomplishment it was (say creating a FuelBreak), and how
many of them there were.

"Fields are expensive, records are cheap". You'll have much better
luck storing one count of accomplishments per record, rather than one
count per field in a wide-flat table.

[...]

A better design might be a many to many relationship. You'ld have a
small table of Activities:

Activities
ActivityID Autonumber Primary Key
Activity Text <e.g. "Thin", "RxBurn"

and a resolver table ProjectAccomplishments:

ProjectAccomplishments
ProjectID Long Integer <link to Projects>
ActivityID Long Integer <link to Activities>
Amount Number <where you fill in the amount>

I forgot to ask in my other post.
Those last to fields I have in my AccomplishmentsTbl, Plans&Assessment and
I&E are not number fields, but rather text fields where the user inputs what
it was that was done. So, how can I set up these two new tables to include
those fields as text fields? Or is it not possible? Will I need to set up
another table for those two fields and link it back to the Activities Table?

 

[John Vinson]

On Thu, 27 Oct 2005 07:45:02 -0700, justin

Answers inline with trimming.

I see now. I was confused with primary keys and foreign keys. I think I've
sorted things out now. See below...

Good... glad to have helped you through that particular thicket!

Form related question: when users are entering in the data for the project,
how do I change the view so they see the actual Text (ie, "Thin") instead of
the ActivityID?

By putting a Combo Box on the subform which stores the ID while
displaying the text. That's the most common use of combos. See the
Orders form in the Northwind sample database - it displays the product
name while storing the product ID.

So, after doing this I no longer need my original AccomplishmentsTbl correct?

Correct. If you have data in it already post back - that information
can be salvaged and moved into the normalized table.

Yea, that is definitely a nice feature to have, but since we are really only
dealing with the state of Colorado, I don't think I'm asking too much of my
fellow employees.

Well... I'd find it much easier to tab into a combo box, type "OT" and
have the combo autofill Otero, and hit tab to go on than to type
"Otero" over and over again. Ten minutes work on your part now will
save hours of your and your colleagues' time during the life of the
project.

Well, now that I've made these adjustments things seem to be working ok. I
made a couple of test forms, entered some data, and everything looks like
it's working the way I had visioned it.

Although, I tried making just one big form with all the relavant tables
included (just to see if things were working) and it wasn't working properly.
In design view all the various fields were there, but when switching back to
form view it just became a blank form. I assume this is because the way the
relationships are set up doesn't allow for all the fields to be on one form?
Maybe?

No. A form based on a Grand Master Query That Includes Everything is
not going to be particularly useful, either for display or for data
entry. With that many tables, the query will probably not be
updateable (so you won't see the "new record", since you can't ADD a
new record); and unless every single linked table contains data, you
won't see the existing records either, since the Query will only show
you those records which have data in all the tables. You can use
"Outer Joins" to see more data (and that will probably be useful in
generating Reports), but there is no benefit to doing so on a Form.

You *CAN* see all the data (well, all the data which fits your screen)
by using a Form with Subforms. It is not necessary to try to create
the form you describe - it serves no purpose which cannot be served
more easily using other techniques.

When I pick 2 tables to make a form out of, everything works fine.

Thanks again for all the help. I think, as long as my tables are now
normalized, I can start working on the forms and reports. At least I hope I
can.

If you do need help, please feel free to post in the .forms or
..reports newsgroups. See you there, maybe! Good Luck!

John W. Vinson[MVP]

 

[John Vinson]

I forgot to ask in my other post.
Those last to fields I have in my AccomplishmentsTbl, Plans&Assessment and
I&E are not number fields, but rather text fields where the user inputs what
it was that was done. So, how can I set up these two new tables to include
those fields as text fields? Or is it not possible? Will I need to set up
another table for those two fields and link it back to the Activities Table?

If these fields apply to a Project as a whole, put them in the project
table; if they apply to each individual accomplishment (i.e. if you
have Plans&Assessment for a "fuel break"), just put the text field in
the new accomplishments table.


John W. Vinson[MVP]

 

[justin]

If these fields apply to a Project as a whole, put them in the project
table; if they apply to each individual accomplishment (i.e. if you
have Plans&Assessment for a "fuel break"), just put the text field in
the new accomplishments table.

Well, they're just another type of accomplishment, so they apply to projects
as a whole. So, I guess I will put them into the ProjectInfoTbl. But now if I
do this, can I still put the fields into the Accomplishments subform? Oh, I
also wanted to put an 'other' field in there too, incase something is done
that doesn't fit into any of these categories. I guess I will put that field
into the ProjectInfoTbl too. Thanks for the help.

-justin

 

[Vincent Johns]

Well, they're just another type of accomplishment, so they apply to projects
as a whole.

If they're another type of accomplishment, then they are NOT a quality
that describes a project, and they probably should be listed as
additional records in the [Accomplishments] Table.

So, I guess I will put them into the ProjectInfoTbl. But now if I
do this, can I still put the fields into the Accomplishments subform?

Fields that you should include in the [Accomplishments] Table should be
those which describe some aspect (such as cost, resources used, benefits
gained, who is involved, &c.) of the specific accomplishment listed in a
record in that Table.

Oh, I
also wanted to put an 'other' field in there too, incase something is done
that doesn't fit into any of these categories. I guess I will put that field
into the ProjectInfoTbl too.

If I were inclined to be a gambler, I'd bet that "other" should also be
a type of accomplishment; i.e., it should be the subject of another
record in the [Accomplishments] Table.