[MS_AccessPros] Re: SQL, pulling specific rows from a table

 

Hello, thank you very much. I have just tried the query, that removes BANK NORTHEAST, which is the bank that has 4 rows of "SVB", however, it still pulled all other banks included the Type not = "SVB".

ID Bname NewDate OldDate Type
HK260 HARBOR BANKING COMPANY 20100331 19691231 SVB
BB510 BAYBANK and TRUST 20110425 19960202 NAB
BB510 BAYBANK and TRUST 20111231 19960202 SVB
NT190 NORFOLK TRUST 20110102 19960202 TRC
NT190 NORFOLK TRUST 20120130 19960202 SVB
BT500 BELKNAP BANK & TRUST 20080520 19880520 BBT
BT500 BELKNAP BANK & TRUST 20100618 19990618 NAB
BT500 BELKNAP BANK & TRUST 20120618 19990618 SVB

I have just throw around with the query again, and I added another "AND Type = "SVB" after the sub query, right after the HAVING clause, that seems to be working, I am wondering if this is the right way to code? or there's another better method.

SELECT * FROM [Bks]
WHERE ID not IN (SELECT ID FROM [Bks] WHERE [Type] = "SVB"
GROUP BY ID
HAVING COUNT(*) >=2) AND Type = "SVB"

ID Bname NewDate OldDate Type
HK260 HARBOR BANKING COMPANY 20100331 19691231 SVB
BB510 BAYBANK and TRUST 20111231 19960202 SVB
NT190 NORFOLK TRUST 20120130 19960202 SVB
BT500 BELKNAP BANK & TRUST 20120618 19990618 SVB

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> Try something like:
> SELECT *
> FROM [YourTable]
> WHERE ID NOT IN (SELECT ID FROM [YourTable] WHERE [Type] = "SVB" GROUP BY ID HAVING COUNT(*) >=2)
>
> Duane Hookom
> MS Access MVP
>
>
> > To: MS_Access_Professionals@yahoogroups.com
> > From: saigonf7q5@...
> > Date: Thu, 27 Sep 2012 14:38:02 +0000
> > Subject: [MS_AccessPros] Re: SQL, pulling specific rows from a table
> >
> >
> >
> > Hello there good morning
> >
> > I've still been struggling with the query. Let me restate my trouble and hopefully it is a little clearer this time.
> > My goal is to drop the institutions that have Type = "SVB" that appear more than 1 time in that institution, but pull the institutions that have "SVB" appears only 1.
> > For example, BANK NORTHEAST should be dropped, because SVB occurs more than 1 time, but BAYBANK TRUST and BELKNAP BANK & TRUST should be pulled, because there's only 1 row of SVB, although these 2 banks have more than 1 rows.
> > Sample table (this table is a little different than the one in my last post, I should have include the TYPE column last time. The ID is text type.)
> >
> > ID BName NewDate OldDate Type
> > BK217 BANK NORTHEAST 20000331 19800331 SVB
> > BK217 BANK NORTHEAST 20041231 19840123 SVB
> > BK217 BANK NORTHEAST 20080630 19980924 SVB
> > BK217 BANK NORTHEAST 20120531 20000531 SVB
> > HK260 HARBOR BANKING COMPANY 20100331 19691231 SVB
> > BB510 BAYBANK TRUST 20110425 19960202 NAB
> > BB510 BAYBANK TRUST 20111231 19960202 SVB
> > NT190 NORFOLK TRUST 20110102 19960202 TRC
> > NT190 NORFOLK TRUST 20120130 19960202 SVB
> > BT300 BAYSIDE TRUST COMPANY 20090202 19960202 SVB
> > BT300 BAYSIDE TRUST COMPANY 20100202 19960202 SVB
> > BT500 BELKNAP BANK & TRUST 20080520 19880520 BBT
> > BT500 BELKNAP BANK & TRUST 20100618 19990618 NAB
> > BT500 BELKNAP BANK & TRUST 20120618 19990618 SVB
> >
> > Expecting output
> >
> > ID BName NewDate OldDate TYPE
> > HK260 HARBOR BANKING COMPANY 20100331 19691231 SVB
> > BB510 BAYBANK and TRUST 20111231 19960202 SVB
> > NT190 NORFOLK TRUST 20120130 19960202 SVB
> > BT500 BELKNAP BANK & TRUST 20120618 19990618 SVB
> >
> > Thank you very much and appreciate your help.
> >
> > Phucon
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@> wrote:
> > >
> > > Hi Phucon,
> > >
> > > I assumed that BkID is an AutoNumber so I make it
> > >
> > > SELECT Count(ImportDb1.BkID) AS CountOfBkID, ImportDb1.BkName, Max(ImportDb1.NewDate) AS MaxOfNewDate
> > > FROM ImportDb1
> > > GROUP BY ImportDb1.BkName
> > > HAVING (((Max(ImportDb1.NewDate)) Between 20100101 And 20120603));
> > >
> > > to get your desired result.
> > >
> > > Hope that helps.
> > >
> > > Regards, Clive.
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > >
> > > > Phucon
> > > >
> > > > If you want only banks that have only one record each then do it this way:
> > > > SELECT BkID, BkName, NewDate, OldDate
> > > > FROM tblInstitution
> > > > WHERE BkID In (
> > > > SELECT BkID
> > > > FROM tblInstitution
> > > > GROUP BY BkID
> > > > HAVING COUNT(BkID)=1)
> > > >
> > > > Regards,
> > > > Bill Mosca, Founder - MS_Access_Professionals
> > > > http://www.thatlldoit.com
> > > > Microsoft Office Access MVP
> > > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> > > > My nothing-to-do-with-Access blog
> > > > http://wrmosca.wordpress.com
> > > >
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "saigonf7q5" <saigonf7q5@> wrote:
> > > > >
> > > > > I have a table that contains a list of financial institutions. Some of the institutions have more than 1 row due to information change.
> > > > >
> > > > > BkID BkName NewDate OldDate
> > > > > BK217 BANK NORTHEAST 20000331 19800331
> > > > > BK217 BANK NORTHEAST 20041231 19840123
> > > > > BK217 BANK NORTHEAST 20080630 19980924
> > > > > BK217 BANK NORTHEAST 20120531 20000531
> > > > > HK260 HARBOR BANKING COMPANY 20100331 19691231
> > > > > BB510 BAYBANK and TRUST 20110425 19960202
> > > > > NT190 NORFOLK TRUST 20120102 19960202
> > > > > BT300 BAYSIDE TRUST COMPANY 20090202 19960202
> > > > > BT500 BELKNAP BANK & TRUST 20080520 19880520
> > > > > BT500 BELKNAP BANK & TRUST 20100618 19990618
> > > > > BD602 BOSTON DEPOSIT COMPANY 20080607 19940407
> > > > > NB310 NORTH BRISTOL BK & TR 20120310 19880310
> > > > >
> > > > >
> > > > > My task is to pull out the institutions that have only 1 occurrence (exclude the repeated institutions), and include only the rows that have NewDate Between 20100101 and 20120603.
> > > > >
> > > > > How do I achieve this kind of task? Use Sub queries?
> > > > >
> > > > > This is what I tried, but I think this is not a correct way ...
> > > > >
> > > > > SELECT BkID, BkName, NewDate, OldDate
> > > > > FROM tblInstitution
> > > > > WHERE Bk ID Not In (Bk217, BT500) And NewDate Between 20100101 And 2012630
> > > > >
> > > > > Phucon
> > > > >
> > > >
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (6)

Recent Activity:

• New Members 4
• New Files 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___