Merge two fields when duplicated 1

[surfbum3000]

I need to merge two fields only when there is a duplicate. I created a query to separate the duplicates from a table (although that step may not be necessary. The table contains appt. information for an outpatient clinic. Some patients have multiple appts. A sample of the data is as follows:

NAME LOCATION DATE TIME STAFF_NAME

Doe, John NWMH 06/06/2008 13:00 Freud
Doe, John NWMH 06/06/2008 14:00 Ratchet

In need the data to be in this format:

Doe, John NWMH 06/06/2008 13:00,14:00 Freud,Ratchet

I have no idea where to start.

 

[dhookom]

Start by searching the FAQs for this forum.

Then consider changing your field names (if you are displaying actual names) to non-reserved words. NAME, DATE, and TIME are not good candidates for field names.

Duane
Hook'D on Access
MS Access MVP

 

[surfbum3000]

I have been searching the FAQs and have googled it. As with most searches such as this, I am not sure what topics to search on. I have read about append queries but that doesn't seem to work. I have also come across concatenate but that also doesn't seem to do what I need done. I don't mind reading and teaching myself but if someone could provide a hint as to what to search for that would be appreciated.

 

[fneily]

You might also want to post your table structures - fieldnames, primary keys. If you have a field that has Doe, John in it, that table is already set up wrong.
Have you studied normalization?
See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[Golom]

faq701-4233 is one place you might start.

 

[surfbum3000]

Thanks Golom for the lead. This code worked:

SELECT tblTestDups.CASE_NUM, tblTestDups.STARTTIME, Concatenate("SELECT STARTTIME FROM tblTestDups
WHERE CASE_NUM =""" & [CASE_NUM] & """") AS STARTTIME2
FROM tblTestDups;