ACCESS FEED: RE: [MS_AccessPros] Re: Combine 2 queries into 1 single query

Minggu, 03 Februari 2013

RE: [MS_AccessPros] Re: Combine 2 queries into 1 single query

Bob & Phucon-

I missed the fact that there was a filter on eDistrict in the first example
(it was late at night - what can I say?). I see no reason why it doesn't
work as written unless you've run into an Access bug. The AND criteria
should force it to return only District 11 rows.

Phucon, are you building this query in code? You clearly didn't use the
query designer because it's missing all the parens that Access normally
inserts. Try adding parens to your original query:

SELECT A.DISTINCT A.eID, A.eNAME, A.eDate, A.eCITY, A.eSTATE,
A.eDistrict
FROM tblATTRIBUTES AS A
WHERE (A.eID
In (SELECT T.tID FROM tblTOP AS T
WHERE 20120131 BETWEEN T.STARTDate AND T.ENDDate))
AND (20120131 BETWEEN A.STARTdate AND A.ENDDate)
AND (A.eDistrict = 11)
AND (A.IND1 = 1)
AND ((A.eIND2 = 2) OR (A.eIND3 = 2))
ORDER BY A.eDistrict, A.eSTATE;

I also just noticed that you're referencing a field "tID" in the subquery -
what is that and how is it related to eID? The test for dates seems
redundant until you realize you're looking at a different field.

And what is the data type of STARTDate and ENDDate? If Date/Time, you
should be doing:

SELECT A.DISTINCT A.eID, A.eNAME, A.eDate, A.eCITY, A.eSTATE,
A.eDistrict
FROM tblATTRIBUTES AS A
WHERE (A.eID
In (SELECT T.tID FROM tblTOP AS T
WHERE #2012-01-31# BETWEEN T.STARTDate AND T.ENDDate))
AND (#2012-01-31# BETWEEN A.STARTdate AND A.ENDDate)
AND (A.eDistrict = 11)
AND (A.IND1 = 1)
AND ((A.eIND2 = 2) OR (A.eIND3 = 2))
ORDER BY A.eDistrict, A.eSTATE;

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bob
Sent: Monday, February 04, 2013 4:55 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Combine 2 queries into 1 single query

Hello Phucon,

Is it possible to add "AND T.eDistrict = 11 to the embedded SELECT
statement, remove two redundant AND statements and place parentheses around
the OR statement? - I would be curious to see if these changes keep the
selection set to just District 11 rows (see below).

Bob

Suggested rewrite: of query:

SELECT A.DISTINCT A.eID,
A.eNAME, A.eDate,
A.eCITY,
A.eSTATE,
A.eDistrict
FROM tblATTRIBUTES AS A
WHERE A.eID In
(SELECT T.tID FROM tblTOP AS T WHERE 20120131 BETWEEN T.STARTDate
AND T.ENDDate
AND T.eDistrict = 11)
AND A.IND1 = 1
AND (A.eIND2 = 2 OR A.eIND3 = 2)
ORDER BY A.eDistrict, A.eSTATE;

--- In MS_Access_Professionals@yahoogroups.com, "saigonf7q5" wrote:
>
>
>
>
>
>
> Hello Bob, Mr. Viescas
>
> I apologize, I just realized there should be 1 more indicator that I
forgot to include in the first query. Which is "OR A.eIND3 = 2" Is this
where causing the trouble?
> Sorry for the confusion.
> Phucon
>
> Revised query
>
> SELECT A.DISTINCT A.eID,
> A.eNAME, A.eDate,
> A.eCITY,
> A.eSTATE,
> A.eDistrict
> FROM tblATTRIBUTES AS A
> WHERE A.eID In
> (SELECT T.tID FROM tblTOP AS T WHERE 20120131 BETWEEN T.STARTDate AND
> T.ENDDate) AND 20120131 BETWEEN A.STARTdate AND A.ENDDate AND
> A.eDistrict = 11 AND A.IND1 = 1 AND A.eIND2 = 2 OR A.eIND3 = 2 ORDER
> BY A.eDistrict, A.eSTATE;
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bob" wrote:
> >
> > And of course drop the original A.eDistrict = 11 part!
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Bob" wrote:
> > >
> > > Hello Phucon,
> > >
> > > The problem may be with the "WHERE A.eID In..." statement. I suspect
this is where all of the districts are coming over.
> > >
> > > Is it possible to expand the SELECT to include district 11 and also
drop the redundant select for the Start and End Dates? (see below).
> > >
> > > Bob
> > >
> > >
> > > SELECT
> > > A.DISTINCT A.eID,
> > > A.eNAME,
> > > A.eDate,
> > > A.eCITY,
> > > A.eSTATE,
> > > A.eDistrict
> > > FROM tblATTRIBUTES AS A
> > > WHERE
> > > A.eID In (SELECT T.tID FROM tblTOP AS T WHERE 20120131 BETWEEN
> > > T.STARTDate AND T.ENDDate
> > > -- add T.eDistrict = 11)
> > > -- AND 20120131 BETWEEN A.STARTdate AND A.ENDDate --Drop
> > > -- AND A.eDistrict = 11 -- Drop
> > > AND A.IND1 = 1
> > > AND A.eIND2 = 2
> > >
> > > ORDER BY A.eDistrict,
> > > A.eSTATE;
> > >
> > >
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "saigonf7q5" wrote:
> > > >
> > > > My task is to pull only the records in District11. I thought the
query Step1 below would do that. However it pulled all Districts instead. I
then have to add a second query(Step2)to filter it to get the correct
result.
> > > >
> > > > I guess the 2 steps can be combined into 1 single query by adding
the Group By and Having clause to the first query. If that can be done, how
the query can be rewritten? (I tried, but still having trouble to get it
right).
> > > >
> > > > Phucon
> > > >
> > > > Step1
> > > >
> > > > SELECT A.DISTINCT A.eID, A.eNAME, A.eDate, A.eCITY, A.eSTATE,
> > > > A.eDistrict FROM tblATTRIBUTES AS A WHERE A.eID In (SELECT T.tID
> > > > FROM tblTOP AS T WHERE 20120131 BETWEEN T.STARTDate AND
> > > > T.ENDDate) AND 20120131 BETWEEN A.STARTdate AND A.ENDDate AND
> > > > A.eDistrict = 11 AND A.IND1 = 1 AND A.eIND2 = 2 ORDER BY
> > > > A.eDistrict, A.eSTATE;
> > > >
> > > > Step2
> > > >
> > > > SELECT eID, eNAME, eDate, CITY, eSTATE, eDistrict FROM Step1
> > > > WHERE eDistrict =11
> > > >
> > >
> >
>

------------------------------------

Yahoo! Groups Links