querystring with related database results

[fran7]

Hi, I wonder if you anyone could give me a pointer as to how to return related results to a main query.
I have a main query from an access database, like this, it works fine.

description=Request("titleimagesix")
If description <> "" Then
 SQLQuery="Select tblGreetingcategories.categoryname,tblGreetingpostcards.categoryid,tblGreetingPostCards.titleimagesix,tblGreetingPostCards.NewImagesix from tblGreetingcategories,tblGreetingPostCards Where  tblGreetingcategories.categoryid = tblGreetingpostcards.categoryid and titleimagesix='" & replace(description ,"-"," ") & "' "
 

It returns one result as expected.

Below this result i want to also list all other records that can be found with the same categoryid as the above query. I have the code below but it returns all results and not those only related to the above query. I imagine its something like adding where titleimagesix=categoryid but of course i dont know how to write it or indeed if thats right at all. Or if i need to include the main query in this one.

SQLQuery="Select tblGreetingcategories.categoryid,tblGreetingpostcards.categoryid,tblGreetingPostCards.titleimagesix,tblGreetingPostCards.NewImagesix from tblGreetingcategories,tblGreetingPostCards Where  tblGreetingcategories.categoryid = tblGreetingpostcards.categoryid "

Thanks in advance for any pointers
Richard

[limweizhong]

If you are so sure that the first query will return exactly one record, then you:
1. Create the Recordset

Dim r as Recordset
Set r = CurrentDb.OpenRecordset(SQLQuery)

2. Retrieve the categoryid field of the first record

If Not r.EOF Then catid = r.Fields("categoryid")

3. Build the second query filtering for the categoryid that you retrieved.

SQLQuery2 = "... categoryid='" & catid & "' ..."

(fill in the "..." and remove the single quotes if categoryid is not a text type)

Search "Microsoft DAO Recordset object" for more information on Google.

However, if your first query has multiple records, then you will need to include the main query in the second one. There are a few ways:

Use the filter:

tblGreetingcategories.categoryid in (select tblGreetingcategories.categoryid from ...)

where ... is the remainder of the main query. Search "sql in operator" on Google.

Another way is to do a join with a query retrieving unique categoryid(s) of the main query, using a real nested query, but I am not sure if the new version of Microsoft Access supports this. From previous experience with old versions, the nested query needs to be saved as a QueryDef.

Note that you should also change both your queries each to use a join with the join criterion tblGreetingcategories.categoryid =tblGreetingpostcards.categoryid, using the "__table1name__ [left] join __table2name__ on __join_criterion__ " construct. Search "sql joins" on Google.

However, if you are using this query directly in a form/report, you might want to just use a sub-form/sub-report, and set the appropriate "child/master" linking fields. Search "Microsoft Access subforms" on Google.