dlookup syntax question

[OhioSteve]

1. I have a table called census. Its primary key is a text field called town. It also contains the field population. I want to update the field population.

2. I want to update census.population to the value query1.population. Luckily Query1 contains town, and each record in Query1 contains a different town value.

3. I tried to do an update query to complete the task. Unfortunately access would not allow me to do an update query involving a query with grouping....even though I did not want to update the grouped data (query1 uses the "group by..." clause).

4. I strongly suspect that I need to use dlookup. Unfortunately the dlookup examples I have found all involve hardcoded values in the comparison. I do not want to say town="chicago". I want to say town=[the town from this record in census].

5. I know that I could do this by first making a table out of query1. I do not want to do that because I actually have to perform seven operations of this nature. Seven superflous tables would be ugly.

6. I know that I could solve the problem in VBA. I want to avoid that if possible.

So here are my questions:

Can you give the the exact dlookup syntax that I need?
Or
Will access still stop me from doing the update query if I write it in sql view? Maybe it is like union queries, the gui does not support it, but it is possible.
Or
Do you have another suggestion?

 

[lespaul]

can you please post the SQL of the two queries? It's not really necessary to use DLOOKUP (in fact it can be VERY slow with large recordsets), but it's much easier to help with queries if we can see them.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

A starting point (SQL code):

UPDATE census
SET population = DLookUp("population", "query1", "town='" & [town] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

Thanks for responding, guys. Unfortunately I never found a way to stop this problem consistently. However, I used a different way to give the user data.