Join expression not supported

V

vckidd

Registered User.
Local time
Today, 20:33
Joined
May 8, 2015
Messages
13
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 I put those brackets back in this query it isn't working. Can anyone point me in the correct direction either for why this query isn't running or why the brackets disappear in Access 2010. Any help gratefully appreciated.
Cheers
Viv

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 [MYMOP2 Date]
, M2.Completed AS [MYMOP2 Completed]
, NZ(M3.[MYMOP Date], DateAdd("m", 2, [Clinic Patient].[First Appointment])) AS [MYMOP3+ Date]
, M3.Completed AS [MYMOP3+ Completed]
, NZ(M4.[MYMOP Date], DateAdd("m", 3, [Clinic Patient].[First Appointment])) AS [MYMOP3+(2)]
, M4.Completed AS [MYMOP3+(2) Completed]
, NZ(M5.[MYMOP Date], DateAdd("m", 4, [Clinic Patient].[First Appointment])) AS [MYMOP3+(3)]
, M5.Completed AS [MYMOP3+(3) Completed]
, NZ(M6.[MYMOP Date], DateAdd("m", 5, [Clinic Patient].[First Appointment])) AS [MYMOP3+(4)]
, M6.Completed AS [MYMOP3+(4) Completed]
, NZ(M7.[MYMOP Date], DateAdd("m", 6, [Clinic Patient].[First Appointment])) AS [MYMOP3+ Final]
, [Clinic Patient].[Final MYMOP3+ Posted Date]
, [Clinic Patient].[Final MYMOP3+ Returned]
, [Clinic Patient].Notes
FROM ((((((((
Patients INNER JOIN [Clinic Patient] ON Patients.ID = [Clinic Patient].[Patient ID]
)
LEFT JOIN MYMOPs ON ([Clinic Patient].ID = MYMOPs.[Clinic Patient ID] AND MYMOPs.[MYMOP Type] = "1")
)
LEFT JOIN MYMOPs AS M2 ON ([Clinic Patient].ID = M2.[Clinic Patient ID] AND M2.[MYMOP Type] = "2")
)
LEFT JOIN MYMOPs AS M3 ON ([Clinic Patient].ID = M3.[Clinic Patient ID] AND M3.[MYMOP Type] = "3+(1)")
)
LEFT JOIN MYMOPs AS M4 ON ([Clinic Patient].ID = M4.[Clinic Patient ID] AND M4.[MYMOP Type] = "3+(2)")
)
LEFT JOIN MYMOPs AS M5 ON ([Clinic Patient].ID = M5.[Clinic Patient ID] AND M5.[MYMOP Type] = "3+(3)")
)
LEFT JOIN MYMOPs AS M6 ON ([Clinic Patient].ID = M6.[Clinic Patient ID] AND M6.[MYMOP Type] = "3+(4)")
)
LEFT JOIN MYMOPs AS M7 ON ([Clinic Patient].ID = M7.[Clinic Patient ID] AND M7.[MYMOP Type] = "3+(final)")
)
WHERE [Clinic Patient].[Clinic ID] = 3
AND [Clinic Patient].Status <> "Archived"
ORDER BY [Clinic Patient].[First Appointment] DESC;
 
access doesn't format the code as you are displaying it, so either you are formatting it manually or you are using one of the formatting tools - so I suspect it is this process that is removing brackets
 
Another issue is your naming. When you use non-alphanumeric characters (including spaces) in table, query and field names you must use braces and Access tries to help out (sometimes to your detriment). You should only use alpha-numeric characters and underscores in your names:

[Clinic Patient ID] = ClinicPatientID
[MYMOP Type] = MYMOP_Type
[MYMOP3+ Date] = MYMOP3_Date
 
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 similar query with slightly less left joins works fine. Is there anything else people can see wrong with the query which might help?

Thanks
 
