update query--two tables!

[chrisfres]

From what I have found so far, I don't think I'm going to be able to
pull this off, but perhaps someone here can help me out.

The database is a SQL/MSDE database. I'm using an access 2000 project.
(adp).

This is a registration database for a music school. We are forced to
raise our tuition rates starting in February. Charges through June are
already in the database--there are about 4500 fields to update, so of
course I want to do an update query.

I have two tables: [CHARGES] and [CHARGES_DETAIL].
Charges.ID is linked to Charges_Detail.Charge_ID.
I need to update the Amount field in BOTH tables.
The Due Date field only exists in the Charges table (Charges.Due_Date).

Updating the CHARGES table is not a problem. The following stored
procedure worked great:

UPDATE dbo.Charges
SET Amount = 66
WHERE (Due_Date = CONVERT(DATETIME, '2006-02-06 00:00:00', 102))
AND (Amount = 55)

However, updating the CHARGE_DETAIL table! I only want to update the
charge details that are linked to the charges with the due date after
2/1/06. Access won't let me pull criteria from a second table--it'll
only let me work on one table.

Here's the stored procedure that I created:

ALTER PROCEDURE dbo.[charge detail change3]
AS UPDATE dbo.Charges_Detail
SET Amount = 99

From dbo.Charges_Detail INNER JOIN dbo.Charges ON

dbo.Charges_Detail.Charge_ID = dbo.Charges.ID
WHERE dbo.Charges_Detail.Amount = 82.50
AND dbo.Charges.Due_Date = CONVERT(DATETIME, '2006-01-25 00:00:00',
102)

When I run it, I get feedback that it ran successfully, but returned no
records. And sure enough the records in the charges_detail were not
updated.

I'm sure there is something I'm missing and/or just don't know about.

Can someone pretty please help me out?

Thanks!

Chris

 

[Robert Morley]

Was that just a typo or something? You've got a > sign in front of the
"FROM" clause, but at the same time, it looks like you're missing a > sign
in "dbo.Charges.Due_Date = CONVERT..." (i.e., it should be
"dbo.Charges.Due_Date >= CONVERT...").

Other than that, everything looks okay at a quick glance.



Rob

From what I have found so far, I don't think I'm going to be able to
pull this off, but perhaps someone here can help me out.

The database is a SQL/MSDE database. I'm using an access 2000 project.
(adp).

This is a registration database for a music school. We are forced to
raise our tuition rates starting in February. Charges through June are
already in the database--there are about 4500 fields to update, so of
course I want to do an update query.

I have two tables: [CHARGES] and [CHARGES_DETAIL].
Charges.ID is linked to Charges_Detail.Charge_ID.
I need to update the Amount field in BOTH tables.
The Due Date field only exists in the Charges table (Charges.Due_Date).

Updating the CHARGES table is not a problem. The following stored
procedure worked great:

UPDATE dbo.Charges
SET Amount = 66
WHERE (Due_Date = CONVERT(DATETIME, '2006-02-06 00:00:00', 102))
AND (Amount = 55)

However, updating the CHARGE_DETAIL table! I only want to update the
charge details that are linked to the charges with the due date after
2/1/06. Access won't let me pull criteria from a second table--it'll
only let me work on one table.

Here's the stored procedure that I created:

ALTER PROCEDURE dbo.[charge detail change3]
AS UPDATE dbo.Charges_Detail
SET Amount = 99

From dbo.Charges_Detail INNER JOIN dbo.Charges ON

dbo.Charges_Detail.Charge_ID = dbo.Charges.ID
WHERE dbo.Charges_Detail.Amount = 82.50
AND dbo.Charges.Due_Date = CONVERT(DATETIME, '2006-01-25 00:00:00',
102)

When I run it, I get feedback that it ran successfully, but returned no
records. And sure enough the records in the charges_detail were not
updated.

I'm sure there is something I'm missing and/or just don't know about.

Can someone pretty please help me out?

Thanks!

Chris

 

[chrisfres]

The first one is a typo--I don't have a ">" in front of FROM--I guess
something wonky happened when I copied and pasted.

As for the other one--I actually did just want the charges that equaled
2/1/05.

So the code looks ok--that's ironically what I didn't want to hear! I
wonder why access won't do anything with it...