Suggestion for database design

I have a customers table. I will be receiving an account balance each month
for each customer. I need to be able to keep each monthly account balance
stored for a considerable period of time, lets say 5 years. What would be
the best way to store this data? It seems like keeping a column of each
month/year would lead to a very large table. Is this the only/best way to do
this? Suggestions would be very much appreciated. Thanks. Matt

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account balance each
month
for each customer. I need to be able to keep each monthly account balance
stored for a considerable period of time, lets say 5 years. What would be
the best way to store this data? It seems like keeping a column of each
month/year would lead to a very large table. Is this the only/best way to
do
this? Suggestions would be very much appreciated. Thanks. Matt

If I am required to store a quarterly balance, would you recommend that I
store that in the tblCustAcctBalances table or in a seperate table by itself.
I imagine I will need to keep these quarterly balances for a significant
amount of time also. Thanks for your suggestions. Matt

"Duane Hookom" wrote:

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account balance each
month
for each customer. I need to be able to keep each monthly account balance
stored for a considerable period of time, lets say 5 years. What would be
the best way to store this data? It seems like keeping a column of each
month/year would lead to a very large table. Is this the only/best way to
do
this? Suggestions would be very much appreciated. Thanks. Matt

If you need to store both quarterly and monthly balances, isn't one of your
monthly balances, the same as your quarterly balance? If not, I would use
the suggested table with a new text field [QuarterMonth] to store either Q
or M.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
news
If I am required to store a quarterly balance, would you recommend that I
store that in the tblCustAcctBalances table or in a seperate table by
itself.
I imagine I will need to keep these quarterly balances for a significant
amount of time also. Thanks for your suggestions. Matt

"Duane Hookom" wrote:

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account balance each
month
for each customer. I need to be able to keep each monthly account
balance
stored for a considerable period of time, lets say 5 years. What would
be
the best way to store this data? It seems like keeping a column of
each
month/year would lead to a very large table. Is this the only/best way
to
do
this? Suggestions would be very much appreciated. Thanks. Matt

The quarterly balance is not the same as one of my monthly balances. The
quarterly balance needs to be the sum of the previous 3 monthly balances from
when the query is run to update the quarterly balance value. This is why I'm
asking if I should have a separate table for the multiple quarterly balances.
There will only be four quarterly balances for each customer per year while
there will be 12 monthly balances per customer per year. Thanks for your
help. Matt

"Duane Hookom" wrote:

If you need to store both quarterly and monthly balances, isn't one of your
monthly balances, the same as your quarterly balance? If not, I would use
the suggested table with a new text field [QuarterMonth] to store either Q
or M.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
news
If I am required to store a quarterly balance, would you recommend that I
store that in the tblCustAcctBalances table or in a seperate table by
itself.
I imagine I will need to keep these quarterly balances for a significant
amount of time also. Thanks for your suggestions. Matt

"Duane Hookom" wrote:

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account balance each
month
for each customer. I need to be able to keep each monthly account
balance
stored for a considerable period of time, lets say 5 years. What would
be
the best way to store this data? It seems like keeping a column of
each
month/year would lead to a very large table. Is this the only/best way
to
do
this? Suggestions would be very much appreciated. Thanks. Matt

I would not store a quarterly balance if it can easily be queried from your
monthly balances.

--
Duane Hookom
MS Access MVP


"Matt" wrote in message
...
The quarterly balance is not the same as one of my monthly balances. The
quarterly balance needs to be the sum of the previous 3 monthly balances
from
when the query is run to update the quarterly balance value. This is why
I'm
asking if I should have a separate table for the multiple quarterly
balances.
There will only be four quarterly balances for each customer per year
while
there will be 12 monthly balances per customer per year. Thanks for your
help. Matt

"Duane Hookom" wrote:

If you need to store both quarterly and monthly balances, isn't one of
your
monthly balances, the same as your quarterly balance? If not, I would use
the suggested table with a new text field [QuarterMonth] to store either
Q
or M.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
news
If I am required to store a quarterly balance, would you recommend that
I
store that in the tblCustAcctBalances table or in a seperate table by
itself.
I imagine I will need to keep these quarterly balances for a
significant
amount of time also. Thanks for your suggestions. Matt

"Duane Hookom" wrote:

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account balance
each
month
for each customer. I need to be able to keep each monthly account
balance
stored for a considerable period of time, lets say 5 years. What
would
be
the best way to store this data? It seems like keeping a column of
each
month/year would lead to a very large table. Is this the only/best
way
to
do
this? Suggestions would be very much appreciated. Thanks. Matt