How about copying the query from Access and show us that.
 
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 [MYMOP2 Date], M2.Completed AS [MYMOP2 Completed], NZ(M3.[MYMOP Date], DateAdd("m", 2, [Clinic Patient].[First Appointment])) AS [MYMOP3+ Date], M3.Completed AS [MYMOP3+ Completed], NZ(M4.[MYMOP Date], DateAdd("m", 3, [Clinic Patient].[First Appointment])) AS [MYMOP3+(2)], M4.Completed AS [MYMOP3+(2) Completed], NZ(M5.[MYMOP Date], DateAdd("m", 4, [Clinic Patient].[First Appointment])) AS [MYMOP3+(3)], M5.Completed AS [MYMOP3+(3) Completed], NZ(M6.[MYMOP Date], DateAdd("m", 5, [Clinic Patient].[First Appointment])) AS [MYMOP3+(4)], M6.Completed AS [MYMOP3+(4) Completed], NZ(M7.[MYMOP Date], DateAdd("m", 6, [Clinic Patient].[First Appointment])) AS [MYMOP3+ Final], [Clinic Patient].[Final MYMOP3+ Posted Date], [Clinic Patient].[Final MYMOP3+ Returned], [Clinic Patient].Notes
FROM (((((((Patients INNER JOIN [Clinic Patient] ON Patients.ID = [Clinic Patient].[Patient ID]) LEFT JOIN MYMOPs ON [Clinic Patient].ID = MYMOPs.[Clinic Patient ID] AND MYMOPs.[MYMOP Type] = "1") LEFT JOIN MYMOPs AS M2 ON [Clinic Patient].ID = M2.[Clinic Patient ID] AND M2.[MYMOP Type] = "2") LEFT JOIN MYMOPs AS M3 ON [Clinic Patient].ID = M3.[Clinic Patient ID] AND M3.[MYMOP Type] = "3+(1)") LEFT JOIN MYMOPs AS M4 ON [Clinic Patient].ID = M4.[Clinic Patient ID] AND M4.[MYMOP Type] = "3+(2)") LEFT JOIN MYMOPs AS M5 ON [Clinic Patient].ID = M5.[Clinic Patient ID] AND M5.[MYMOP Type] = "3+(3)") LEFT JOIN MYMOPs AS M6 ON [Clinic Patient].ID = M6.[Clinic Patient ID] AND M6.[MYMOP Type] = "3+(4)") LEFT JOIN MYMOPs AS M7 ON [Clinic Patient].ID = M7.[Clinic Patient ID] AND M7.[MYMOP Type] = "3+(final)"
WHERE [Clinic Patient].[Clinic ID] = 2
AND [Clinic Patient].Status <> "Archived"
ORDER BY [Clinic Patient].[First Appointment] DESC;
 
Reformatted for readability only via Poor sql
Code: 
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 [MYMOP2 Date]
	,M2.Completed AS [MYMOP2 Completed]
	,NZ(M3.[MYMOP Date], DateAdd("m", 2, [Clinic Patient].[First Appointment])) AS [MYMOP3+ Date]
	,M3.Completed AS [MYMOP3+ Completed]
	,NZ(M4.[MYMOP Date], DateAdd("m", 3, [Clinic Patient].[First Appointment])) AS [MYMOP3+(2)]
	,M4.Completed AS [MYMOP3+(2) Completed]
	,NZ(M5.[MYMOP Date], DateAdd("m", 4, [Clinic Patient].[First Appointment])) AS [MYMOP3+(3)]
	,M5.Completed AS [MYMOP3+(3) Completed]
	,NZ(M6.[MYMOP Date], DateAdd("m", 5, [Clinic Patient].[First Appointment])) AS [MYMOP3+(4)]
	,M6.Completed AS [MYMOP3+(4) Completed]
	,NZ(M7.[MYMOP Date], DateAdd("m", 6, [Clinic Patient].[First Appointment])) AS [MYMOP3+ Final]
	,[Clinic Patient].[Final MYMOP3+ Posted Date]
	,[Clinic Patient].[Final MYMOP3+ Returned]
	,[Clinic Patient].Notes
