Recent content by vckidd

SELECT Patients.[First Name] & " " & Patients.[Surname] AS Fullname, [Clinic Patient].[MYMOP ID], NZ(MYMOPs.[MYMOP Date], [Clinic Patient].[First Appointment]) AS [MYMOP1 Date], MYMOPs.Completed AS [MYMOP1 Completed], NZ(M2.[MYMOP Date], DateAdd("m", 1, [Clinic Patient].[First Appointment])) AS...

I definitely have the brackets and it is definitely missing data. I The annoying thing is that my original query works and then just occasionally throws the join expression error. Could you point me in the direction of anywhere that show me how to do separate queries and then join back, please?

Just realised by testing this stuff that moving the left join "and" statement to the where clause with the extra null is only bringing back data from someone who has all the joins as not null or someone who has all null joins. Is there any other way to join to a table multiple times without...

I get you. Thanks so much that seems to have done the trick and makes the code much easier to read. Thanks so much for your help!!

Sorry but I don't understand what you mean by criteria as opposed to a join. I am joining to the same table 5 times to get monitoring information and the MYMOP Type = 1,2,3+(1),3+(2),etc. are the different lines in that table. I don't know how else I would do that. Happy to try anything though...

I then have to put the brackets round the ON clauses because they have been removed. It then gives me multiple JOIN expression errors.

SELECT Patients.[First Name] & " " & Patients.[Surname] AS Fullname, [Clinic Patient].[MYMOP ID], NZ(MYMOPs.[MYMOP Date], [Clinic Patient].[First Appointment]) AS [MYMOP1 Date], MYMOPs.Completed AS [MYMOP1 Completed], NZ(M2.[MYMOP Date], DateAdd("m", 1, [Clinic Patient].[First Appointment])) AS...

I have cut and pasted the query from a word document where I was trying to assess where my brackets were going wrong. That isn't how the query looks in access. Also I have inherited this database so didn't see the point in massively changing the naming conventions as it would be a major pain. A...

I am trying to execute the crazy query below with multiple left joins because of the data I am trying to get back. The weird thing is sometimes it work and then sometimes it gives me a join expression error. It seems that access strangely removes brackets around the ON clauses. However even when...

"How we heard2 about relates to the separate treatment types but is one of the reasons I know I would be better with a separate treatment table. Is that what you meant? A client can have treatment A, H and N or just one and each of those treatments has an associated "how did you hear" about it...

I can't attach anything because I am a new user but here are the types of fields I have: ID name address fields contact fields treatment first A appt how they heard last a appt a comment first h appt how they heard last h appt h comments first n appt n how n comments dob MY first a appt MY...

I am working on a database at work which at present is just one table and I think it would be better as 4. Is there an easy way to change the underlying structure of the database or would I be better creating a new database and if yes how would I migrate the current data? Any ideas gratefully...

Hello from Viv in York, UK. I am not new to Access Databases but haven't used one for a looooooong time so class myself as a newbie. I am a computer programmer by trade by have been home edding my kids for the last 13 years and have only just started working (very part-time) over the last few...