|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 30th, 2008, 06:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
using query returns to create new columns
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! 
|
|
#2
June 30th, 2008, 07:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
using query returns to create new columns
Sounds like a job for a crosstab query.
-- KARL DEWEY Build a little - Test a little "TaniaD" wrote: 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!
|
|
#3
June 30th, 2008, 07:35 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
using query returns to create new columns
TaniaD wrote:
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/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records' -- Marsh MVP [MS Access]
|
|
#4
June 30th, 2008, 07:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
using query returns to create new columns
Could you please let me know what the function is called that you're
referring to? The posted link didn't work. Thanks! "Marshall Barton" wrote: TaniaD wrote: 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/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records' -- Marsh MVP [MS Access]
|
|
#5
June 30th, 2008, 08:02 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
using query returns to create new columns
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 TaniaD wrote: 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!
|
|
#6
June 30th, 2008, 10:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
using query returns to create new columns
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. -- Marsh MVP [MS Access] TaniaD wrote: Could you please let me know what the function is called that you're referring to? The posted link didn't work. "Marshall Barton" wrote: TaniaD wrote: 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/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records'
|
|
#7
July 1st, 2008, 12:22 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
using query returns to create new columns
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/O...p#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 wrote: 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.
|
|
#8
July 1st, 2008, 01:27 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
using query returns to create new columns
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. -- Marsh MVP [MS Access] John Spencer wrote: 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/O...p#Hookom,Duane and then find and click on Generic Function To Concatenate Child Records
|
|
#9
July 1st, 2008, 02:45 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
using query returns to create new columns
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 Marshall Barton wrote: 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
