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
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
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar