S
Susan Philip
Hi all,
I have an access 2000 table with the following fields :
ProviderID
ProviderOffID
ProviderSrvHoldCD
ProviderCompCD
And below is the sample data in the table :
ProviderID ProviderOffID ProviderSrvHoldCD ProviderCompCD
------------- ----------------- --------------------------
-----------------------
0000001 000 3N **
0000001 000 3N **
0000002 000 2P **
0000002 000 2P **
0000002 000 2P **
0000002 000 2P 01
0000002 000 3N **
0000002 000 3N **
0000003 000 2P **
0000003 000 2P **
0000003 000 2P **
0000003 000 2P 01
0000003 000 3N **
0000003 000 3N **
0000004 000 2P **
0000004 000 2P **
0000004 000 2P **
0000004 000 2P 01
0000004 000 3N **
0000004 000 3N **
0000005 000 2P **
0000005 000 2P **
0000005 000 2P **
0000005 000 2P 01
0000005 000 3N **
0000005 000 3N **
0000006 000 2P **
0000006 000 2P **
0000006 000 2P **
0000006 000 2P 01
The above is just a sample data ...Actualy the table has thousands of
records
what i want to do is, get 10 different ProviderID's for each
ProviderSrvHoldCD(prefer this to be in excel spreadsheet)
I have tried the following query
SELECT DISTINCT BPRS_ALL.ProviderID, BPRS_ALL.ProviderSrvHoldCD,
BPRS_ALL.ProviderOffID, BPRS_ALL.ProviderCompCD
FROM BPRS_ALL
WHERE BPRS_ALL.ProviderSrvHoldCD='2P' OR
BPRS_ALL.ProviderSrvHoldCD='3N'
ORDER BY BPRS_ALL.ProviderSrvHoldCD, BPRS_ALL.ProviderID;
and got the following:
ProviderID ProviderSrvHoldCD ProviderOffID ProviderCompCD
0000002 2P 000
**
0000002 2P 000
01
0000003 2P 000
**
0000003 2P 000
01
0000004 2P 000
**
0000004 2P 000
01
0000005 2P 000
**
0000005 2P 000
01
0000006 2P 000
**
0000006 2P 000
01
0000001 3N 000
**
0000002 3N 000
**
0000003 3N 000
**
0000004 3N 000
**
0000005 3N 000
**
In each ProviderSrvHoldCD group I only want to appear each ProviderID
once and if there are more than 10 different ProviderID in each
ProviderSrvHoldCD, i want to limit them to the first 10 ProviderID's
thanks for any help
Susan Philip
I have an access 2000 table with the following fields :
ProviderID
ProviderOffID
ProviderSrvHoldCD
ProviderCompCD
And below is the sample data in the table :
ProviderID ProviderOffID ProviderSrvHoldCD ProviderCompCD
------------- ----------------- --------------------------
-----------------------
0000001 000 3N **
0000001 000 3N **
0000002 000 2P **
0000002 000 2P **
0000002 000 2P **
0000002 000 2P 01
0000002 000 3N **
0000002 000 3N **
0000003 000 2P **
0000003 000 2P **
0000003 000 2P **
0000003 000 2P 01
0000003 000 3N **
0000003 000 3N **
0000004 000 2P **
0000004 000 2P **
0000004 000 2P **
0000004 000 2P 01
0000004 000 3N **
0000004 000 3N **
0000005 000 2P **
0000005 000 2P **
0000005 000 2P **
0000005 000 2P 01
0000005 000 3N **
0000005 000 3N **
0000006 000 2P **
0000006 000 2P **
0000006 000 2P **
0000006 000 2P 01
The above is just a sample data ...Actualy the table has thousands of
records
what i want to do is, get 10 different ProviderID's for each
ProviderSrvHoldCD(prefer this to be in excel spreadsheet)
I have tried the following query
SELECT DISTINCT BPRS_ALL.ProviderID, BPRS_ALL.ProviderSrvHoldCD,
BPRS_ALL.ProviderOffID, BPRS_ALL.ProviderCompCD
FROM BPRS_ALL
WHERE BPRS_ALL.ProviderSrvHoldCD='2P' OR
BPRS_ALL.ProviderSrvHoldCD='3N'
ORDER BY BPRS_ALL.ProviderSrvHoldCD, BPRS_ALL.ProviderID;
and got the following:
ProviderID ProviderSrvHoldCD ProviderOffID ProviderCompCD
0000002 2P 000
**
0000002 2P 000
01
0000003 2P 000
**
0000003 2P 000
01
0000004 2P 000
**
0000004 2P 000
01
0000005 2P 000
**
0000005 2P 000
01
0000006 2P 000
**
0000006 2P 000
01
0000001 3N 000
**
0000002 3N 000
**
0000003 3N 000
**
0000004 3N 000
**
0000005 3N 000
**
In each ProviderSrvHoldCD group I only want to appear each ProviderID
once and if there are more than 10 different ProviderID in each
ProviderSrvHoldCD, i want to limit them to the first 10 ProviderID's
thanks for any help
Susan Philip