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

Kamis, 27 September 2012

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