Help with Update Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Me again! This time need help with an Update Query. Here is my code:

ALTER PROCEDURE dbo.sp_OUTSTANDING_UPDATE
AS UPDATE dbo.tbl_MSTRACC
SET tbl_MSTRACC.OUTSTANDING = tbl_OUTSTANDING.OUTSTANDING -
tbl_OUTSTANDING.CURRPMNT
FROM dbo.tbl_MSTRACC, dbo.tbl_OUTSTANDING
WHERE tbl_MSTRACC.DEBTOR_IDX = tbl_OUTSTANDING.DEBTOR_IDX

I'm not getting the correct values in the tbl_MSTRACC.OUTSTANDING field.
Can anyone see why, or maybe it's something else?

Thanks.
 
winsa said:
Hi

Me again! This time need help with an Update Query. Here is my code:
Click to expand...

I'm not getting the correct values in the tbl_MSTRACC.OUTSTANDING field.
Can anyone see why, or maybe it's something else?

Thanks.
Click to expand...

winsa,

Ok, now I'm *certain* you're using MS SQL Server.


ALTER PROCEDURE dbo.sp_OUTSTANDING_UPDATE
AS
UPDATE dbo.tbl_MSTRACC
SET tbl_MSTRACC.OUTSTANDING =
tbl_OUTSTANDING.OUTSTANDING - tbl_OUTSTANDING.CURRPMNT
FROM dbo.tbl_MSTRACC
,dbo.tbl_OUTSTANDING
WHERE tbl_MSTRACC.DEBTOR_IDX = tbl_OUTSTANDING.DEBTOR_IDX

ALTER is used to change existing procedures (for corrections,
production updates). It wouldn't be something run normally (at least
I wouldn't imagine it would be). May I ask why this is ALTER
PROCEDURE?

As for the Update statement in the stored procedure, the syntax looks
ok at first glance (MS SQL Server was willing to successfully parse
the Update portion).

As for why you're not receiving correct values? It's impossible to
know without knowing what the sample data, expected results, and
actual results are.

(FYI: Many in microsoft.public.sqlserver.programming will want you to
provide the *full* DDL of the Tables, and the *full* INSERT scripts to
load the tables with sample data, in addition to all the Transact-SQL
involved.)

You can repost over to: microsoft.public.sqlserver.programming (which
would be the most appropriate on-topic course of action), or post your
DDL, INSERTS, and T-SQL here and I'll take a look at it.


Sincerely,

Chris O.
 
Hi Chris2

Thanks again for replying to my post.

Again, I think I’m having more problems with the query rather than the SQL
as I don’t know what I’m doing with the SQL behind the query that I'm trying
to create, hence why I posted in this discussion, not the SQL discussions.

What I really want to do is update a field in one table with the data in a
field of another table. When I tried to create the update query in the QBE
pane, I got the message that I can't update more than one table. This
procedure works fine in an mdb, but since I'm trying to work in an adp, I'm
getting errors.

I hunted around and found the code below, without the ALTER PROCEDURE line.
When I tried to run this, the QBE window disappeared and I got what I assume
is an SQL window (?) instead of the QBE window as Access couldn’t display the
query and the ALTER PROCEDURE line was added.

I'm not quite sure what you are referring to with DDL, INSERT and TSQL
(well, I do know what this is, but not sure where to find the TSQL to be
able to include it). I'll try and explain it another way.

tbl_MSTRACC includes a field for the amount of an invoice (AMOUNT) and what
is still outstanding on that invoice (OUTSTANDING). tbl_OUTSTANDING is more
of a temporary table (which is created via another query) that brings up all
invoices outstanding for a particular debtor and users enter payments
received against those invoices via a form. What I then want to do is to
update tbl_MSTRACC with the payments received in tbl_OUTSTANDING (this should
equal tbl_MSTRACC.OUTSTANDING - tbl_OUTSTANDING.CURRPMT). What I end up
getting after running the query is not what I expected.

Sample data
tbl_MSTRACC
ID DEBTOR TYPE NARRATIVE AMOUNT OUTSTANDING
1 100 INV MV3800 7710 0
2 100 CN MV3800CRE -72 0
3 053 INV PA3367 410 0
4 053 INV SR276 11536 0

tbl_OUTSTANDING
ID DEBTOR TYPE NARRATIVE AMOUNT CURRPMT
1 100 INV MV3800 7710 856
2 100 CN MV3800CRE -72 -72
3 053 INV PA3367 410 0
4 053 INV SR276 11536 10000


When I run the update query, I get the following data:
tbl_MSTRACC
ID DEBTOR TYPE NARRATIVE AMOUNT OUTSTANDING
1 100 INV MV3800 7710 0
2 100 CN MV3800CRE -72 0
3 053 INV PA3367 410 1536
4 053 INV SR276 11536 1536

When updated, invoice #1 outstanding should read 6854, invoice 2 is correct,
invoice 3 should read 410 and invoice 4 should read 1536.

I hope I’ve made myself a little more clear.

Thanks
winsa
 
winsa said:
Hi Chris2

Thanks again for replying to my post.

Again, I think I’m having more problems with the query rather than the SQL
as I don’t know what I’m doing with the SQL behind the query that I'm trying
to create, hence why I posted in this discussion, not the SQL
Click to expand...
discussions.

This topic *is* about JET SQL. The QBE Grid is a GUI shell that
*makes* JET SQL. The QBE grid is, by and large, quite difficult to
explain in written terms (even if it is useful for certain tasks). MS
Access books use lots of pictures for that subject, and they need to.
What I really want to do is update a field in one table with the data in a
field of another table. When I tried to create the update query in the QBE
pane, I got the message that I can't update more than one table. This
procedure works fine in an mdb, but since I'm trying to work in an adp, I'm
getting errors.

I hunted around and found the code below, without the ALTER PROCEDURE line.
When I tried to run this, the QBE window disappeared and I got what I assume
is an SQL window (?) instead of the QBE window as Access couldn’t display the
query and the ALTER PROCEDURE line was added.
Click to expand...

You are running into an artifact of the interface between MS Access
and the SQL Server itself.

JET SQL: MS Access' dialect of the SQL relational database query
language.
Transact-SQL: MS SQL Server's dialect of the SQL relational database
query language.

SQL Server has something called Stored Procedures (and so to many
other major database products). These are collections of Transact-SQL
statements stuck together, and "wrappered" with an interface to the
outside world. The procedure may be called by using its name and the
correct calling syntax. It's pre-compiled, and security controlled.
In many respects, it's like an MS Access QueryDef (only with more
bells and whistles). I believe an Access Project lets you create
stored procedures on the attached database.

If you're going to do an Access Project, you will *need* to know
Transact-SQL, or the type of thing you've run into with the & bitwise
operator will continue to strike.


Sincerely,

Chris O.
 
Hi Chris

Thanks again for your reply. I worked out the problem with the Update Query
- it's just that I had the wrong fields linked as the temporary join.

Looks like I'm going to have to learn a lot more about Transact-SQL!

Thanks
winsa
 
Back
Top