Solved

VBA Excel: how to combine two Access queries

Posted on 2009-07-15
14
361 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

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

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 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