JOIN expression not supported.

[aebstract]

Not sure why I am getting this error and was hoping someone could shed some light on the issue, everything worked fine in sql server management studio:

SELECT dbo_MIMOH.mohId, dbo_MIMOH.ordQty, dbo_MIMOH.buildItem, dbo_MIMOH.bomRev,
dbo_MIBOMD.qty as qty1, (dbo_MIBOMD.qty * dbo_MIMOH.ordQty) AS totalQty, dbo_MIBOMD.partId as part1, dbo_MIITEM.revId as rev1,
MIBOMD2.qty as qty2, (dbo_MIBOMD.qty * dbo_MIMOH.ordQty * MIBOMD2.qty) AS totalQty2, MIBOMD2.partId as part2, MIITEM2.revId as rev2

FROM (((dbo_MIMOH

LEFT JOIN dbo_MIBOMD ON dbo_MIBOMD.bomItem = dbo_MIMOH.buildItem AND dbo_MIBOMD.bomRev = dbo_MIMOH.bomRev)
LEFT JOIN dbo_MIITEM ON dbo_MIITEM.itemId = dbo_MIBOMD.partId)
LEFT JOIN dbo_MIBOMD AS MIBOMD2 ON MIBOMD2.bomItem = dbo_MIBOMD.partId AND MIBOMD2.bomRev = dbo_MIITEM.revId)
LEFT JOIN dbo_MIITEM AS MIITEM2 ON MIITEM2.itemId = MIBOMD2.partId

WHERE dbo_MIMOH.moStat = 1 AND dbo_MIMOH.mohId = 'US-1-0000002148';

[Micron]

Did you try dumping that sql into query sql view and then try to switch to datasheet view? Often that will highlight the offending part and/or provide more clues. I don't see anything obvious but then I'm no expert on SSMS.

[aebstract]

It won't let me switch it to datasheet or design view, it pops up the same error. If I change all of my LEFT JOINs to INNER JOINs, it doesn't give me an error. It doesn't give an error with INNER, however if I add in the rest of my code (3-5) then it just gives me blank results.

SELECT dbo_MIMOH.mohId, dbo_MIMOH.ordQty, dbo_MIMOH.buildItem, dbo_MIMOH.bomRev,dbo_MIBOMD.qty as qty1, (dbo_MIBOMD.qty * dbo_MIMOH.ordQty) AS totalQty, dbo_MIBOMD.partId as part1, dbo_MIITEM.revId as rev1,
MIBOMD2.qty as qty2, (dbo_MIBOMD.qty * dbo_MIMOH.ordQty * MIBOMD2.qty) AS totalQty2, MIBOMD2.partId as part2, MIITEM2.revId as rev2,
MIBOMD3.qty as qty3, (dbo_MIBOMD.qty * dbo_MIMOH.ordQty * MIBOMD2.qty * MIBOMD3.qty) AS totalQty3, MIBOMD3.partId as part3, MIITEM3.revId as rev3,
MIBOMD4.qty as qty4, (dbo_MIBOMD.qty * dbo_MIMOH.ordQty * MIBOMD2.qty * MIBOMD3.qty * MIBOMD4.qty) AS totalQty4, MIBOMD4.partId as part4, MIITEM4.revId as rev4,
MIBOMD5.qty as qty5, (dbo_MIBOMD.qty * dbo_MIMOH.ordQty * MIBOMD2.qty * MIBOMD3.qty * MIBOMD4.qty * MIBOMD5.qty) AS totalQty5, MIBOMD5.partId as part5, MIITEM5.revId as rev5