FROM (
	(
		(
			(
				(
					(
						(
							Patients INNER JOIN [Clinic Patient] ON Patients.ID = [Clinic Patient].[Patient ID]
							) LEFT JOIN MYMOPs ON [Clinic Patient].ID = MYMOPs.[Clinic Patient ID]
							AND MYMOPs.[MYMOP Type] = "1"
						) LEFT JOIN MYMOPs AS M2 ON [Clinic Patient].ID = M2.[Clinic Patient ID]
						AND M2.[MYMOP Type] = "2"
					) LEFT JOIN MYMOPs AS M3 ON [Clinic Patient].ID = M3.[Clinic Patient ID]
					AND M3.[MYMOP Type] = "3+(1)"
				) LEFT JOIN MYMOPs AS M4 ON [Clinic Patient].ID = M4.[Clinic Patient ID]
				AND M4.[MYMOP Type] = "3+(2)"
			) LEFT JOIN MYMOPs AS M5 ON [Clinic Patient].ID = M5.[Clinic Patient ID]
			AND M5.[MYMOP Type] = "3+(3)"
		) LEFT JOIN MYMOPs AS M6 ON [Clinic Patient].ID = M6.[Clinic Patient ID]
		AND M6.[MYMOP Type] = "3+(4)"
	)
LEFT JOIN MYMOPs AS M7 ON [Clinic Patient].ID = M7.[Clinic Patient ID]
	AND M7.[MYMOP Type] = "3+(final)"
WHERE [Clinic Patient].[Clinic ID] = 2
	AND [Clinic Patient].STATUS <> "Archived"
ORDER BY [Clinic Patient].[First Appointment] DESC;
 
I then have to put the brackets round the ON clauses because they have been removed. It then gives me multiple JOIN expression errors.
 
The code I showed in post #7 is the sql you provided in post#6. I did not change anything, just reformatted with poor sql reformatter.

I tried to run the query(even though I don't have any of the tables) and got the Join not supported message as well.
 
found this link

http://webcheatsheet.com/sql/access_specification.php

which states 'Number of joins in a query 16*'
...
...
*Maximum values might be lower if the query includes multivalued lookup fields

not sure if
LEFT JOIN MYMOPs ON [Clinic Patient].ID = MYMOPs.[Clinic Patient ID]
AND MYMOPs.[MYMOP Type] = "1"

counts as one or two joins but looks like you might have reached the maximum number of joins

can you change this bit

AND MYMOPs.[MYMOP Type] = "1"

to be a criteria instead of part of a join

or are you using multivalue fields in your tables?

Alternatively split the query in some way
 
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 if there is a way to achieve joining to the same data in a table 5 times.
 
I'm suggesting modify your joins to

Code: 
 Patients INNER JOIN [Clinic Patient] ON Patients.ID = [Clinic Patient].[Patient ID]
) LEFT JOIN MYMOPs ON [Clinic Patient].ID = MYMOPs.[Clinic Patient ID]
 ) LEFT JOIN MYMOPs AS M2 ON [Clinic Patient].ID = M2.[Clinic Patient ID]
   ) LEFT JOIN MYMOPs AS M3 ...
and your criteria to

Code: 
 WHERE [Clinic Patient].[Clinic ID] = 2
AND [Clinic Patient].STATUS <> "Archived"
  AND (MYMOPs.[MYMOP Type] = "1" or MYMOPs.[MYMOP Type] is null)
   AND (M2.[MYMOP Type] = "2" OR M2.[MYMOP Type] is null)
     ....
 
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!!
 
you'll also be able to see the query in the query grid
 
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 running out of joins?

Thanks
Viv
 
only bringing back data from someone who has all the joins as not null or someone who has all null joins.
Click to expand...
it shouldn't do - important you have the brackets tho so check you have these

Code: 
 AND[COLOR=red][B] ([/B][/COLOR]MYMOPs.[MYMOP Type] = "1" or MYMOPs.[MYMOP Type] is null[B][COLOR=red])
[/COLOR][/B]
Is there any other way to join to a table multiple times without running out of joins?
Click to expand...
no - that is the limit.

However you may be able to split the query into a number of separate queries which can then join back
 
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?
 
If you post your revised query sql, I'll take a look

