Need to populate query w/ cells from related table (Record/ FieldName)

[efinnen]

I have two tables. The first one has two fields which match a row and field name in a second table. What I would like to do get the corresponding cell (pardon the Excel analogy) and populate that in a query to Table 1.<br><br>Hopefully this makes more sense and imagine the tables are significantly more then 4 rows, and there is one to one relationship between the PKEY's<br><br>TABLE/Query 1 1<br><br>PKEY&nbsp;&nbsp;&nbsp;Soil Type&nbsp;&nbsp;&nbsp;Curve Number<br>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;This Needs Filling with Curve Numbers<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from Table 2<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B/D<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D<br><br>Table 2 (Curve Numbers)<br><br>FKey&nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B/D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D<br>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;95&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;32&nbsp;&nbsp;&nbsp;&nbsp;45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;58<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;32&nbsp;&nbsp;&nbsp;&nbsp;42&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;78<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;87&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;56&nbsp;&nbsp;&nbsp;&nbsp;43&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;87<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;37&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;42<br><br>Any help/advice/quidance would be greatly appreciated.<br><br>Thanks<br>Eric<br><A HREF="mailto:efinnen@ithink.net">efinnen@ithink.net</A><br><br><br>

 

[WaltLukeIII]

Go To the query tab and click on new&nbsp;&nbsp;then choose design view&nbsp;&nbsp;and click the button then choose the tables to show once you have those close the show table window and then put your fields in in the order that you want them in. That should work.<br> <p>Walt III<br><a href=mailto:SAElukewl@netscape.net>SAElukewl@netscape.net</a><br><a href=

http://www.muc.edu/~lukewl>homepage</a><br>

 

[efinnen]

I appreciate the feedback, but I might not have made myself clear. For example in my mini sample table. My Curve Numbers should look something like<br><br>PKEY&nbsp;&nbsp;&nbsp;Soil Type&nbsp;&nbsp;&nbsp;Curve Number<br>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;95<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;32<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B/D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;43<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;42<br><br>and so on (mind you there will be about 15 rows) and seven different soil types<br>

 

[WaltLukeIII]

So what you are saying is that you want to look at the value entered into the first table then based upon what type of soil and the pKey you want to fill the third value.<br><br>I would do this with an update query. <br><br>in a form I would have that form related to the first table then I would set it so that when a button was pushed the following code would execute.<br><br>dim db as database, recset as recordset, recset2 as recordset<br>dim value as string, setTo as integer<br><br>set db = dbengine.workspaces(0).databases(0)<br>set recset = db.openRecordset(&quot;first table&quot;, db_open_dynaset)<br>set recset = db.openRecordset(&quot;second table, db_open_dynaset)<br><br>recset.findfirst &quot;[pkey] = Criteria &quot; <br>value = recset![Soil]<br><br>recset2.findfirst &quot;[pkey] = criteria&quot;<br>setTo = recset2![value]<br><br><br>recset.edit<br>recset![Curve number] = setTo<br>recset.Update<br><br>I think that that should work for you. <p>Walt III<br><a href=mailto:SAElukewl@netscape.net>SAElukewl@netscape.net</a><br><a href=

http://www.muc.edu/~lukewl>homepage</a><br>

 

[efinnen]

I must say. I appreciate the help you've been giving me but I still have a couple of questions about the above code.<br><br>I don't seem to have the ability to perform<br><br>recset.findfirst <br>(it will do recset.find which I tried) nor will it do<br><br>recset.edit <br>(I'm allowed recset.editmode)<br><br>I tried with the changes that were allowed and then I got a type mismatch error. Hopefully I don't have my head up my butt to much, and any help showing me the light would be cool.<br><br>Thanks<br>Eric<br>

 

[WaltLukeIII]

Do you know what version of access you are using. on the type mismatch Make sure that the fields are declared correctly i.e. that you are putting a number in a number field. <p>Walt III<br><a href=mailto:SAElukewl@netscape.net>SAElukewl@netscape.net</a><br><a href=

http://www.muc.edu/~lukewl>homepage</a><br>