How can I track financial data in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am keeping a database of contributors and have the mailing lable fields,
groups, etc. set up ok, but is there a way to capture financial contributions
and record the history for each individual? Perhaps on a yearly basis? Would
you just create yearly fields? Or is there some other way to handle this
data?
 
Contributors / Financial Contributions Wace Year is a one to many
relationship. A contributor may make many contributions over time. So you
need two related table:
TblContributor
ContributorID
<name, address, etc>

TblFinancialContribution
FinancialContributionID
ContributorID
ContributionDate
ContributionAmount

To enter the data in bot tables, use a form/subform where the main form is
based on TblContributor and the subform is based on
TblFinancialContribution.
 
I am a new user so please help further - am I setting up two separate TABLES
with the same data (name, address, etc) and renaming the ID field Contributor
ID? And also doing the same with a form? I used the WIzard to create my form
from my Table fields.
 
Curious said:
I am keeping a database of contributors and have the mailing lable fields,
groups, etc. set up ok, but is there a way to capture financial contributions
and record the history for each individual? Perhaps on a yearly basis? Would
you just create yearly fields? Or is there some other way to handle this
data?
Click to expand...

One easy way would be to create a Table containing personal information,
such as name, group, and address of each contributor, and another Table
linked to it containing contribution information, such as amount of
money and date that you received the donation.

Be sure that all the types of information you wish to record are
represented in the correct tables.

For displaying the contents, define a Select Query that specifies what
date range, what donor groups, what geographical regions, etc., you want
to include. Use the Tables for recording the data; use the Queries for
manipulating them. The Queries can serve as a basis for Forms (for data
input) and Reports (for output to a printer, for example). Most things
that you might want to do with a Table, you can do with a Query, and
Queries allow you to do many things a Table can't do.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Curious said:
I am a new user so please help further - am I setting up two separate TABLES
Click to expand...

Yes, two Tables.
with the same data (name, address, etc) and renaming the ID field Contributor
ID?
Click to expand...

No, different kinds of data. If the date were the same, or quite
similar, they should all be in the same Table.

I recommend changing the default primary key name of "ID" in each table
to something unique. I usually use the name of the table with "ID" at
the end, similar to [TblContributor].[ContributorID] that PC Datasheet
suggested. My reason is that, if this field name is unique in your
database (by which I mean no other table uses the name for its own
primary key field), any other table that links to [TblContributor] can
use this same name as its foreign-key link, and it will be obvious to
you that it is a link, and obvious what table it's linking to, without
your having to look that up.
And also doing the same with a form? I used the WIzard to create my form
from my Table fields.
Click to expand...

You have a couple of choices. You'll probably want to use a Form for
[TblContributors] and a subform for the other table; look for "Subforms:
What they are and how they work" in MS Access Help.

Or you can apply a Subdatasheet linking [TblFinancialContribution] to
[TblContributors] in Table Design View. Or you can define a Query that
displays data from [TblContributors], and apply a Subdatasheet to that
which will link the contributions to it.

My suggestion is to try a couple of these to determine which is easiest
to use.
 
I concur with al Vincent says. TblContributor contains fields about each
contributor and TblFinancialContribution contains fields about each
contribution a contributor makes whether the same contributor multiple times
or a contributor with a contribution one-time. Remember that a record in
this table is about a specific contributor (ContributorID) and a specific
contribution (FinancialContributionID). Contributions by the same
contributor multiple times then will be multiple records. Hence the
one-to-many relationship.
1. Build the tables.
2. Go to the relationships screen and create the relationship.
3. Use the form wizard to create the form/subform:
a) First select TblContributor and then all its fields
b) Next select TblFinancialContributions and then all its fields
c) A dialog will come up where you tell the wizard that you want
TblContributor for the mainform and TblFinancialContribution for the
subform. Follow the instructions.
d) Follow the remaining instructions and the wizard will build the
form/subform you need.
 
Back
Top