With regards splitting queries, take a copy, remove half of the tables you are left joining to, then in another copy, remove the other half. Then simply have a 3rd query to combine the two together
 
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 [MYMOP2 Date], M2.Completed AS [MYMOP2 Completed], NZ(M3.[MYMOP Date], DateAdd("m", 2, [Clinic Patient].[First Appointment])) AS [MYMOP3+ Date], M3.Completed AS [MYMOP3+ Completed], NZ(M4.[MYMOP Date], DateAdd("m", 3, [Clinic Patient].[First Appointment])) AS [MYMOP3+(2)], M4.Completed AS [MYMOP3+(2) Completed], NZ(M5.[MYMOP Date], DateAdd("m", 4, [Clinic Patient].[First Appointment])) AS [MYMOP3+(3)], M5.Completed AS [MYMOP3+(3) Completed], NZ(M6.[MYMOP Date], DateAdd("m", 5, [Clinic Patient].[First Appointment])) AS [MYMOP3+(4)], M6.Completed AS [MYMOP3+(4) Completed], NZ(M7.[MYMOP Date], DateAdd("m", 6, [Clinic Patient].[First Appointment])) AS [MYMOP3+ Final], [Clinic Patient].[Final MYMOP3+ Posted Date], [Clinic Patient].[Final MYMOP3+ Returned], [Clinic Patient].Notes
FROM (((((((Patients INNER JOIN [Clinic Patient] ON Patients.ID = [Clinic Patient].[Patient ID]) LEFT JOIN MYMOPs ON [Clinic Patient].ID = MYMOPs.[Clinic Patient ID] AND MYMOPs.[MYMOP Type] = "1") LEFT JOIN MYMOPs AS M2 ON [Clinic Patient].ID = M2.[Clinic Patient ID] AND M2.[MYMOP Type] = "2") LEFT JOIN MYMOPs AS M3 ON [Clinic Patient].ID = M3.[Clinic Patient ID] AND M3.[MYMOP Type] = "3+(1)") LEFT JOIN MYMOPs AS M4 ON [Clinic Patient].ID = M4.[Clinic Patient ID] AND M4.[MYMOP Type] = "3+(2)") LEFT JOIN MYMOPs AS M5 ON [Clinic Patient].ID = M5.[Clinic Patient ID] AND M5.[MYMOP Type] = "3+(3)") LEFT JOIN MYMOPs AS M6 ON [Clinic Patient].ID = M6.[Clinic Patient ID] AND M6.[MYMOP Type] = "3+(4)") LEFT JOIN MYMOPs AS M7 ON [Clinic Patient].ID = M7.[Clinic Patient ID] AND M7.[MYMOP Type] = "3+(final)"
WHERE [Clinic Patient].[Clinic ID] = 2
AND [Clinic Patient].Status <> "Archived"
ORDER BY [Clinic Patient].[First Appointment] DESC;

The above is the original query which was giving the error. My husband has helped and this is what we have come up with which seems to work but seems awfully complicated:

