Get Table Value from a Query

[Guest]

I have a Query call Balance and a Table called Account. The query totals up
3 different values and puts them into a field called Balance. What I want is
the Account Table to pull Balance value from the Query and display it in the
table but I'm not sure how to do that. I tried a DLookup in the default
value of the table field but that did not work.

 

[Guest]

Please provide more information on
The fields of Account
the tables and fields in query Balance
What table you want to put the balance into

 

[Guest]

AccountTable -- fields -- Balance, ClientNo, AccountNo
BalanceQuery -- fields -- ClientNo, Balance, Payee, Restricted, NonRestricted

In the BalanceQuery Balance is Payee, Restricted, NonRestricted all added
together. I then want the Balance from BalanceQuery to appear in the
AccountTable Balance field for each ClientNo.

 

[Guest]

IF BalanceQuery has just one record per ClientNo then you can use this

UPDATE AccountTable LEFT JOIN BalanceQuery ON AccountTable.ClientNo =
BalanceQuery.ClientNo SET AccountTable.Balance = [BalanceQuery]![Balance];

I sense that I still don't have all the info...
Can a Client have >1 Account ? If so, how does BalanceQuery accomodate
multiple accounts per client?

 

[Guest]

Thanks for your help, I am new to Access. But to answer your question a
Client can only have one Account. And a quick question about the code that
you wrote. Does that get placed in the BalanceQuery criteria or in the
AccountTable?

 

[Guest]

The code I wrote is SQL for a new query that will update the AccountTable.

If the field and table names match what I wrote, you can

Create a new query in design view
Add the AccountTable and BalanceQuery to the query
Choose View/SQL View from the main menu
Copy and paste the SQL from this message into the new SQL window(after
deleting anything that is there)
Switch back to Design view by choosing View/Design View from the main menu

Thanks for your help, I am new to Access. But to answer your question a
Client can only have one Account. And a quick question about the code that
you wrote. Does that get placed in the BalanceQuery criteria or in the
AccountTable?

IF BalanceQuery has just one record per ClientNo then you can use this

UPDATE AccountTable LEFT JOIN BalanceQuery ON AccountTable.ClientNo =
BalanceQuery.ClientNo SET AccountTable.Balance = [BalanceQuery]![Balance];

I sense that I still don't have all the info...
Can a Client have >1 Account ? If so, how does BalanceQuery accomodate
multiple accounts per client?