VBA Excel: how to combine two Access queries

Hi x-perts,

I have two queries:

1) SELECT * from fund_results_view WHERE [date] > #5/1/2009#

2) SELECT * from fund_results_view WHERE [date] > #12/1/2005#

I need to run the 2nd query on the results from the 1st query.

How can I do it in VBA excel? I tried QueryDef object, but it looks as it doesn't exists in ADO VBA excel. Is there a way of doing this in ADO?

Please, help

Thanks
andy7789Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rgn2121Commented:
If the first query pulls data after May 1st, 2009 what good is the second query that pulls data after 2005?  Do you mean that you need to run the first query on the results of the second?
Also, if you have 2 queries just put the one you want to run first in the inside:
 


SELECT * from ( SELECT * from fund_results_view WHERE [date] > #12/1/2005# )
WHERE [date] > #5/1/2009# 

Open in new window

0
andy7789Author Commented:
Thank you. Yes, the logic may sound craze, but it is what I have to do. See the details here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24574134.html

The first query should deselect records with non-existing returns after 3 months before the current date. the second query should deselect everything before 2005.

Can you suggest a better logic?
0
andy7789Author Commented:
I have explained it a bit wrong, sorry. This is the correct logic:

1) I run 1st query

SELECT * from fund_results_view WHERE [date] > #5/1/2009#

the query returns records with fields [id],  [name], [date]

2) next I have to run the second query

SELECT * from fund_results_view WHERE [date] > #12/1/2005#

JOINING the results from the 1st query on field [name]

I understand how to join tables, but how can i join queries?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ravi KallaTechnology LeadCommented:

SELECT * from
(SELECT * from fund_results_view WHERE [date] > #5/1/2009# ) firstSet,
(SELECT * from fund_results_view WHERE [date] > #12/1/2005# ) secondSet
;

Open in new window

0
andy7789Author Commented:
Ravi, it doesn't work that way. The closest logic that works is

1) SELECT * INTO tempTable from fund_results_view WHERE [date] > #5/1/2009#

2)

SELECT * from tempTable
LEFT JOIN fund_results_view ON (tempTable.[name] = fund_results_view.[name])
WHERE fund_results_view.[date] > "12/1/2005"

But it is not a good idea as requires a temp table. Alternatively, I could run a query

SELECT * from fund_results_view WHERE [date] > #5/1/2009#

and filter recordset on date > 12/1/2005

But.... I am still trying to find a way of doing this through a complex query
0
rgn2121Commented:

With A as (
SELECT * from fund_results_view WHERE [date] > #5/1/2009# 
),
B as (
SELECT * from fund_results_view WHERE [date] > #12/1/2005# 
)
 
Select A.ID, A.Name, A.Date
From A, B
WHere A.Name=B.Name

Open in new window

0
rgn2121Commented:
know what...scratch that, I don't think Access will take that if it isn't a pass-through
0
rgn2121Commented:
You could create a query for the first one, one for the second, and then ..
SELECT query1.ID, query1.Name, query1.Date
FROM query1 INNER JOIN query2 ON (query1.Name =query2.Name);
0
andy7789Author Commented:
>You could create a query for the first one, one for the second, and then ..

OK, but what is the whole syntax for the SQL string? I need to pass a single string for VBA execution
0
rgn2121Commented:
create query1 and in it put:
SELECT * from fund_results_view WHERE [date] > #5/1/2009#

create query 2 and in it put:
 SELECT * from fund_results_view WHERE [date] > #12/1/2005#
create query 3 and in it out:
SELECT query1.ID, query1.Name, query1.Date
FROM query1 INNER JOIN query2 ON (query1.Name =query2.Name);
Then in your VBA code either call a query 3 that is similar to the one with the Inner join above using querydefs
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgn2121Commented:
So when you do your .execute or whatever it is...I don't recall off the top of my head, it will run the query3 and execute the underlying queries...
I don't know of a way to right that all in one query without the 3...not saying it isn't possible, but I don't know how if it is...
0
andy7789Author Commented:
This is the way how it works in VBA ADO:

So, I have to make a single SQL string.....

I do not see any feasible solutions except filtering recordset after execution
SQL = "SELECT * from fund_results_view WHERE [date] > #5/1/2009#" 
Set rc = New ADODB.Recordset
Call rc.Open(SQL, connHFI_quant, CursorTypeEnum.adOpenForwardOnly, .......

Open in new window

0
rgn2121Commented:
If you have the query's that work, then what I suggested would work, becuase I use it in an app I currently have.
But I am sure you can filter through them with code too...
Best of luck...
0
andy7789Author Commented:
Thank you! Now I understand why i cannot use your code  - ADO doesn't have querydefs... I have to use command instead and make it too damn complicated.

Anyway, thanks a lot for help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.