creating a query that displays 2 items from 2 tables (1 Viewer)

[shutzy]

i have a many to many tables in my database which i have uploaded.

tblClientDetails hold the ClientdDetailsId
tblTreatmentList hold TreatmentListID
tblAppointments holds AppointmentID & ClientDetailsID
tblAppointmentTreatmentItems hold AppointmentID & TreatmentItemsID

i want a query that shows me for example what client 731 had as a treatment. for this is need to be able to filter the client. find what appointments belong to that client and then find out what appointment treatments apply to that appointment. thus giving the result of what treatment that client had.

can anybody help with this as i am really stuck and this is the first time i have even used a many to many table system and definately the first time i have tried to extract information from it.

ps. sorry for posting this again but the answer i got in the first instance wasnt really an answer but mearly a suggestion of how my table system should be. i dont mean any offence by this just in my experience of posting threads is that if it already has replies then few and far between do other user give input.

i really would like to know if it is possible with the table system i have.

 

[MarkK]

The type of your AppointmentsID field in tblAppointmentTreatment is Text and you are trying to join it to a long integer autonumber. This fails in SQL with a 'Type Mismatch in Expression' error.
That field needs to be a long integer.
Fix that and you can use this SQL...

SELECT tblClientDetails.FirstName, tblAppointments.DateOfTreatment, tblTreatmentList.Treatments
FROM tblTreatmentList INNER JOIN ((tblClientDetails INNER JOIN tblAppointments ON tblClientDetails.ClientDetailsID = tblAppointments.ClientDetailsID) INNER JOIN tblAppointmentTreatmentItems ON tblAppointments.AppointmentID = tblAppointmentTreatmentItems.AppointmentsID) ON tblTreatmentList.TreatmentListID = tblAppointmentTreatmentItems.TreatmentItemsID;

Cheers,
Mark