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@yahoo.com
> 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]
Tidak ada komentar:
Posting Komentar