asked on

Using INTERSECT in SQL query from Access to SQL Server

I'm using Access 2003 - ADP against SQL Server 2000 using ADO

I have the following problem I'm trying to solve:
I'm trying to derive values from a single table that match month and year

field: ss_month
field: ss_year

start_month = 10
start_year = 2005

end_month = 5
end_year = 2006

I want to select all records that are dated between start_month/start_year (e.g. 10/2005) and end_month/end_year (e.g. 5/2006)
The only way I can figure out how to do this is to do a true intersect

Select the_id from table1 WHERE ss_month >= 5 AND ss_year >= 2005
INTERSECT
Select the_id from table1 WHERE ss_month <= 6 AND ss_year <= 2006

This, however, does not work in Access. The individual queries work fine, and they work fine when using UNION (which gives erroneous data), but the query does not work with INTERSECT.

Is there a query workaround for this?

Markus Fischer

Hello Hometowncc

I guess you will have to chop down your WHERE clause if you want to still have an optimizable query. Note that the individual queries do *not* work. The first would not return 1/2006, although I bet you think it should.

Select the_id from table1
WHERE ss_year = 2005 And ss_month >= 5
OR ss_year = 2006 And ss_month <= 6

If you choose a simple query over a fast query, you can of course go:

Select the_id From table1
Where ss_year*100 + ss_month Between 200505 And 200606

But a criteria based on a calculation is always quite slow...

Cheers!
(°v°)

SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

lol yeah I forgot to remove the > and <.
Ahh copy paste - my old nemesis. :-)

Leigh Purvis

Glad you liked it Hometowncc - but I'd have to say that what Markus had to say (both before and after me) is pretty pivotal to the overall solution.

Hometowncc

ASKER

Thanks to harfang and lpurvis.
I'm new to this exchange, and I goofed the points. I meant to give LPurvis assist points and harfang the Accept flag. How do I give points for an assist, and how do I fix these points for you guys? I thought after I clicked Accept, I would have a chance to give assists points.

Leigh Purvis

Post in Support (top right link) and ask for the question to be re-opened.
Then there's some option somewhere for spitting the points.

(That's really helpful huh?)
I think it's just above the Comment posting field? Or next to submit or something.
<Markus Help Pls>

Markus Fischer

LOL. This happens often. In fact I did the same mistake when closing my first question...

You can post a short note in http:/Community_Support/ with for example:

Please reopen question

Please reopen {http:/Q_21877116.html} so that I can split points.
Thanks

Once the question is reopened, the link labled "Split Points" is where Leigh thinks it is: right above the comment box.

Cheers!
(°v°)