using query returns to create new columns

[TaniaD]

Hi,
I'm working on a project that requires something I'm sure can be
accomplished with SQL, but I can't figure it out myself. Basically I have a
list of names with corresponding specialty information. The specialties have
a varied number of corresponding subspecialties, so it currently looks like:

Name Specialty Subspecialty
Dr. Joe Emergency Emergency - no surgery
Dr. Joe Emergency Emergency - w/ surgery
Dr. Joe Emergency Urgent Care Medicine
Dr. Jane Oncology Surgery - Oncology
Dr. Jane Oncology Oncology - no surgery

etc...

What I need to do is create a query that will allow all of the providers
records to appear in one row, filling in up to 5 subspecialty columns, as
follows:

Name Specialty Subspecialty1 Subspecialty2
Subspecialty3
Dr. Joe Emergency Emergency -no surgery Emergency-w/surgery etc.
Dr. Jane Oncology Surgery - Oncology Oncology - no surgery

Any thoughts or suggestions on this would be greatly appreciated!

Thank you!

 

[KARL DEWEY]

Sounds like a job for a crosstab query.

 

[Marshall Barton]

I'm working on a project that requires something I'm sure can be
accomplished with SQL, but I can't figure it out myself. Basically I have a
list of names with corresponding specialty information. The specialties have
a varied number of corresponding subspecialties, so it currently looks like:

Name Specialty Subspecialty
Dr. Joe Emergency Emergency - no surgery
Dr. Joe Emergency Emergency - w/ surgery
Dr. Joe Emergency Urgent Care Medicine
Dr. Jane Oncology Surgery - Oncology
Dr. Jane Oncology Oncology - no surgery

etc...

What I need to do is create a query that will allow all of the providers
records to appear in one row, filling in up to 5 subspecialty columns, as
follows:

Name Specialty Subspecialty1 Subspecialty2
Subspecialty3
Dr. Joe Emergency Emergency -no surgery Emergency-w/surgery etc.
Dr. Jane Oncology Surgery - Oncology Oncology - no surgery

You can use a function to do that. There's a good one at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[TaniaD]

Could you please let me know what the function is called that you're
referring to? The posted link didn't work.

Thanks!

 

[John Spencer]

If I were doing this I would be using a ranking query to get the
subspecialties in some kind of order and then a crosstab to display the data.

SELECT A.Name, A.Specialty, A.SubSpecialty
, Count(B.SubSpecialty) as SubSpecRank
FROM SomeTable as A LEFT JOIN SomeTable as B
On A.Name = B.Name
AND A.Specialty = B.Specialty
AND A.SubSpecialty < B.SubSpecialty
GROUP BY A.Name, A.Specialty, A.SubSpecialty

Now use that result, in a crosstab query
TRANSFORM First(SubSpecialty)
SELECT [Name],[Specialty]
FROM AboveQuery
GROUP BY [Name], [Specialty]
Pivot SubSpecRank in (1,2,3,4,5)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

The function is named Concatenate and the link works for me.

If you really want the subspecialties in separate columns,
then I agree with Karl and John about using a crosstab
query.

 

[John Spencer]

The link as posted for some reason does not work. The final apostrophe does
not seem to be part of the link.

Try this link
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
and then find and click on
Generic Function To Concatenate Child Records

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

How odd.

When composing my original response, I went to the main
page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

Then scrolled down to Duane's section and clicked on the
Concatenate link. At the page for the function, I
Copy/Pasted the IE address bar, which included the final
apostrophe.

After Tania said the link didn't work, I double clicked the
link in quoted part of Tania's message and it worked just
fine.

I am using Agent, but maybe the readers you and Tania are
using are stripping the apostrophe? If so, try adding the
apostrophe in IE's address bar when you get the page error.

 

[John Spencer]

Oh, I did that long ago (added the apostrophe).

I am using Thunderbird and have not experienced this particular peculiar
behavior before.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County