That makes sense. However, let's assume that this data will be exported and
imported into another application. If I were to store the quarterly balance,
would you recommend it stored in account balances table or a separate table?
Thanks for your input. I hope I'm not wasting your time with hypotheticals.
I'm trying to consider all situations that may arise. Thanks again.

"Duane Hookom" wrote:

I would not store a quarterly balance if it can easily be queried from your
monthly balances.

--
Duane Hookom
MS Access MVP


"Matt" wrote in message
...
The quarterly balance is not the same as one of my monthly balances. The
quarterly balance needs to be the sum of the previous 3 monthly balances
from
when the query is run to update the quarterly balance value. This is why
I'm
asking if I should have a separate table for the multiple quarterly
balances.
There will only be four quarterly balances for each customer per year
while
there will be 12 monthly balances per customer per year. Thanks for your
help. Matt

"Duane Hookom" wrote:

If you need to store both quarterly and monthly balances, isn't one of
your
monthly balances, the same as your quarterly balance? If not, I would use
the suggested table with a new text field [QuarterMonth] to store either
Q
or M.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
news If I am required to store a quarterly balance, would you recommend that
I
store that in the tblCustAcctBalances table or in a seperate table by
itself.
I imagine I will need to keep these quarterly balances for a
significant
amount of time also. Thanks for your suggestions. Matt

"Duane Hookom" wrote:

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account balance
each
month
for each customer. I need to be able to keep each monthly account
balance
stored for a considerable period of time, lets say 5 years. What
would
be
the best way to store this data? It seems like keeping a column of
each
month/year would lead to a very large table. Is this the only/best
way
to
do
this? Suggestions would be very much appreciated. Thanks. Matt

You can always create a query that calculates the quarterly balances. You
can export the records from the query.

Why would you import the quarterly balances if you can calculate them based
on the monthly balances?

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
That makes sense. However, let's assume that this data will be exported
and
imported into another application. If I were to store the quarterly
balance,
would you recommend it stored in account balances table or a separate
table?
Thanks for your input. I hope I'm not wasting your time with
hypotheticals.
I'm trying to consider all situations that may arise. Thanks again.

"Duane Hookom" wrote:

I would not store a quarterly balance if it can easily be queried from
your
monthly balances.

--
Duane Hookom
MS Access MVP


"Matt" wrote in message
...
The quarterly balance is not the same as one of my monthly balances.
The
quarterly balance needs to be the sum of the previous 3 monthly
balances
from
when the query is run to update the quarterly balance value. This is
why
I'm
asking if I should have a separate table for the multiple quarterly
balances.
There will only be four quarterly balances for each customer per year
while
there will be 12 monthly balances per customer per year. Thanks for
your
help. Matt

"Duane Hookom" wrote:

If you need to store both quarterly and monthly balances, isn't one of
your
monthly balances, the same as your quarterly balance? If not, I would
use
the suggested table with a new text field [QuarterMonth] to store
either
Q
or M.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
news If I am required to store a quarterly balance, would you recommend
that
I
store that in the tblCustAcctBalances table or in a seperate table
by
itself.
I imagine I will need to keep these quarterly balances for a
significant
amount of time also. Thanks for your suggestions. Matt

"Duane Hookom" wrote:

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account balance
each
month
for each customer. I need to be able to keep each monthly
account
balance
stored for a considerable period of time, lets say 5 years. What
would
be
the best way to store this data? It seems like keeping a column
of
each
month/year would lead to a very large table. Is this the
only/best
way
to
do
this? Suggestions would be very much appreciated. Thanks. Matt

Here is a brief overview of my situation. I have a company who sends me a
text file of data that they have based on data entry in an in-house
application. I need to update my database with this data, then calculate the
quarterly balances and export the quarterly balances to update their system.
Basically all of the manipulation of the data needs to be done on my side.
Also, I need to be able to do a mail merge from my data for letters that we
send to each customer with their monthly balance and then every quarter a
letter with their quarterly balance. That is why I needed to keep the
quarterly balance. However, if I can export from a query then that answers
my question about keeping a quarterly balance for the export. Can I
calculate the quarterly balance in a mail merge or do I need to have an
actual field for that? Thanks again for your help. I know that it is easier
to understand knowing the whole situation.


You can always create a query that calculates the quarterly balances. You
can export the records from the query.

Why would you import the quarterly balances if you can calculate them based
on the monthly balances?

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
That makes sense. However, let's assume that this data will be exported
and
imported into another application. If I were to store the quarterly
balance,
would you recommend it stored in account balances table or a separate
table?
Thanks for your input. I hope I'm not wasting your time with
hypotheticals.
I'm trying to consider all situations that may arise. Thanks again.

