Using Append to Update & Calculate Data

[Adrian]

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.

 

[Dale Fye]

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

 

[Adrian]

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.

 

[Dale Fye]

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.

 

[Adrian]

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]

Dale,

Each Pmt A, B, C, D is a different type of payment rather than another
occurance of the same type.

 

[Dale Fye]

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

 

[Adrian]

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.

 

[Dale Fye]

Adrian,

Data we get from outside sources is rarely in a good format. It is usually
in Excel worksheets (don't confuse these with tables) or in CSV or fixed
width column text files. Although our first inclination is to just import
these, and use them in the format they are in, it is rarely the "best" way
to do it for database purposes.

Good luck!

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.

 

[Adrian]

Thanks. My intent for importing the excel worksheets is so I can set up a
series of queries to normalize the data I receive each month. This should be
easier to do than to fix the data in excel before importing. At least once
it's set up.

Anyway, I have one more question. each report has one record per individual
(by ID) with several different payment type columns and several deduction
columns and a pay from and pay thru date. As you suggested, I could
organize the payment colums into a single payment type category, but I am
sure how to handle the deductions. I could treat them as a payment type
simply ones with a negative value or I could organize separate payment table
and separate deduction table. the end result is I will need to know all of
the payments and deductions per individual for a given period, usually
monthly or annually. I am not sure which will be easier to build
queries/reports from a single combined table or two? One payment one
deduction? Or is this more a matter of preference?

Adrian,

Data we get from outside sources is rarely in a good format. It is usually
in Excel worksheets (don't confuse these with tables) or in CSV or fixed
width column text files. Although our first inclination is to just import
these, and use them in the format they are in, it is rarely the "best" way
to do it for database purposes.

Good luck!

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.

 

[Dale Fye]

Thanks. My intent for importing the excel worksheets is so I can set up a

series of queries to normalize the data I receive each month. This should
be
easier to do than to fix the data in excel before importing. At least
once
it's set up.

I strongly agree. At least that is the method I prefer

Anyway, I have one more question. each report has one record per
individual
(by ID) with several different payment type columns and several deduction
columns and a pay from and pay thru date. As you suggested, I could
organize the payment colums into a single payment type category, but I am
sure how to handle the deductions. I could treat them as a payment type
simply ones with a negative value or I could organize separate payment
table
and separate deduction table. the end result is I will need to know all
of
the payments and deductions per individual for a given period, usually
monthly or annually. I am not sure which will be easier to build
queries/reports from a single combined table or two? One payment one
deduction? Or is this more a matter of preference?

Well, IMHO, I the more complete the normalization, the better (although
there are exceptions).

In your case, I strongly suggest going with the deductions just being
another payment type, although in this construct, the Deductions would not
be negative values, any more than the payments would be.

You never mention what these payments are for, but I assume that this is for
rent or some king of loan, or something along those lines. So, you might
also want to create a query that sums the debits and payments for each ID
prior to the start date of this report. You could then link that query into
your people table so that you would have each individuals Previous Balance
in the reports main query.

This query (qry_PrevBalance) might look something like:

SELECT Balance.People_ID, SUM(Balance.Amt) as PrevBalance
FROM
(SELECT tbl_Debits.People_ID, tbl_Debits.Debit_Amt as Amt
FROM tbl_Debits
WHERE tbl_Debits.Debit_Date < #6/1/09#
UNION ALL
SELECT tbl_Payments.PeopleID, -tbl_Payments.Pmt_Amt as Amt
FROM tbl_Payments
WHERE tbl_Payments.Pmt_Date < #6/1/09#) as Balance
GROUP BY Balance.People_ID

With this query, a positive sum would imply a debit of that amount.

Then, the query for your report might look something like:

SELECT P.ID, P.Name, P.Address, P.City, P.State, P.Zip, NZ(Q.PrevBalance) as
PrevBalance
FROM tbl_People as P
Left JOIN qry_PrevBalance as Q
ON P.ID = Q.People_ID

Then, in your report, you could put all of this information in a group
header

Then, create a subreport that contains the debits and payments for the
selected period; something like:

SELECT tbl_Debits.People_ID, "Debit" as Reason, tbl_Debits.Debit_Amt as Amt2
FROM tbl_Debits
WHERE tbl_Debits.Debit_Date >= #6/1/09# AND tbl_Debits.Debit_Date <=
#7/1/09#
UNION ALL
SELECT tbl_Payments.PeopleID, tbl_Payments.Pmt_Type, -tbl_Payments.Pmt_Amt
as Amt
FROM tbl_Payments
WHERE tbl_Payments.Pmt_Date >- #6/1/09# AND tbl_Payments.Pmt_Date <=
#7/1/09#

In the subreport, you could add all three of these fields, an extra copy of
the Amt field (which you could hide but which would give you a running sum
of the debits and payments for the report period, and finally another extra
textbox which would sum the PrevBalance from the Group header and the
textbox that contains the running sum for report period, although this
running balance is generally not necessary.

In the report footer of the subform, you could add another textbox that Sums
the Previous balance from the group header, and the SUM of the amount values
from the subreport. The control source might look like: =
reports!yourReport.PrevBalance + Sum([Amt])

Hope this isn't too much for you to handle all at once. If you need more
help, post back.

Adrian,

Data we get from outside sources is rarely in a good format. It is
usually
in Excel worksheets (don't confuse these with tables) or in CSV or fixed
width column text files. Although our first inclination is to just
import
these, and use them in the format they are in, it is rarely the "best"
way
to do it for database purposes.

Good luck!

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.