Multiple field values need to be returned in ONE line (1 Viewer)

[TessB]

I have no idea if this is possible or not.
I have a requirement to pull a list of Doctor's, their address, and which counties they serve. Sounds fine, right? Well, they want all the counties on one line... One record for each unique address.

So, my query would return this
Dr Address County
Dr Smith Main Street Plymouth County
Dr Smith Main Street Sarasota County

But they want it to look like this
Dr Address County
Dr Smith Main Street Plymouth County Sarasota County

Any idea on how to pull that together for them and put it in an Excel spreadsheet?

Many many thanks.
Tess

 

[Banana]

Look up on concentating using the character &.

 

[Brianwarnock]

I don't know if I should get involved as my visits are spasmodic buthere goes.

I assume that there is a code that uniquely identifies the Dr .
Do they want it only on the spread sheet?
Will each field including each County be in a separate cell?

If the answers to the above are Yes then I would do the breakdown in Excel.

Having sorted the query by code and exported it to Excel, the macro would step through the rows on sheet1 and place the data into sheet2, if there is a change of code then a new row is started on sheet2 if not the the County cell is placed in the next cell of the current row on sheet2.

I just wish my VBA was good enough to do it for you but it would take me quite a while and the weather is too good , but I hope this helps.

Brian

 

[TessB]

Yes, Brian, that is exactly the situation and your solution DOES sound promising, but my VBA skills are no longer up to par as my job has been focusing on SQL queries and Actuate report writing. Been quite a while since I did a macro in Excel.
If anyone wants to try their hand at Brian's solution, I'd very much appreciate the help. (And the young lady that would have to do it manually would appreciate it even more.)

 

[Brianwarnock]

You may have to post to the EXCEL forum for help, you might like to look at Microsofts help and support KB item 141624, but that also requires VBA work.

Brian

 

[Rich]

This might give you some ideas on how to do it in Access


Public Function Conc(ID, IDa) As String
Dim db As DAO.DATABASE
Dim rs As DAO.Recordset
Dim SQL As String
Dim sConc As String

Set db = CurrentDb

SQL = "SELECT [Cust] AS Fld2 FROM [MoniesReceivedQry]"


If TypeName(ID) = "Date" And TypeName(IDa) = "String" Then

SQL = SQL & "WHERE [fldPymntDate]= #" & Format(ID, "mm/dd/yyyy") & "# And [fldPdTo] = """ & IDa & """"
Else
SQL = SQL & " WHERE [fldPymntDate]= #" & ID & "# And [fldPdTo] = """ & IDa & """"

End If

Set rs = db.OpenRecordset(SQL)

Do While Not rs.EOF


sConc = sConc & ", " & rs!Fld2

sConc = sConc
rs.MoveNext

Loop
sConc = Mid(sConc, 3) 'Remove leading comma and space.

Set rs = Nothing
Set db = Nothing

Conc = sConc

End Function