Hello, (using PHP/MySQL)

I need to filter a recordset with a field from a different recordset but can't seem to find a solution.

Basically this is what is happening, and what I need to happen:

(The example below deals with a table "sales" that holds information about sales that businesses are having.)

(1) A Results page passes a "sale_id"(autonumber) to a Details page.

(2) On the Details page, a Recordset "rs_sale_details" uses the sale_id to retrieve and display information about that specific sale.

(Now an important thing, is that a single business could have multiple sales listed. So in the table "sales" there is also a field named "client_id" which holds information like "mcdonalds" or "pizzahut" or "bobsauto". So several records could have the same client_id. This way I can group all sales for a single client.)

(3) On that same Details page which is displaying a single sale, for a single client... I'd like to have a second recordset "rs_all_sales" that also gets information from the db table "sales" and fills a table in a Repeat Region that will also display ALL THE SALES (in short form, like just the title of the other sales) for THE SAME SPECIFIC CLIENT. You know, so when a visitor is looking at the details page, he can see all the other sales that that same business are running.

Problem is, I don't know how to get the second recordset "rs_all_sales" to retrieve ALL THE SALES for that specific business "client_id" since the only information being passed to "rs_all_sales" is a "sale_id" which is too specific and causes only one record to show.

What I want to do in "rs_all_sales" is have it get the "client_id" from "rs_sale_details", and then have it display all sales that have the same "client_id".

God I hope this makes sense!

I've tried the following, but it only displays sales if I put a client_id in the Default Value field. It doesn't seem to get the value dynamically from the first recordset like I want it to.

So in short... HELP!!

Peter

SELECT sale_id,client_id,sale_title,sale_start_date,sale_end_date
FROM ss_sales
WHERE sale_client_id='varclientid'

Name -------- Default Value ---- Run-time Value
varclientid --- whatever -------- $rs_sale_details["client_id"]