Help me with this query.

[Pablo]

Hello, there,
I have two tables:
tblPCP: it has 108 doctors name and affiliation number.
tblMembers: it has 7800 members name and PCP affiliation number.
Let's say tblPCP has two fields: PCPName, PCPID
tblMembers has two fields: MemberName, PCPID

Now I want to get 10 members associated with each PCP. Say DoctorA has
seen 98 members, but I only want to get 10 sample members; DoctorB has
seen 456 members, and I only want 10 sampel members...
The result should have 108*10=1080 records.
Does anyone know how to get the result in ONE query?
Thanks in advance.
Pablo

[MLH]

Because I am seeking the help of this forum, I feel obliged to try
to lend a hand. I don't think a simple suggestion relating to using
TopValues property setting is gonna do it here. I think the difficulty
of what you're trying to accomplish warrants the skills of someone
like John Winterbottom. John's an absolute whiz at this stuff.

I will offer this strategy... sometimes, you can develop a series of
one or more queries (using the QBE design grid tool) that meet
your objective, then, displaying each of them in SQL view, you
can cut 'n paste the SQL for each of them into a single query &
accomplish what you want. I don't know if that will work for you.
You may have already tried it. Its worth fooling around with a bit
just to see if it works for you.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx

On 10 Oct 2003 11:46:24 -0700, pa*****@hispavista.com (Pablo) wrote:

Hello, there,
I have two tables:
tblPCP: it has 108 doctors name and affiliation number.
tblMembers: it has 7800 members name and PCP affiliation number.
Let's say tblPCP has two fields: PCPName, PCPID
tblMembers has two fields: MemberName, PCPID

Now I want to get 10 members associated with each PCP. Say DoctorA has
seen 98 members, but I only want to get 10 sample members; DoctorB has
seen 456 members, and I only want 10 sampel members...
The result should have 108*10=1080 records.
Does anyone know how to get the result in ONE query?
Thanks in advance.
Pablo

[Ross Presser]

pa*****@hispavista.com (Pablo) wrote in news:e9**************************@posting.google.c om:

Hello, there,
I have two tables:
tblPCP: it has 108 doctors name and affiliation number.
tblMembers: it has 7800 members name and PCP affiliation number.
Let's say tblPCP has two fields: PCPName, PCPID
tblMembers has two fields: MemberName, PCPID

Now I want to get 10 members associated with each PCP. Say DoctorA has
seen 98 members, but I only want to get 10 sample members; DoctorB has
seen 456 members, and I only want 10 sampel members...
The result should have 108*10=1080 records.
Does anyone know how to get the result in ONE query?
Thanks in advance.
Pablo

Assuming you don't mind that the members be the first alphabetically in the list:

SELECT aa.PCPID, aa.PCPName, count(bb.PCPID) as SampleNum
FROM tblPCP as aa, tblPCP as bb
WHERE aa.PCPID = bb.PCPID and aa.PCPName >= bb.PCPName
GROUP BY aa.PCPID, aa.PCPName
HAVING count(bb.PCPID) <= 10

On a member table with 7800 rows and with indexes on PCPID and PCPName, this shouldn't take more
than 5 sec to run.

--
Ross Presser -- rpresser AT imtek DOT com
.... seeking a new quote ...

[Pablo]

Ross Presser <rp******@NOSPAM.imtek.com.invalid> wrote in message news:<Xn**********************@129.250.170.82>...

pa*****@hispavista.com (Pablo) wrote in news:e9**************************@posting.google.c om:

Hello, there,
I have two tables:
tblPCP: it has 108 doctors name and affiliation number.
tblMembers: it has 7800 members name and PCP affiliation number.
Let's say tblPCP has two fields: PCPName, PCPID
tblMembers has two fields: MemberName, PCPID

Now I want to get 10 members associated with each PCP. Say DoctorA has
seen 98 members, but I only want to get 10 sample members; DoctorB has
seen 456 members, and I only want 10 sampel members...
The result should have 108*10=1080 records.
Does anyone know how to get the result in ONE query?
Thanks in advance.
Pablo

Assuming you don't mind that the members be the first alphabetically in the list:

SELECT aa.PCPID, aa.PCPName, count(bb.PCPID) as SampleNum
FROM tblPCP as aa, tblPCP as bb
WHERE aa.PCPID = bb.PCPID and aa.PCPName >= bb.PCPName
GROUP BY aa.PCPID, aa.PCPName
HAVING count(bb.PCPID) <= 10

On a member table with 7800 rows and with indexes on PCPID and PCPName, this shouldn't take more
than 5 sec to run.

Thanks for reply. I am thinking a join query(maybe nested join query)
between tblPCP and tblMembers would do the work. But the join between
tblPCP itself would return the tblPCP itself.
BTW, I want to correct myself: tblMembers has around 78000 members
instead of 7800 members.
Thanks again for all of you.
Pablo

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd try something like this - I haven't tested this:

SELECT P.PCPName, M.MemberName
FROM tblPCP As P INNER JOIN tblMembers As M
ON P.PCPID = M.PCPID
WHERE M.MemberName In (SELECT TOP 10 MemberName FROM tblMembers WHERE
PCPID = P.PCPID)
ORDER BY 1, 2

The part that gets the 10 Members per doctor is in the WHERE clause.
It "says" "Use the first 10 Member records you find for the current
doctor ID (PCPID = P.PCPID)." If you had other criteria to determine
which Members should be choosen, it would go in the subquery's WHERE
clause:

....WHERE PCPID = P.PCPID AND DateOfBirth Between #1/1/1951# And
#12/31/1951#)

