Need advice - how should I set this up?

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.
--
I've never used Access before. HELP!

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



"justin" wrote in message
...
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.
--
I've never used Access before. HELP!

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." ?
--



"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



"justin" wrote in message
...
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.
--
I've never used Access before. HELP!

justin wrote:

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.

-- Vincent Johns
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

"justin" wrote in message ...

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


"Vincent Johns" wrote:

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

"justin" wrote in message ...

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.
--
I've never used Access before. HELP!

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!


"justin" wrote:

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


"Vincent Johns" wrote:

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

"justin" wrote in message ...

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.
--
I've never used Access before. HELP!

On Thu, 20 Oct 2005 08:56:03 -0700, justin
wrote:

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]

John Vinson wrote:

On Thu, 20 Oct 2005 08:56:03 -0700, justin
wrote:


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]

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.


justin wrote:
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.

[...]

"justin" wrote in message
...

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,

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

the projects associated with each grant,

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

what was achieved in the project,

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.

and the payments made.

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.

Then in the end I need to able to pull
reports from the data showing final figures for the work accomplished,

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.

the total project cost,

available IF you have tracked individual costs...

and any remaining funds.

There can be several projects associated with an account number.

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

Each grant
is associated with an account number.

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

So, for each grant, there can be multiple projects.

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


Each project is awarded a dollar amount of the grant, which is then paid
in
installments

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

with matching amounts coming from the recipient, ending in a
total project dollar amount.

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.


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

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


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

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


GrantInfoTbl
ProjectID -- primary key
Account# -- primary key
Project# -- primary key
FFY
DistrictPriority
District
CongDist
GrantName
Request
Awarded
DateAgreementSigned
POReceived
Practice
Extensions
Notes
WestGrantApp#
BLMGrants

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

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

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.


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

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.


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?

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


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?

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.

Also, some of those fields listed are calculations,
should I remove them completely?

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.


Basically I'm confused on how many tables I should be using,

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.

assigning proper primary keys,

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.

and relationships with this database.

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.

If someone could
help me out it would be greatly appreciated. Thanks in advance for any and
all advice.
--
I've never used Access before. HELP!

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


"Vincent Johns" wrote:

John Vinson wrote:

On Thu, 20 Oct 2005 08:56:03 -0700, justin
wrote:


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]

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.


justin wrote:
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.

[...]

"justin" wrote in message
...

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,

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

the projects associated with each grant,

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

what was achieved in the project,

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.

and the payments made.

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.

Then in the end I need to able to pull
reports from the data showing final figures for the work accomplished,

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.

the total project cost,

available IF you have tracked individual costs...

and any remaining funds.

There can be several projects associated with an account number.

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

Each grant
is associated with an account number.

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

So, for each grant, there can be multiple projects.

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


Each project is awarded a dollar amount of the grant, which is then paid
in
installments

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

with matching amounts coming from the recipient, ending in a
total project dollar amount.

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.


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

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


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

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


GrantInfoTbl
ProjectID -- primary key
Account# -- primary key
Project# -- primary key
FFY
DistrictPriority
District
CongDist
GrantName
Request
Awarded
DateAgreementSigned
POReceived
Practice
Extensions
Notes
WestGrantApp#
BLMGrants

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

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

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.


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

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.


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?

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


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?

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.

Also, some of those fields listed are calculations,
should I remove them completely?

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.


Basically I'm confused on how many tables I should be using,

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

On Mon, 24 Oct 2005 13:38:04 -0700, justin
wrote:

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
Amount Number where you fill in the amount

[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]