How to group records in a table

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
 
S

strive4peace

Hi Susan,

try this:

make a report based on your query

group by --> ProviderSrvHoldCD
(from the menu --> View, sorting & Grouping)
Grou Header --> yes

put ProviderSrvHoldCD in the group header for that section

in the Detail section, put ProviderCompCD

and then, to limit the records to 10...

in the detail section, make an textbox control:

Name --> countDetail
controlSource --> = 1
Runningsum --> OverGroup

in the detail Onformat event...

me.detail.visible = _
iif(me.countDetail <=10, true,false)

if you need more help, just ask!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
S

Susan Philip

Hi ,

But I want to do this in a query and export the query to an excel
spreadsheet...Is there a way to do this ?

Thanks
Susan
 
S

strive4peace

Hi Susan,

you can also export a report to an Excel spreadsheet... make
sure the TOP property of your controls is the same...

you can specify TOPVALUES for a query, but it applies to the
whole query, not just a group of records

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
J

John Vinson

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;
Click to expand...

You'll need a Subquery to get multiple groups:

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'
AND BPRS_ALL.ProviderID IN
(SELECT TOP 10 ProviderID
FROM BPRS_ALL AS X
WHERE X.ProviderSrvHoldCD = BPRS_ALL.ProviderSrvHoldCD)
ORDER BY BPRS_ALL.ProviderSrvHoldCD, BPRS_ALL.ProviderID;


John W. Vinson[MVP]
 
S

Susan Philip

Hi Crystal ,
Sorry to bother u again....

What I am currently getting is in the following format :

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

But i like the report as follows:

ProviderID ProviderSrvHoldCD ProviderOffID ProviderCompCD
0000002 2P 000 **
0000003 2P 000 **
0000004 2P 000 **
0000005 2P 000 **
0000006 2P 000 **
0000001 3N 000 **
0000002 3N 000 **
0000003 3N 000 **
0000004 3N 000 **
0000005 3N 000 **

ie, Within each ProviderSrvHoldCD, i would like distinct ProviderID's

Thanks,
Susan
 
G

Gary Walter

PMFBI

it sure sounds like "10" was some kind
of red herring and you simply wanted
a totals query to reduce down
to a group of
ProviderID/ProviderSrvHoldCD
or
ProviderID/ProviderSrvHoldCD/ProviderOffID?

SELECT
B.ProviderID,
B.ProviderSrvHoldCD,
B.ProviderOffID,
Min(B.ProviderCompCD) As mProviderCompCD
FROM
BPRS_ALL As B
WHERE
B.ProviderSrvHoldCD IN ('2P','3N')
GROUP BY
B.ProviderID,
B.ProviderSrvHoldCD,
B.ProviderOffID
ORDER BY
B.ProviderSrvHoldCD,
B.ProviderID;

if the above works for you,
save it (say as "qryGrpProvider")

then, you can use it to
*create a new Excel table*
(say "NewTable" in new C:\Provider.xls)
by

SELECT

q.ProviderID,
q.ProviderSrvHoldCD,
q.ProviderOffID,
q.mProviderCompCD As ProviderCompCD
INTO
[Excel 8.0;database=C:\Provider.xls].NewTable
FROM
qryGrpProvider As q
ORDER BY
q.ProviderSrvHoldCD,
q.ProviderID;

or
(if C:\Provider.xls already exists)
*create a new sheet *
(named "NewSheet")
by

SELECT

q.ProviderID,
q.ProviderSrvHoldCD,
q.ProviderOffID,
q.mProviderCompCD As ProviderCompCD
INTO
[Excel 8.0;database=C:\Provider.xls].NewSheet
FROM
qryGrpProvider As q
ORDER BY
q.ProviderSrvHoldCD,
q.ProviderID;

I believe the above works just
like a regular Select ..Into with
tables, i.e., when the table already
exists, it will be overwritten.

you no longer can append data to
an existing sheet.....sigh

I am not an Excel expert, but I have
used similar to above in previous
projects.....
its possible I am missing some
"subtlety" in your situation...

apologies again for butting in
(especially if I misunderstood)
 
P

priyapalmfield

Hi all,
I got that into work ...thanks a lot !!

Gary said:
PMFBI

it sure sounds like "10" was some kind
of red herring and you simply wanted
a totals query to reduce down
to a group of
ProviderID/ProviderSrvHoldCD
or
ProviderID/ProviderSrvHoldCD/ProviderOffID?

SELECT
B.ProviderID,
B.ProviderSrvHoldCD,
B.ProviderOffID,
Min(B.ProviderCompCD) As mProviderCompCD
FROM
BPRS_ALL As B
WHERE
B.ProviderSrvHoldCD IN ('2P','3N')
GROUP BY
B.ProviderID,
B.ProviderSrvHoldCD,
B.ProviderOffID
ORDER BY
B.ProviderSrvHoldCD,
B.ProviderID;

