Solved

VBA Excel: how to combine two Access queries

Posted on 2009-07-15
14
353 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:andy7789
  • 7
  • 6
14 Comments
 
LVL 12

Expert Comment

by:rgn2121
ID: 24866168
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
 

Author Comment

by:andy7789
ID: 24866380
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
 

Author Comment

by:andy7789
ID: 24866410
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
 
LVL 6

Expert Comment

by:Ravi Kalla
ID: 24866580

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
 

Author Comment

by:andy7789
ID: 24866642
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
 
LVL 12

Expert Comment

by:rgn2121
ID: 24866670

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
 
LVL 12

Expert Comment

by:rgn2121
ID: 24866674
know what...scratch that, I don't think Access will take that if it isn't a pass-through
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 12

Expert Comment

by:rgn2121
ID: 24866687
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
 

Author Comment

by:andy7789
ID: 24866731
>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
 
LVL 12

Accepted Solution

by:
rgn2121 earned 500 total points
ID: 24866751
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
 
LVL 12

Expert Comment

by:rgn2121
ID: 24866756
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
 

Author Comment

by:andy7789
ID: 24866862
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
 
LVL 12

Expert Comment

by:rgn2121
ID: 24867035
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
 

Author Comment

by:andy7789
ID: 24867211
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now