Please explain this update query?!?

[Guest]

Hi,

I'm using Access 2k, linked to SQL Server 2k. I have a prob with an update
query and I hope someone can explain it to me.

TESTMSTRACC = stores invoices
RECEIPTDETAILS = stores payment transactions

I have an update query that updates the Outstanding Invoice amount in
TESTMSTRACC with the payment from RECEIPTDETAILS as follows:

ALTER PROCEDURE dbo.sp_RECEIPTDETAILS_UPDATE
AS UPDATE dbo.TESTMSTRACC
SET dbo.TESTMSTRACC.OUTSTANDING = TESTMSTRACC.OUTSTANDING -
TEST_RECEIPTDETAILS.CURRPMNT
FROM dbo.TESTMSTRACC, dbo.TEST_RECEIPTDETAILS
WHERE TESTMSTRACC.MST_TRANS_IDX = TEST_RECEIPTDETAILS.MST_TRANS_IDX

(I have no idea about the first line as Access inserted it when I created
the query and won't seem to run without it!)

This update query works fine as it updates the values that I want it to,
however, I seem to having a problem with multiple transactions, ie.
RECEIPTDETAILS might contain two separately entered transactions (entered on
two separate days) that pay off the one invoice with two separate payments:

Invoice101 = $200
Receipt1 = $100
Receipt2 = $100

Apparently when I run the update query, it updates the invoice with the
first Receipt transaction, but it seems to ignore the second transaction?
Therefore when I do a reconciliation, I'm shown that Invoice101 still has
$100 outstanding, when it fact it has already been paid.

I would have assumed that the update query would calculate the first
transaction, $200-$100, then calculate the second transaction $100-$100 to
equal 0. Is this not the case?

Thanks for any explanations!
Winsa

 

[MGFoster]

Hi,

I'm using Access 2k, linked to SQL Server 2k. I have a prob with an update
query and I hope someone can explain it to me.

TESTMSTRACC = stores invoices
RECEIPTDETAILS = stores payment transactions

I have an update query that updates the Outstanding Invoice amount in
TESTMSTRACC with the payment from RECEIPTDETAILS as follows:

ALTER PROCEDURE dbo.sp_RECEIPTDETAILS_UPDATE
AS UPDATE dbo.TESTMSTRACC
SET dbo.TESTMSTRACC.OUTSTANDING = TESTMSTRACC.OUTSTANDING -
TEST_RECEIPTDETAILS.CURRPMNT
FROM dbo.TESTMSTRACC, dbo.TEST_RECEIPTDETAILS
WHERE TESTMSTRACC.MST_TRANS_IDX = TEST_RECEIPTDETAILS.MST_TRANS_IDX

(I have no idea about the first line as Access inserted it when I created
the query and won't seem to run without it!)

This update query works fine as it updates the values that I want it to,
however, I seem to having a problem with multiple transactions, ie.
RECEIPTDETAILS might contain two separately entered transactions (entered on
two separate days) that pay off the one invoice with two separate payments:

Invoice101 = $200
Receipt1 = $100
Receipt2 = $100

Apparently when I run the update query, it updates the invoice with the
first Receipt transaction, but it seems to ignore the second transaction?
Therefore when I do a reconciliation, I'm shown that Invoice101 still has
$100 outstanding, when it fact it has already been paid.

I would have assumed that the update query would calculate the first
transaction, $200-$100, then calculate the second transaction $100-$100 to
equal 0. Is this not the case?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In general, good DB design discourages storing calculated values in
tables 'cuz of the "expense" of updating the data every time one of the
parameters changes; or, "remembering" to run updates in a timely manner.
The way to get summations is thru SELECT queries that use the SQL
aggregate functions (SUM(), Count(), AVG(), etc.).

What you have printed, above, is the DDL command to alter a stored
procedure (SP). Therefore, you are using an Access Data Project (.adp
file), with a SQL Server (SQL'r) back-end. This means, it would behoove
you to learn how to program in T-SQL, the SQL version used in SQL'r.
Read the SQL'r Books On Line (BOL) for articles on how to program using
T-SQL - start off w/ ALTER PROCEDURE.

If you wish to have just an answer, here is your statement changed to
get the summation of the payments:

ALTER PROCEDURE dbo.sp_RECEIPTDETAILS_UPDATE
AS
UPDATE dbo.TESTMSTRACC
SET OUTSTANDING = OUTSTANDING -
(SELECT Sum(CURRPMNT) FROM dbo.TEST_RECEIPTDETAILS
WHERE MST_TRANS_IDX = TESTMSTRACC.MST_TRANS_IDX)

I use standard SQL syntax for the UPDATE since there are some reported
problems using Microsoft's proprietary FROM syntax in an UPDATE
statement.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnk4lIechKqOuFEgEQJuawCeNCvjNS1N7EQBx6OLM4p5kGnBGjUAnRhI
ZutcQ0CrH92aObNFipdpJg66
=Nwvk
-----END PGP SIGNATURE-----

 

[Guest]

Hi

Thanks muchly for your suggestion. I unfortunately won't get to try it out
until next week.

I know that it's bad practice to store a calculated field, but I cannot
think of any other way (and I've been sweating over this for months) to
handle part-payment of invoices than to have an "outstanding" field and have
that update everytime a payment is made against an invoice.

Unfortunately this database is inherited! I would have just used an mdb and
connected it to SQL rather than using an adp, but sometimes you just don't
have a choice! Learning TSQL is down the bottom of my long list of things to
do!

Cheers!
winsa