Question about SQL code

[tnt203]

Hello Everyone,

I am new to the forum. I have a question about the SQL code from a sample Access appointment database that I downloaded from this thread:

//access-programmers.co.uk/forums/showthread.php?t=275897

It is from the post #7. I was trying to ask question in this thread, but it was closed.

I downloaded this file:

Wide Calendar with Switchboard.zip

and used this file from the zip, Wide Calendar with Switchboard Mon-Sun.mdb

I added a table, called tblTherapist containing names of the therapist and created a relationship to tblPatients table. The calendar worked perfectly, but when I clicked on the daily block on calendar, in the detail display below the calendar, it listed all the appointment for the day. In stead of listing the therapist names, it only listed the ID of the therapist. I am hoping someone can point out where to fix this so I can have the therapist name in the detail listing below the calendar. I would be easier to know the appointment belong whom than the ID number. I uploaded the access file and the screen pictures below. Thanks for any help. I really appreciate any help you can give.
Best Regards,
Tung

Sorry, the forum would not let me attach links.

 

[Gasman]

I believe you will need to amend this code

strSQL2 = "SELECT tblPatients.Patient_ID, tblPatients.TherapistID, tblPatients.Account, tblPatients.Last, tblPatients.First, tblPatients.Date, tblPatients.Time, " & _
          "tblVisitType.Type, tblVisitType.Code, tblPatients.Note FROM tblVisitType INNER JOIN tblPatients ON " & _
          "tblVisitType.TypeID = tblPatients.TypeID " & _
          "WHERE tblPatients.Date = " & ctlDayBlock.Tag & _
          " ORDER BY tblPatients.Time, tblPatients.Last, tblPatients.First;"

in PopulateEventsList(ctlDayBlock As Control) in frmCalendar

You can see that the first two fields are ID fields.

Run/amend the code in the QBE window to get what you want, then paste back into this function.

I'd coment out the old code, so you can always refer to it for a while.

You use this for the view appointments combo

SELECT tblTherapist.TherapistID, tblTherapist.FirstName, tblTherapist.LastName, [FirstName] & " " & [LastName] AS Name
FROM tblTherapist;

so why are you not using 'FirstName] & " " & [LastName] AS Name' in the listbox?

You willl need to add the Therapist table and joing to Patient table using the therapistID.

HTH

 

[Ranman256]

instead of tPatiend.PatientID,
show tPatient.PatientName

same w therapist.

 

[Gasman]

instead of tPatiend.PatientID,
show tPatient.PatientName

same w therapist.

Well I'd say you only the need the concatenation that you have used for the therapist name, as you already have the patient name in separate columns.?

 

[tnt203]

Thank you, Ranman256 and Gasman. I was able make it to work the way I like. What I did was created a table with therapist names, and in the Patients form I made a combo box to select the name from therapist table. Thanks again for your help.
Best Regards,
Tung

PS, I have been trying to login the forum, for some reason the computer at work will not let me log, always saying I enter the wrong password, even I know my password is correct. I used my tablet to login and no problem. I’m sorry for the delay to reply.

 

[Gasman]

Well if you had a TherapistID, I would have expected you to have their names in the same record, so a little confused.?

Not to worry though, you have it as you want it, which is the end result required.

 

[tnt203]

Well if you had a TherapistID, I would have expected you to have their names in the same record, so a little confused.?

Not to worry though, you have it as you want it, which is the end result required.

If I used the TherapistID in the combox, only shown the record ID number not the name, and I tried everything, but I did not how to fix it. This worked well and I am a happy baby with a lollipop. Thank you for the help and have a nice weekend.