FROM (((((((((dbo_MIMOH 
LEFT JOIN dbo_MIBOMD ON dbo_MIBOMD.bomItem = dbo_MIMOH.buildItem AND dbo_MIBOMD.bomRev = dbo_MIMOH.bomRev)
LEFT JOIN dbo_MIITEM ON dbo_MIITEM.itemId = dbo_MIBOMD.partId)


LEFT JOIN dbo_MIBOMD AS MIBOMD2 ON MIBOMD2.bomItem = dbo_MIBOMD.partId AND MIBOMD2.bomRev = dbo_MIITEM.revId)
LEFT JOIN dbo_MIITEM AS MIITEM2 ON MIITEM2.itemId = MIBOMD2.partId)


LEFT JOIN dbo_MIBOMD AS MIBOMD3 ON MIBOMD3.bomItem = MIBOMD2.partId AND MIBOMD3.bomRev = MIITEM2.revId)
LEFT JOIN dbo_MIITEM AS MIITEM3 ON MIITEM3.itemId = MIBOMD3.partId)


LEFT JOIN dbo_MIBOMD AS MIBOMD4 ON MIBOMD4.bomItem = MIBOMD3.partId AND MIBOMD4.bomRev = MIITEM3.revId)
LEFT JOIN dbo_MIITEM AS MIITEM4 ON MIITEM4.itemId = MIBOMD4.partId)


LEFT JOIN dbo_MIBOMD AS MIBOMD5 ON MIBOMD5.bomItem = MIBOMD4.partId AND MIBOMD5.bomRev = MIITEM4.revId)
LEFT JOIN dbo_MIITEM AS MIITEM5 ON MIITEM5.itemId = MIBOMD5.partId


WHERE dbo_MIMOH.moStat = 1 AND dbo_MIMOH.mohId = 'US-1-0000002148';

This is the full query, I had removed a lot of it to try and pinpoint the issue. Once I added alias' 2 in the FROM portion, I got the error. When I changed to INNER JOINs, the error went away and I got information when the query was ran. I thought the issue was fixed at this point, so I filled out the rest of my query the way I wanted it and then I get no results.

[Micron]

I'd say I have no chance of figuring it out based on that - or maybe anything else. It may be one of those things that you have to build and test step by small step and add on when the last step works and backtrack when it doesn't. When I chimed in you had 19 views but no responses and I was hoping my suggestion would cause the offending part to get highlighted when you tried to swap views.

[aebstract]

I am going to go back to a smaller query that works and slowly try to build it up and look at what is going on, in the mean time the image attached will show this query ran through sql server management studio and the results it gives. Notice that there are 6 rows returned, 1 of the rows is almost a "duplicate" in that you can see line 2 and 3 having the same information in "part1" but it changes when it gets to "part2". This is important to keep structured like this, and I am wondering if this is something access is having trouble displaying? When I remove a lot of my joins and run the query, I get results with 5 lines. When I add the next join in is where I started to receive my error. I am going to try and look over everything and maybe I can come up with a solution, but I think this is the issue I am running in to and not 100% sure how to go about solving it currently.

[Micron]

Maybe I do have one last suggestion - stacked queries. That would be where a working query becomes a filtered 'table' that gets joined to other tables (or queries that filter values). Unfortunately, as I mentioned I'm not knowledgeable in SSMS but do know that Access syntax is not always compatible.
OK, maybe one other suggestion - why alias tables or fields (if that is, in fact, what you are doing in some places).
Hope you post the solution if you ever find one - especially if it's syntax related.

[aebstract]

The alias on the fields is for easier recognition in the data and when I go to loop through the data in vba it will be easier to handle. The alias on the tables is because I'm joining the same table repeatedly and am pretty sure they have to be aliased or it will error, right? I'm not sure about using stacked queries or if it would help me any, I'm looking in to it a little bit now.

[Micron]

Yes, if you join tables to themselves you must use aliases.

[aebstract]

I really think the issue is with it not splitting it out in to 6 rows of data like SSMS is, I don't know if there is a way to get access to do that?

[isladogs]

Is this a full outer join query in SSMS? If so, that query structure isn't supported by Access.
The work round in Access is to run the left outer join and right outer join separately and then union them together.

[aebstract]

The full query as it was wrote in SSMS is posted above, post #5. They are all LEFT JOINs. I will do some more research on all of the different join methods and see if I can get this to work but I have never used UNION to join anything together so I'll be going in a direction I'm unfamiliar with.

[isladogs]

Yes I saw that.
Without trying to be awkward, the fact that they are all written as LEFT JOIN doesn't necessarily mean the arrows are all in one direction.
Can you show a query diagram from SSMS.

This article from my website may be worth looking at http://www.mendipdatasystems.co.uk/q...pes/4594517491. Check out parts d) and h)

[aebstract]

As far as "query diagram" are you talking about the "execution plan" or something else? The execution plan is quite -wide- but I may be able to get it exported or something? I haven't really used SSMS too much in the past, but they had it set up at this job so I just use it as a way to write to our database if necessary and test queries before going in to access.

[isladogs]

No. I meant the diagrammatic representation of the SSMS view (query). For example



Did you look at my article in the link?