How to update table

[hondaman2003]

I am using Access 2000. I have a table that has 'accounts' that has a field that is a 'starting balance'. I also have a table called 'adjustments'. This 'adjustments' table has a foriegn key that is the primary key to the 'accounts table'. Basically it works like this, the balance on the account it calculated by summing the adjustments for that account and subtracking from the beginning balance of the account table.

There is a closing date field that we added to the accounts table. I need to be able to mark all accounts that have no balance and no closing date as closed using the date of the last adjustment date from the adjustment table. How can I accomplish this with a single SQL statement?

 

[BigRed1212]

I need to be able to mark all accounts that have no balance and no closing date as closed

Are accounts with no closing date:

select account
from accounts
where closing_date is null

??


Are accounts that have no balance:

select a.account
from account as a left join
(
select account, sum(adjustment) as adjustments
from adjustments
group by account
) as ad
on a.account= ad.account
where a.balance + ad.adjustments=0

?? (Assumes ad.adjustments is a negative number.)


If so, then perhaps accounts that meet your criteria are IN a union of those two lists of accounts.

Don't know.

 

[BigRed1212]

Hmm. Looks like Access doesn't allow UNION in a subquery.

Nevermind, maybe.

 

[BigRed1212]

Ooo. Maybe this:

update account
set status= 'closed'
where account in (
select account
from account d
where account in (
select a.account
from account as a left join
(
select account, sum(adjustment) as adjustments
from adjustments
group by account
) as ad
on a.account= ad.account
where a.balance + ad.adjustments=0) or d.balance is null)

 

[hondaman2003]

BigRed1212,
Thank you for your responses. I will check this out on Monday and let you know how it goes!

 

[BigRed1212]

What is the good word on this?

 

[hondaman2003]

My apologies on this! This is for a work project and I have been detoured this week and not able to get back to work on this one yet. Maybe next week. Please keep your name as a notification on this thread and I will let you know if that is okay?