how to reference current row in SQL string?

[Jax.Zeng]

how to reference current row in SQL string?
hi, i have a qst while using RowSource.

i have 3 tables:

[1] CITY
col: _value _name

[2] STREET
col: _city _value _name

[3] ADDRESS
cal: city street

the problem ocurred when i designed ADDRESS table:
the RowSource of [city] is "SELECT _name, _value FROM CITY".
and i set the binding-column as 2,
so that _name can be displayed and the real value is _value(integer).
but here comes the trouble: how can i set the RowSource of [street]?
---->>>
the _value of STREET table depends on _city, and _city is _value in CITY table.
eg. _name of STREET is "Wall Street" when (_city,_value) = (1,1),
but "Lincoln Avenue" when (_city,_value) = (2,1)
i want to use "SELECT _name, _value FROM STREET WHERE _city=CurrentRow.city" as RowSource,
but what can replace the "CurrentRow"?
can anybody help me? thanks!

:banghead:

 

[Mihail]

A picture with your relationships is enough to understand your problem and to help yo.

 

[plog]

Sample data would be more help than a relationship diagram, although that couldn't hurt.

However, I think you've overnormalized your data. Is your database a Geographic Information data set? Or is it about customers or businesses or some other entities that have addresses? If its the latter, you have overnormalized and need the data in those 3 tables in just one table.

Taking a 3rd look at it, what you want to do, would be best done in a form and not a table. If I understand what you want when building an ADDRESS record is for someone to select a city from a drop down and upon that selection the street drop down would show just those streets related to that city in the STREET table. If that's correct, the place to do that is in a form and not a table.