if the above works for you,
save it (say as "qryGrpProvider")

then, you can use it to
*create a new Excel table*
(say "NewTable" in new C:\Provider.xls)
by

SELECT

q.ProviderID,
q.ProviderSrvHoldCD,
q.ProviderOffID,
q.mProviderCompCD As ProviderCompCD
INTO
[Excel 8.0;database=C:\Provider.xls].NewTable
FROM
qryGrpProvider As q
ORDER BY
q.ProviderSrvHoldCD,
q.ProviderID;

or
(if C:\Provider.xls already exists)
*create a new sheet *
(named "NewSheet")
by

SELECT

q.ProviderID,
q.ProviderSrvHoldCD,
q.ProviderOffID,
q.mProviderCompCD As ProviderCompCD
INTO
[Excel 8.0;database=C:\Provider.xls].NewSheet
FROM
qryGrpProvider As q
ORDER BY
q.ProviderSrvHoldCD,
q.ProviderID;

I believe the above works just
like a regular Select ..Into with
tables, i.e., when the table already
exists, it will be overwritten.

you no longer can append data to
an existing sheet.....sigh

I am not an Excel expert, but I have
used similar to above in previous
projects.....
its possible I am missing some
"subtlety" in your situation...

apologies again for butting in
(especially if I misunderstood)

Susan Philip said:
Hi Crystal ,
Sorry to bother u again....

What I am currently getting is in the following format :

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

But i like the report as follows:

ProviderID ProviderSrvHoldCD ProviderOffID ProviderCompCD
0000002 2P 000 **
0000003 2P 000 **
0000004 2P 000 **
0000005 2P 000 **
0000006 2P 000 **
0000001 3N 000 **
0000002 3N 000 **
0000003 3N 000 **
0000004 3N 000 **
0000005 3N 000 **

ie, Within each ProviderSrvHoldCD, i would like distinct ProviderID's

Thanks,
Susan
Click to expand...
Click to expand...
 
S

strive4peace

Hi Gary,

good observation, thanks for your input!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Gary said:
PMFBI

it sure sounds like "10" was some kind
of red herring and you simply wanted
a totals query to reduce down
to a group of
ProviderID/ProviderSrvHoldCD
or
ProviderID/ProviderSrvHoldCD/ProviderOffID?

SELECT
B.ProviderID,
B.ProviderSrvHoldCD,
B.ProviderOffID,
Min(B.ProviderCompCD) As mProviderCompCD
FROM
BPRS_ALL As B
WHERE
B.ProviderSrvHoldCD IN ('2P','3N')
GROUP BY
B.ProviderID,
B.ProviderSrvHoldCD,
B.ProviderOffID
ORDER BY
B.ProviderSrvHoldCD,
B.ProviderID;

if the above works for you,
save it (say as "qryGrpProvider")

then, you can use it to
*create a new Excel table*
(say "NewTable" in new C:\Provider.xls)
by

SELECT

q.ProviderID,
q.ProviderSrvHoldCD,
q.ProviderOffID,
q.mProviderCompCD As ProviderCompCD
INTO
[Excel 8.0;database=C:\Provider.xls].NewTable
FROM
qryGrpProvider As q
ORDER BY
q.ProviderSrvHoldCD,
q.ProviderID;

or
(if C:\Provider.xls already exists)
*create a new sheet *
(named "NewSheet")
by

SELECT

q.ProviderID,
q.ProviderSrvHoldCD,
q.ProviderOffID,
q.mProviderCompCD As ProviderCompCD
INTO
[Excel 8.0;database=C:\Provider.xls].NewSheet
FROM
qryGrpProvider As q
ORDER BY
q.ProviderSrvHoldCD,
q.ProviderID;

I believe the above works just
like a regular Select ..Into with
tables, i.e., when the table already
exists, it will be overwritten.

you no longer can append data to
an existing sheet.....sigh

I am not an Excel expert, but I have
used similar to above in previous
projects.....
its possible I am missing some
"subtlety" in your situation...

apologies again for butting in
(especially if I misunderstood)

Susan Philip said:
Hi Crystal ,
Sorry to bother u again....

What I am currently getting is in the following format :

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

But i like the report as follows:

ProviderID ProviderSrvHoldCD ProviderOffID ProviderCompCD
0000002 2P 000 **
0000003 2P 000 **
0000004 2P 000 **
0000005 2P 000 **
0000006 2P 000 **
0000001 3N 000 **
0000002 3N 000 **
0000003 3N 000 **
0000004 3N 000 **
0000005 3N 000 **

ie, Within each ProviderSrvHoldCD, i would like distinct ProviderID's

Thanks,
Susan
Click to expand...
Click to expand...
 
Top