Solved

VBA Excel: how to combine two Access queries

Posted on 2009-07-15
14
352 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

11 Experts available now in Live!

Get 1:1 Help Now