query a query in VBA

Question

Hi guys I dont know if this makes sense but how can I query another query in VBA?

I will show with example below

This is my first query

strSQL1 = "SELECT DISTINCT SourceBank" _
    & ", Fullname, FirstNames" _
    & ", Surname, Company" _
    & ", EmailAddress" _
    & " FROM question" _
    & " WHERE FirstNames = '" & strFirstNames & "'" _
    Set rs = dbs.OpenRecordset(strSQL)

Then I want to do something like this. Query the first query

        strSQL2 = "S"SELECT * from " & strSQL1
    Set rs1 = dbs.OpenRecordset(strSQL)

I just want to know if this is possible and if not then what is the best way around it? All I want to do is to be able to query another query/string/recordset.

thanks

why do you use tag [mysql]? ms-access use Jet SQL.

4dmonster
– 4dmonster

2013-09-24 10:30:51 +00:00
Commented Sep 24, 2013 at 10:30 — 4dmonster
– 4dmonster, Commented Sep 24, 2013 at 10:30

4dmonster · Accepted Answer · 2013-09-24 11:20:08Z

2

You can do it almost like you've wrote:

strSQL2="SELECT * FROM (" & strSQL1 & ")"

but be sure not to include ; in strSQL1

upd, try:

strSQL2 = "SELECT Question.EmailAddress, SUBQUERY.EmailAddress &" _ 
          & "FROM Question LEFT JOIN (" & strSQL1 & ") AS SUBQUERY ON Question.EmailAddress = SUBQUERY.EmailAddress"

OR just save sql1 into QueryDef (Query in ms access) and use it like source table.

edited Sep 24, 2013 at 11:20

answered Sep 24, 2013 at 10:39

4dmonster

3,0411 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user2528303 Over a year ago

and how is it used in a query with a left join? <code> strSQL1 = "SELECT Question.EmailAddress, (" & strSQL & ").EmailAddress &" _ & "FROM Question LEFT JOIN (" & strSQL & ") ON Question.EmailAddress = (" & strSQL & ").EmailAddress" </code>

user2528303 Over a year ago

hmm. I'm only getting back 1 record count when I should be getting a lot more

user2528303 Over a year ago

i changed the join type but still get the same count

4dmonster Over a year ago

left join was to select all Question and some connected rows from subquery. what result do you need?

user2528303 Over a year ago

Left join is what I want. I want all records from question and only matching records from strSQL1 where the email matches. It's just that I'm not getting the 18k records I should be getting. I'm only getting one from this - RsCount = rs1.RecordCount

|

Collectives™ on Stack Overflow

query a query in VBA

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related