SLQ -SELECT <THEN COUNT THEN UPDATE Another Table

[mhall222]

I have been working with this only access database that has about 1000 tables with some huge files. THe selected the info that they needed, lets says

Select bank.account, bank.record, bank.amount
where amount >100 or <-100

then they counted the records

Select Bank.account, count(bank.amount) and then they want to update another table that was created. update bank.amount from the count query.

I am trying to figure out how to do this all in one query, instead of having three queries and three tables. I have played around with subqueries and get unupdatablequeries etc,

I would like to select the data, count the records then update the table with those records.

Any Help would be really appreciate.? Thank you in advance.

 

[PHV]

Something like this ?

UPDATE [another table] A
SET theField=(SELECT Count(*) FROM bankTable WHERE amount BETWEEN -100 And 100 AND account=A.account)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhall222]

This is what i had at one point but i need to count each sub account that are in account numbers from my "current" table My table that i am updating has the account numbers. This is what i had, i am dealing with 700k records. A lot of sub records are in the account numbers though.

Update Metrics

Set metrics.[out of balance]= (select count(1) from current
Where (current.cashdifference) >100 or <-100)
Group by current.accounts
And metrics.account=current.account

I need to =(select account, count somehow so those that i can match those accounts in my metrics table.

My inner join should be metrics.accounts and current.accounts
I really appreciate your help PH.

 

[PHV]

Back to the beginning: what is the SQL code of the three queries you want to replace with a single one ?
FYI, lot of syntax error in your last posred code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhall222]

I apologize for that. I am typing from my iPhone and i wasn't paying attention to parenthesis. The two original queries, the first one was just creating the table so i can count but i don't need to creat a table for that

Table names: metrics and current

1.
Select current.account, count(1) as expr1 into bob
From current
Where(((current.cshdif)<> 0))
Group by current.account;

2. Update

Update metrics inner join bob on metrics.account=bob.account
Set metrics.[out of balance] = iif[bob]![expr1] is null, 0, [bob]![expr1]);

Thank you again PH

 

[PHV]

I'd try this:

UPDATE metrics
SET [out of balance]=(SELECT COUNT(*) FROM current WHERE cshdif<>0 AND account=metrics.account)

Another (slower) way:

UPDATE metrics
SET [out of balance]=DCount("*","current","cshdif<>0 AND account='" & account & "'")

Get rid of the singles quotes if account is defined as numeric in current.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhall222]

The first one, "I'd try this" works but I get the message operation must use an updatable query. How do i make the nested query updatable?!!
Thanks

 

[PHV]

In fact, JetSQL don't like subqueries in the UPDATE statement.
Did you try my second suggestion ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhall222]

Yes but it gave me an error about the count function, not recognizable

 

[PHV]

Which count function ????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhall222]

It says: "undefined function 'dcount' in expression

Thanks ph

 

[PHV]

So, Iif is OK but DCount is not ?
Sorry, I can't help you more unless you omitted to say that you don't run queries but recordset in VBA code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886