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

Jumat, 21 September 2012

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

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