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 said:
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.
:
I would not store a quarterly balance if it can easily be queried from
your
monthly balances.
--
Duane Hookom
MS Access MVP
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
:
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
--
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
:
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
--
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
