Tricky SQL - this is tough!

[bboht]

Here is my situation:

I have 2 data controls - one is for a header table and the other is for a detail table. Let's say, to simplify things, the tables look like this

Header Table - fields
ID
X

Detail - fields
ID
NOTE

The recordsource for header is; SELECT * FROM HEADER WHERE X = 1 ORDER BY ID

The recordsource for detail is; SELECT * FROM DETAIL ORDER BY ID

Now, what I want to do, is open a third record set in my code that will use an inner join to get all the records in detail for all the records in header (where x=1). The sql without data control would look something like this (I think)

SELECT DETAIL.ID, DETAIL.NOTE, HEADER.ID FROM DETAIL INNER JOIN HEADER ON DETAIL.ID = HEADER.ID

I can't figure out the syntax (if even possible) on how to do this. (Because of the data controls) My closest gues is something like this.

"SELECT " & rstDetail & ".ID, " & rstDetail & " .NOTE"
...etc etc.

Does anyone have any ideas on this? if my problem is unclear please tell me.

[PJB]

Not sure if this is what your looking for or not...

Private Sub Data1_Reposition()
Dim HEADERID As Long
HEADERID= Data1.Recordset!ID
Data2.recordSource = "Select * From DETAIL Where ID = " &HEADERID
Data2.Refresh
End Sub

don't know if that'll work for ya or not...

[bboht]

Thanks for the reply but that doesn't work. Lets say the header has 4 records, and 2 of them have Header!X = 1 and 2 have Header!X = 0. The Detail control has 8 records, to records for each Header!ID. Now my Header Data control has 2 records (the where X = 1). So what I want, is the four records that correspond with the ID's in the header. You use a join to do this. If is simple SQL, it is just messing me up because I have to use data controls. The code you show would only give me 2 detail records, the 2 that have the ID that equals HEADERID.