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

Minggu, 03 Februari 2013

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

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