By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,050 Members | 1,564 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,050 IT Pros & Developers. It's quick & easy.

querystring with related database results

100+
P: 229
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.

Expand|Select|Wrap|Line Numbers
  1. description=Request("titleimagesix")
  2. If description <> "" Then
  3.  SQLQuery="Select tblGreetingcategories.categoryname,tblGreetingpostcards.categoryid,tblGreetingPostCards.titleimagesix,tblGreetingPostCards.NewImagesix from tblGreetingcategories,tblGreetingPostCards Where  tblGreetingcategories.categoryid = tblGreetingpostcards.categoryid and titleimagesix='" & replace(description ,"-"," ") & "' "
  4.  
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.

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 31 '12 #1

✓ answered by limweizhong

If you are so sure that the first query will return exactly one record, then you:
1. Create the Recordset
Expand|Select|Wrap|Line Numbers
  1. Dim r as Recordset
  2. Set r = CurrentDb.OpenRecordset(SQLQuery)
2. Retrieve the categoryid field of the first record
Expand|Select|Wrap|Line Numbers
  1. If Not r.EOF Then catid = r.Fields("categoryid")
3. Build the second query filtering for the categoryid that you retrieved.
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.

Share this Question
Share on Google+
1 Reply


P: 62
If you are so sure that the first query will return exactly one record, then you:
1. Create the Recordset
Expand|Select|Wrap|Line Numbers
  1. Dim r as Recordset
  2. Set r = CurrentDb.OpenRecordset(SQLQuery)
2. Retrieve the categoryid field of the first record
Expand|Select|Wrap|Line Numbers
  1. If Not r.EOF Then catid = r.Fields("categoryid")
3. Build the second query filtering for the categoryid that you retrieved.
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 31 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.