SELECT [OUTER].Fullname, [OUTER].[MYMOP ID], NZ([OUTER].firstDate, [OUTER].firstAppointment) AS [1 Date], Switch(firstComp is not null, "Yes", firstcomp is null, "No" ) AS [1 Complete], NZ([OUTER].secondDate, DATEADD("ww", 4, [OUTER].firstAppointment)) AS [2 Date], Switch(secondComp is not null, "Yes", secondComp is null, "No" ) AS [2 Complete], NZ([OUTER].thirdDate, DATEADD("ww", 6, [OUTER].firstAppointment)) AS [3(1) Date], Switch(thirdComp is not null, "Yes", thirdComp is null, "No" ) AS [3+(1) Complete], NZ([OUTER].fourthDate, DATEADD("ww", 8, [OUTER].firstAppointment)) AS [3(2) Date], Switch(fourthComp is not null, "Yes", fourthComp is null, "No" ) AS [3+(2) Complete], NZ([OUTER].fifthDate, DATEADD("ww", 10, [OUTER].firstAppointment)) AS [3(3) Date], Switch(fifthComp is not null, "Yes", fifthcomp is null, "No" ) AS [3+(3) Complete], [OUTER].[3+FPost], Switch([OUTER].[3+FReturn]=1,"Yes", [OUTER].[3+FReturn]=0, "No") AS [3+FReturn], NZ([OUTER].sixthDate, DATEADD("m", 6, [OUTER].firstAppointment)) AS [3(final) Date], Switch(sixthComp is not null, "Yes", sixthComp is null, "No" ) AS [3+(final) Complete]
FROM (SELECT [INNER].Fullname, Max([INNER].[MYMOP ID]) AS [MYMOP ID], Max(Switch(theType="1",theDate)) AS firstDate, Max(Switch(theType="2",theDate)) AS secondDate, Max(Switch(theType="3+(1)",theDate)) AS thirdDate, Max(Switch(theType="3+(2)",theDate)) AS fourthDate, Max(Switch(theType="3+(3)",theDate)) AS fifthDate, Max(Switch(theType="3+(final)",theDate)) AS sixthDate, Max([INNER].[First Appointment]) AS FirstAppointment, Max(Switch(theType="1",Completed)) AS firstComp, Max(Switch(theType="2",Completed)) AS secondComp, Max(Switch(theType="3+(1)",Completed)) AS thirdComp, Max(Switch(theType="3+(2)",Completed)) AS fourthComp, Max(Switch(theType="3+(3)",Completed)) AS fifthComp, Max(Switch(theType="3+(final)",Completed)) AS sixthComp, Max([INNER].[3+(F) Post]) AS [3+FPost], Max([INNER].[3+(F) Return]) AS [3+FReturn] FROM (SELECT Patients.[First Name] & " " & Patients.[Surname] AS Fullname, [Clinic Patient].[MYMOP ID], NZ(M1.[MYMOP Date] , [First Appointment]) AS theDate, M1.[MYMOP Date] AS Completed, [Clinic Patient].[Final MYMOP3+ Posted Date] AS [3+(F) Post], [Clinic Patient].[Final MYMOP3+ Returned] AS [3+(F) Return], [Clinic Patient].Notes, M1.[MYMOP Type] AS theType, [Clinic Patient].[First Appointment], [Clinic Patient].[MYMOP ID] FROM (Patients INNER JOIN [Clinic Patient] ON Patients.ID = [Clinic Patient].[Patient ID]) LEFT JOIN MYMOPs AS M1 ON [Clinic Patient].ID = M1.[Clinic Patient ID] WHERE [Clinic Patient].[Clinic ID] = 3 AND [Clinic Patient].status <> "Archived") AS [INNER] GROUP BY [INNER].Fullname) AS [OUTER]
ORDER BY [OUTER].firstAppointment DESC;

I thought you might want to see it. Thanks for your help and I would love to know if there is an easier way to do it that works.
 
difficult to envisage, but just a thought, here is a brief description

1. INNER JOIN Patients to [Clinic Patient]
2. LEFT JOIN MYMOPs ON [Clinic Patient].ID = MYMOPs.[Clinic Patient ID]
3. then use these statements in the select e.g.

a) Patients.[First Name] & " " & Patients.[Surname] AS Fullname

b) [Clinic Patient].[MYMOP ID]

c) iif(MYMOPs.[MYMOP Type]=1, MYMOPs.[MYMOP Date],[Clinic Patient].[First Appointment]) AS FirstDate

d) choose(MYMOPs.[MYMOP Type], "MYMOP1 Date","MYMOP2 Date",","MYMOP3+ Date", etc) AS AptHeader

e) DateAdd("m", MYMOPs.[MYMOP Type]-1, [Clinic Patient].[First Appointment])) AS [AptDate]

f) choose(MYMOPs.[MYMOP Type], "MYMOP1 Completed","MYMOP2 Completed",","MYMOP3+ Completed", etc) AS compHeader

G) MYMOPs.Completed


4. to arrive at the columns required (7 by my count), then convert to a crosstab query - you may need two crosstabs, one for appointment dates and one for completed y/n - cols a, b, c would be row headers, for the dates d would be column header and e the value and for the other crosstab, a, b and c for row headers, f for column header and g for value (both values you would use first rather than sum)) Note that c (first date) should be a max rather than group by. If this doesn't work, you'll need to create another alias of mymops left joined as before just to bring this through
 
Last edited:
You must log in or register to reply here.

Similar threads

AOB
Solved Insert records where ID combination not already present (JOIN expression not supported)
Replies
2
Views
448
arnelgp
arnelgp
A
Join expression not supported in join query Access
Replies
8
Views
611
CJ_London
C
B
Combo Box Search Issue
2
Replies
34
Views
1,821
MajP
MajP
D
Solved How to sort values (the highest first)? If duplicates values found, the sorting is based on the saved table for those values
Replies
13
Views
1,085
jdraw
jdraw
D
Join Expression Not Supported
Replies
13
Views
2,047
Drand
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom