By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Help me with this query.

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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 ...
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
-----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


Nov 12 '05 #5

P: n/a
MGFoster <me@privacy.com> 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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.