mloucel
Member
- Local time
- Today, 13:21
- Joined
- Aug 5, 2020
- Messages
- 255
Hello GURUS:
I am building sort of a complex query [for me "newbie"] and this is what I need to accomplish:
The query should give me all the records while they are not marked CANCELED, the caveat is that 1 of the tables contains Notes for that record, they can be 1, 2 or 3 notes, for the purpose of this particular form I do not care how many notes there are, I am happy to see the first note only, then when the user picks the record a different form will pop up, showing that particular record with all bells and whistles.
The table for notes has the following structure:
PnoteID - AutoNumber
AuthorizationID - FK to the main database
PNoteDate - date the note was entered
PNote - note attached [Long Text]
This is the SQL created by Access, the DISTINCT clause was added by me trying to make it work, but as you can infer I am not good at all.
Or if you know of any other way to RE_DO this SQL then please correct by any means.
ANY help will be appreciated.
Thank you.
I am building sort of a complex query [for me "newbie"] and this is what I need to accomplish:
The query should give me all the records while they are not marked CANCELED, the caveat is that 1 of the tables contains Notes for that record, they can be 1, 2 or 3 notes, for the purpose of this particular form I do not care how many notes there are, I am happy to see the first note only, then when the user picks the record a different form will pop up, showing that particular record with all bells and whistles.
The table for notes has the following structure:
PnoteID - AutoNumber
AuthorizationID - FK to the main database
PNoteDate - date the note was entered
PNote - note attached [Long Text]
This is the SQL created by Access, the DISTINCT clause was added by me trying to make it work, but as you can infer I am not good at all.
Or if you know of any other way to RE_DO this SQL then please correct by any means.
ANY help will be appreciated.
Thank you.
Code:
SELECT DISTINCT AuthorizationNotesT.authorizationID, AuthorizationT.AuthorizationID, UserT.UserName, AuthorizationT.ReferDate, [plastname] & ", " & [pfirstname] AS Name, PatientT.PDOB, PatientT.PPhone, IpaT.IpaName, HealthPlanT.HealthPlan, AuthorizationT.Diagnosis, DoctorT.DocName, SpecialtyT.Specialty, SpecialistT.DrName, SpecialistT.DrPhone, AuthorizationT.Urgent, AuthorizationT.AuthDateEntered, AuthorizationT.ApprovedDate, AuthorizationT.AppointmentDate, AuthorizationT.MedicalRecordSendDate, AuthorizationT.PatientNotifiedDate, AuthorizationT.ReportFileDate, AuthorizationT.DeniedDate, AuthorizationT.ModifiedDate, AuthorizationNotesT.PNoteDate, AuthorizationNotesT.PNote
FROM UserT INNER JOIN ((SpecialtyT INNER JOIN SpecialistT ON SpecialtyT.SpecialtyID = SpecialistT.SpecialtyID) INNER JOIN (PatientT INNER JOIN (IpaT INNER JOIN (HealthPlanT INNER JOIN (DoctorT INNER JOIN (AuthorizationT INNER JOIN AuthorizationNotesT ON AuthorizationT.AuthorizationID = AuthorizationNotesT.AuthorizationID) ON DoctorT.DoctorID = AuthorizationT.DoctorID) ON HealthPlanT.HealthPlanID = AuthorizationT.HealthPlanID) ON IpaT.IpaID = AuthorizationT.IpaID) ON PatientT.PatientID = AuthorizationT.PatientID) ON (SpecialtyT.SpecialtyID = AuthorizationT.SpecialtyID) AND (SpecialistT.SpecialistsID = AuthorizationT.SpecialistID)) ON UserT.UserID = AuthorizationT.UserID
WHERE (((AuthorizationT.AuthCanceled)=False))
ORDER BY AuthorizationT.AuthorizationID;