Thanks Dale,
I will see if I can do more to properly normalize the data. Thanks for
the
reference.
Unfortunately my problem is that my vendor who sends the report I am
using
for this part of the database does not provide all of the payment types
that
make up the total. I have to extract that information myself which is
why
I
was doing this. however, perhaps I could reorganize the information I
am
given in a more efficient manner.
:
Adrian,
In a well normalized database, you would not normally find columns
where
the
column name contains "data". In your case the columns that contain
data
are
[Pmt A], [Pmt B], [Pmt C], [Pmt D], and [Total]. Generally, you would
have a
Payment Type ("A", "B", "C", "D") and a Pmt_Amount. I would not even
include
the Total, as that can be calculated from the other data. With this
type
of
structure, you might write a "normalizing" query that inserts data
into
this
table. It might look something like:
INSERT INTO tblPayments(MemberID, Pmt_Type, Pmt_Amount, Pay_From,
Pay_To)
SELECT MemberID, Pmt_Type, Pmt_Amount, Pay_From, Pay_To
FROM (
SELECT MemberID, "A" as Pmt_Type, Pmt_A as Pmt_Amount, Pay_From,
Pay_To
FROM yourTable
UNION ALL
SELECT MemberID, "B" as Pmt_Type, Pmt_B as Pmt_Amount, Pay_From,
Pay_To
FROM yourTable
UNION ALL
SELECT MemberID, "C" as Pmt_Type,
NZ([Total], 0) - NZ([Pmt_A], 0) - NZ([Pmt_B], 0) -
NZ([Pmt_D],
0) as
Pmt_Amount, Pay_From, Pay_To
FROM yourTable
UNION ALL
SELECT MemberID, "D" as Pmt_Type, Pmt_D as Pmt_Amount, Pay_From,
Pay_To
FROM yourTable) as Temp
This type of structure makes it far easier to create queries that you
can
use for multiple purposes. For example, there are 14 different ways
you
could select the types of payments (A, B, C, D, AB, AC, AD, BC, BD,
CD,
ABC,
ABD, BCD, ABCD). With your structure, you would have to write 14
different
queries to identify the sume of the various colums. With the well
normalized
version, you would use the same query:
SELECT SUM(Pmt_Amt) FROM yourTable
for all 14 of these cases, and would only need to refine the WHERE
clause
to
indicate which Pmt_Type (s) to include.
This may not be a good example for what you are doing, but will
provide
you
with a better understanding of database normalization.
You might want to consider reading Access Basics (by Crystal) at:
http://www.allenbrowne.com/casu-22.html
----
HTH
Dale
:
Dale,
Thanks, I was able to resolve my issue but in response to your
question:
I am not entirely sure what you mean by separate records, but then I
may not
have been clear enough on what I was doing anyway. I created a
payment
table
that included among other fields the following columns.
Member Id
Member Name
Pmt A
Pmt B
Pmt D
Total Pmt
Pay From
Pay Thru
Since each Member could have multiple records (rows?) based on
dates, I
also
had an auto generated Primary Key.
All of this data other than the key and Pmt C were from the external
excel
table. I needed to calculate the amount for Pmt C (by subtracting
A,
B and
D from the Total) for the table as well as exclude extra unecessary
data
fields from my source table. To do this I created an second
temporary
table
to import my data to, then I created an append query to pull the
data I
needed as well as calculate the Pmt C amount and add it to the
actual
payment
table. I also created a delete query to clean up the temp table so
I
can
reuse it and the append query again each month. It tested well.
:
Adrian,
Are field A, B, D, and Total all included in the same record on
the
spreadsheet?
Are you saving these values all in the same record in your
database,
or as
separate records in a payments table? Generally, the appropriate
method
would be to save each of these values along with the appropriate
account and
payment date (and maybe additional fields) as separate records in
a
payments
table.
----
HTH
Dale
:
Thanks, I am aware of the design issue with storing calculated
data. This
was why I wanted to calculate the data before appending anything
to
the the
table. I only need to run the calculation once, when I first
append the data
and will not need to change it again.
Basically my problem is that my source report is incomplete. I
am
provided
with payment amounts A, B, D and a Total but not payment amount
C.
I need to
extract and store C in my access table. Every month I will get
a
new report
which by using the append function would create a new row in my
access table
rather than change the exisiting data.
:
Adrian,
It is acceptable to do it all in a single query, but I would
caution you
against storing "calculated" values in your tables. If they
can
be
calculated, then I recommend doing the calculations in a
query,
or in the
control Source of an unbound control rather than storing the
value.
Generally, storing calculatable values is considered bad
practice, because
changes in other values that affect this calculatable field
will
not be
reflected in the value you have stored in the table. The
exception to this
is with a database that supports triggers (in SQL Server, you
can
design
triggers that fire when certain fields are changed, causing a
ripple effect
throughout other fields and tables).
----
HTH
Dale
:
I am relatively new to access and database design so I want
to
be sure I am
going in the right direction.
I am setting up a payment history table in access. The
source
of my data is
a monthly excel report that a vendor sends to me. The
original
report has
unnecessary data so I am creating an append query to parse
the
data I do
need. however, my problem is that I also need to calculate
an
additional
value based on the information in the original excel report
and
append the
results to the same access table.
Is it advisable to use the same append query to pull the
data
from the
imported table & do the calculation or do I need to set up
multiple queries
for this function?
I will need to repeat this process every month.
