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
>
Tidak ada komentar:
Posting Komentar