It sounds like you are running into a limitation of FP. FP is great for getting started, but I found that I very quickly outgrew it's built in features. I recommend that you either convert to a higher end tool, such as Visual Interdev, or embed yourown ASP code in the FP generated code. FP and Visual Interdev work extremely well together, because you can quickly build a page in FP and then tweak it with Interdev.
Following is some ASP code that can help you do what you are trying to do. Thecode opens a connection to an Access database and then executes a sql command. I've included samples of code from select, update, and append queries. Just change the value of the sqlCommand variable to do what you need to do. Set the sqlCommand and run the Set dbRs line twice to update 2 tables. I know that FP seems easier, but it is limited in what it allows you to do. Feel free to email me with questions.
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/path/database.mdb")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnect
sqlCommand = " SELECT table.* FROM table " _
& "WHERE [table]![field] = value"
Set dbRs = Conn.Execute(sqlCommand)
If Not dbRs.BOF Then
Do While Not dbRs.EOF
rsfieldName = dbRS.Fields("fieldname")
Response.Write(rsfieldname)
Loop
End If
Conn.Close
Set Conn = Nothing
SQL to append to a table:
INSERT INTO Table1 ( test )
SELECT Table1.test
FROM Table1
WHERE (((Table1.test)="1"));SQL to update a table:
UPDATE Table1 SET Table1.test = "2"
WHERE (((Table1.test)="1"));