"Duane Hookom" wrote:

I would not store a quarterly balance if it can easily be queried from
your
monthly balances.

--
Duane Hookom
MS Access MVP


"Matt" wrote in message
...
The quarterly balance is not the same as one of my monthly balances.
The
quarterly balance needs to be the sum of the previous 3 monthly
balances
from
when the query is run to update the quarterly balance value. This is
why
I'm
asking if I should have a separate table for the multiple quarterly
balances.
There will only be four quarterly balances for each customer per year
while
there will be 12 monthly balances per customer per year. Thanks for
your
help. Matt

"Duane Hookom" wrote:

If you need to store both quarterly and monthly balances, isn't one of
your
monthly balances, the same as your quarterly balance? If not, I would
use
the suggested table with a new text field [QuarterMonth] to store
either
Q
or M.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
news If I am required to store a quarterly balance, would you recommend
that
I
store that in the tblCustAcctBalances table or in a seperate table
by
itself.
I imagine I will need to keep these quarterly balances for a
significant
amount of time also. Thanks for your suggestions. Matt

"Duane Hookom" wrote:

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account balance
each
month
for each customer. I need to be able to keep each monthly
account
balance
stored for a considerable period of time, lets say 5 years. What
would
be
the best way to store this data? It seems like keeping a column
of
each
month/year would lead to a very large table. Is this the
only/best
way
to
do
this? Suggestions would be very much appreciated. Thanks. Matt

You can merge, export, report, print, ... from a query as well as a table. I
still see no reason to store quarterly totals.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
Here is a brief overview of my situation. I have a company who sends me a
text file of data that they have based on data entry in an in-house
application. I need to update my database with this data, then calculate
the
quarterly balances and export the quarterly balances to update their
system.
Basically all of the manipulation of the data needs to be done on my side.
Also, I need to be able to do a mail merge from my data for letters that
we
send to each customer with their monthly balance and then every quarter a
letter with their quarterly balance. That is why I needed to keep the
quarterly balance. However, if I can export from a query then that
answers
my question about keeping a quarterly balance for the export. Can I
calculate the quarterly balance in a mail merge or do I need to have an
actual field for that? Thanks again for your help. I know that it is
easier
to understand knowing the whole situation.


You can always create a query that calculates the quarterly balances. You
can export the records from the query.

Why would you import the quarterly balances if you can calculate them
based
on the monthly balances?

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
That makes sense. However, let's assume that this data will be
exported
and
imported into another application. If I were to store the quarterly
balance,
would you recommend it stored in account balances table or a separate
table?
Thanks for your input. I hope I'm not wasting your time with
hypotheticals.
I'm trying to consider all situations that may arise. Thanks again.

"Duane Hookom" wrote:

I would not store a quarterly balance if it can easily be queried from
your
monthly balances.

--
Duane Hookom
MS Access MVP


"Matt" wrote in message
...
The quarterly balance is not the same as one of my monthly balances.
The
quarterly balance needs to be the sum of the previous 3 monthly
balances
from
when the query is run to update the quarterly balance value. This
is
why
I'm
asking if I should have a separate table for the multiple quarterly
balances.
There will only be four quarterly balances for each customer per
year
while
there will be 12 monthly balances per customer per year. Thanks for
your
help. Matt

"Duane Hookom" wrote:

If you need to store both quarterly and monthly balances, isn't one
of
your
monthly balances, the same as your quarterly balance? If not, I
would
use
the suggested table with a new text field [QuarterMonth] to store
either
Q
or M.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
news If I am required to store a quarterly balance, would you
recommend
that
I
store that in the tblCustAcctBalances table or in a seperate
table
by
itself.
I imagine I will need to keep these quarterly balances for a
significant
amount of time also. Thanks for your suggestions. Matt

"Duane Hookom" wrote:

This is the table I would use:

tblCustAcctBalances
==================
CustAcctID autonumber primary key
CustomerID link to the primary key of your customer table
BalanceDate date/time
BalanceAmount currency

Create a unique index on CustomerID and BalanceDate.

--
Duane Hookom
MS Access MVP
--

"Matt" wrote in message
...
I have a customers table. I will be receiving an account
balance
each
month
for each customer. I need to be able to keep each monthly
account
balance
stored for a considerable period of time, lets say 5 years.
What
would
be
the best way to store this data? It seems like keeping a
column
of
each
month/year would lead to a very large table. Is this the
only/best
way
to
do
this? Suggestions would be very much appreciated. Thanks.
Matt