Using multiple criteria for the same field in a query

[shauntwo]

Hi,

I'm trying to write my own pass-through queries in Access to query our Oracle database, so I'm trying to learn the SQL behind many of the things I've been doing, or would like to do, with our information. One very big question of mine is this - how do you utilize different criteria for the same field in a query? As a brief example, suppose you wanted to illustrate sales between January and March for both year 2002 and 2001, using a "bill date" field. Normally, I'd run such a query in Access twice, changing the respective 'Between m/d/y and m/d/y' for each year in question. However, I suspect that SQL would facilitate such multiple criteria in one query.

Any thoughts? Your help would be greatly appreciated.

Shaun

 

[rcurva]

You can also try something like this;

SELECT *
FROM bills
WHERE (bill_date BETWEEN date1 AND date2 OR
bill_date BETWEEN date3 AND date4 );

Where;

date1 and date2 -- Jan to Mar of 2001
date3 and date4 -- Jan to Mar of 2002

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[shauntwo]

Robbie,

That looks like I'll just get a single criteria field where the date range can be either one of those. What I'd actually like to see are two fields in the dynaset, one for each date range, where the sales are given. This in essence means that I'm specifying different criteria for two instances of the same field in one query, which isn't normally possible in Access, to my knowledge.

Thanks
Shaun

 

[rcurva]

Is it possible for you give an illustrated example? Obviously, my idea did not suit yours. Are we talking about SQL in Access here? Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[shauntwo]

Robbie,

Well, let me give you an example and perhaps this will better clarify what I'm asking: I'm running a report which reflects basic information about a customer in the beginning of the record (e.g. let's say name, address, and phone). However, for each customer, I want the last two fields to reflect the sum of sales for the first quarter of last year and this year, respectively. Like such...

CUST ADDRESS PHONE Q1 2002 Q1 2001
Joe 123 Easy xxx-xxxx $2,392 $3,4938

I've tried building my criteria for each SumOfSales field by using different variations of 'Bill Date >= 1/1/2001 and Bill Date <= 5/1/2002', and likewise for 2002, but to no avail. This has always seemed to be an issue with various situations where I would like to present different criteria slices of the same field, but ultimately resort to running the query multiple times with the different criteria. And yes, if this can be done with a pass-through to directly query Oracle, that would be ideal since this is concerning lots of data.

Thanks for your help Robbie.

 

[rcurva]

I understand you now. Here's an attempt to your requirement;

SELECT a.cust, a.address, a.phone, a.Q1_2001, b.Q1_2002
(SELECT cust, address, phone, SUM(amount) &quot;Q1_2001&quot;
FROM bills
WHERE bill_date BETWEEN date1 AND date2) a,
(SELECT cust, address, phone, SUM(amount) &quot;Q1_2002&quot;
FROM bills
WHERE bill_date BETWEEN date3 AND date4) b
WHERE a.cust = b.cust;

assuming CUST field is unique for the table.

Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[blom0344]

Hello Shaun,

Jet SQL that Access uses is a bit restrictive compared with Ansi SQL used by for instance ORACLE.

However of the Set operators available you can use the UNION one.

The general structure would be:

Select a,b,c from X
Where ............. (relating to 2001)
UNION
Select a,b,c from X
Where ------------- (relating to 2002)

The beauty, is that the a,b,c set does NOT have to be the exact corresponding fields, which means you can add field d to c , provided that type is the same. (If you ever want to do such a thing in the first place) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com