Solved

VBA Excel: how to combine two Access queries

Posted on 2009-07-15
14
356 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

786 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