using query returns to create new columns
in [Queries]
|
From: TaniaD on 30 Jun 2008 13:41 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!
From: KARL DEWEY on 30 Jun 2008 14:06 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!
From: Marshall Barton on 30 Jun 2008 14:35 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%20To%20Concatenate%20Child%20Records' -- Marsh MVP [MS Access]
From: TaniaD on 30 Jun 2008 14:55 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%20To%20Concatenate%20Child%20Records' > > -- > Marsh > MVP [MS Access] >
From: John Spencer on 30 Jun 2008 15:02
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! |