It would probably be best if you used a unique MemberID instead of the
MemberName for the main query WHERE clause - 'cuz some MemberNames may
be the same. E.g.

SELECT P.PCPName, M.MemberName
FROM tblPCP As P INNER JOIN tblMembers As M
ON P.PCPID = M.PCPID
WHERE M.MemberID In (SELECT TOP 10 MemberID FROM tblMembers WHERE
PCPID = P.PCPID)
ORDER BY 1, 2

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4rwhoechKqOuFEgEQI1mQCdHduZBRPqPahqGO1jRtZ9ac DuMTsAn0y3
a3lwlb3EWxcUbxHkt0teOiVg
=ikuw
-----END PGP SIGNATURE-----
Pablo wrote:

Ross Presser <rp******@NOSPAM.imtek.com.invalid> wrote in message news:<Xn**********************@129.250.170.82>...

pa*****@hispavista.com (Pablo) wrote in news:e9**************************@posting.google.c om:

Hello, there,
I have two tables:
tblPCP: it has 108 doctors name and affiliation number.
tblMembers: it has 7800 members name and PCP affiliation number.
Let's say tblPCP has two fields: PCPName, PCPID
tblMembers has two fields: MemberName, PCPID

Now I want to get 10 members associated with each PCP. Say DoctorA has
seen 98 members, but I only want to get 10 sample members; DoctorB has
seen 456 members, and I only want 10 sampel members...
The result should have 108*10=1080 records.
Does anyone know how to get the result in ONE query?
Thanks in advance.
Pablo

Assuming you don't mind that the members be the first alphabetically in the list:

SELECT aa.PCPID, aa.PCPName, count(bb.PCPID) as SampleNum
FROM tblPCP as aa, tblPCP as bb
WHERE aa.PCPID = bb.PCPID and aa.PCPName >= bb.PCPName
GROUP BY aa.PCPID, aa.PCPName
HAVING count(bb.PCPID) <= 10

On a member table with 7800 rows and with indexes on PCPID and PCPName, this shouldn't take more
than 5 sec to run.

Thanks for reply. I am thinking a join query(maybe nested join query)
between tblPCP and tblMembers would do the work. But the join between
tblPCP itself would return the tblPCP itself.
BTW, I want to correct myself: tblMembers has around 78000 members
instead of 7800 members.
Thanks again for all of you.
Pablo

[Pablo]

MGFoster <[e-mail]> wrote in message news:<dg*****************@newsread4.news.pas.earth link.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd try something like this - I haven't tested this:

SELECT P.PCPName, M.MemberName
FROM tblPCP As P INNER JOIN tblMembers As M
ON P.PCPID = M.PCPID
WHERE M.MemberName In (SELECT TOP 10 MemberName FROM tblMembers WHERE
PCPID = P.PCPID)
ORDER BY 1, 2

The part that gets the 10 Members per doctor is in the WHERE clause.
It "says" "Use the first 10 Member records you find for the current
doctor ID (PCPID = P.PCPID)." If you had other criteria to determine
which Members should be choosen, it would go in the subquery's WHERE
clause:

Thank you very much. It works perfectly. You saved me a lot of time.
Have a good day.
Pablo ...WHERE PCPID = P.PCPID AND DateOfBirth Between #1/1/1951# And
#12/31/1951#)

It would probably be best if you used a unique MemberID instead of the
MemberName for the main query WHERE clause - 'cuz some MemberNames may
be the same. E.g.

SELECT P.PCPName, M.MemberName
FROM tblPCP As P INNER JOIN tblMembers As M
ON P.PCPID = M.PCPID
WHERE M.MemberID In (SELECT TOP 10 MemberID FROM tblMembers WHERE
PCPID = P.PCPID)
ORDER BY 1, 2

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4rwhoechKqOuFEgEQI1mQCdHduZBRPqPahqGO1jRtZ9ac DuMTsAn0y3
a3lwlb3EWxcUbxHkt0teOiVg
=ikuw
-----END PGP SIGNATURE-----
Pablo wrote:

Ross Presser <rp******@NOSPAM.imtek.com.invalid> wrote in message news:<Xn**********************@129.250.170.82>...

pa*****@hispavista.com (Pablo) wrote in news:e9**************************@posting.google.c om:
Hello, there,
I have two tables:
tblPCP: it has 108 doctors name and affiliation number.
tblMembers: it has 7800 members name and PCP affiliation number.
Let's say tblPCP has two fields: PCPName, PCPID
tblMembers has two fields: MemberName, PCPID

Now I want to get 10 members associated with each PCP. Say DoctorA has
seen 98 members, but I only want to get 10 sample members; DoctorB has
seen 456 members, and I only want 10 sampel members...
The result should have 108*10=1080 records.
Does anyone know how to get the result in ONE query?
Thanks in advance.
Pablo

Assuming you don't mind that the members be the first alphabetically in the list:

SELECT aa.PCPID, aa.PCPName, count(bb.PCPID) as SampleNum
FROM tblPCP as aa, tblPCP as bb
WHERE aa.PCPID = bb.PCPID and aa.PCPName >= bb.PCPName
GROUP BY aa.PCPID, aa.PCPName
HAVING count(bb.PCPID) <= 10

On a member table with 7800 rows and with indexes on PCPID and PCPName, this shouldn't take more
than 5 sec to run.

Thanks for reply. I am thinking a join query(maybe nested join query)
between tblPCP and tblMembers would do the work. But the join between
tblPCP itself would return the tblPCP itself.
BTW, I want to correct myself: tblMembers has around 78000 members
instead of 7800 members.
Thanks again for all of you.
Pablo