Thank you very much Bob & Mr. Viescas.
You¡¦re right Mr. Viescas, I was building the query by code. As a matter of fact, I have been modifying someone else code. The purpose of this query is to filter the TopHolders in a specific District from the tblAttributes table. The eID is the key field. The tblAttributes table contains only Attribute info like names and addresses... no flag indicates which record is a TopHolder, but the tblTop (Top Holder table) of the sub query does. The tID is a key field of the tblTop. This is how the eID related to the tID. Hope you understand what I¡¦ve just describedƒº
Now both queries (the one from Bob and Mr. Viescar) are working very good, filtering out only the needed district.
I noted that, rewrite the query as Bob suggested, there were 28 rows more than Mr. Viescas¡¦. The query output (number of rows) will be the same if the ¡§AND (20131231 BETWEEN AA.DT_START AND AA.DT_END¡¨ was placed back into the outer query.
One other thing I noted that was, either placing the ¡§ T.eDistrict = 11¡¨ inside or outside the sub query, it does not change the query output.
The good thing is, however, it filters correctly, pulling the right district.
--- In MS_Access_Professionals@yahoogroups.com, John Viescas wrote:
>
> 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
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar