Left Join Not Returning All Rows

[MMcCarthy]

I receive this error:

Syntax error (missing operator) in query expression 'IIf(file_table .UserID=1, file_table.File Access,Null) As FAccess'.

Regards,
Julian

did you include the ' As FAccess' in the group by statement?

If you did remove it.

If not post the query as you're using it now.

[Julian32]

did you include the ' As FAccess' in the group by statement?

If you did remove it.

If not post the query as you're using it now.

No I did not remove it, here's the current query:

sql = "SELECT file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"GROUP BY file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess "

I'm thinking of changing FileAccess to a foreign key, then quering FileAccess based on UserID, saving query to an array, quering FileName, and checking to see if an item in the array matches.

Inneficient I know, but at least I know it will work in the meantime.

Regards,
Julian

[MMcCarthy]

No I did not remove it, here's the current query:

sql = "SELECT file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.FileAccess = file_list.FileName " & _
"GROUP BY file_list.FileName, IIf(file_table.UserID=1, file_table.FileAccess,Null) As FAccess "

REMOVE IT...

Paste in query exactly as below:


sql = "SELECT file_list.FileN ame, IIf(file_table. UserID=1, file_table.File Access,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.File Access = file_list.FileN ame " & _
"GROUP BY file_list.FileN ame, IIf(file_table. UserID=1, file_table.File Access,Null);"

[Julian32]

REMOVE IT...

Paste in query exactly as below:


sql = "SELECT file_list.FileN ame, IIf(file_table. UserID=1, file_table.File Access,Null) As FAccess " & _
"FROM file_table RIGHT JOIN file_list " & _
"ON file_table.File Access = file_list.FileN ame " & _
"GROUP BY file_list.FileN ame, IIf(file_table. UserID=1, file_table.File Access,Null);"

Hello mccarthy,

It goes back to what it was doing before the GROUP BY statement, it has repeats in the FileName column. Any way to filter that?

[Julian32]

Thanks to everyone who helped. I fixed the problem by making file_table.File Access a foreign key to ListID, and playing around with the ON statement. I thought of swithcing the IIf from the SELECT to the ON and Eureka!!

Here's the final statement:

sql = "SELECT file_list.FileName, file_table.listID FROM file_list LEFT " & _ 
"JOIN file_table ON file_list.ListID = " & _
"IIf(file_table.UserID=1,file_table.ListID,Null)"

I was this close to giving up and choosing to use an inneficient array.

Thanks again,
Julian

[MMcCarthy]

Thanks to everyone who helped. I fixed the problem by making file_table.File Access a foreign key to ListID, and playing around with the ON statement. I thought of swithcing the IIf from the SELECT to the ON and Eureka!!

Here's the final statement:

sql = "SELECT file_list.FileName, file_table.listID FROM file_list LEFT " & _ 
"JOIN file_table ON file_list.ListID = " & _
"IIf(file_table.UserID=1,file_table.ListID,Null)"

I was this close to giving up and choosing to use an inneficient array.

Thanks again,
Julian

You're welcom Julian. It's true that most of these problems arise because the table structure is not correct. I should probably have pointed that out.