MS Access 2016 - SQL copy paste issue between 2 databases

[DaveLembke]

So I have a final that I am working on and just about got it done. Went through to make sure that all was well with some forms and some fields in one of the forms shows that there is an issue now. Not sure how the issue happed as the tables and relationships haven't been altered in any way, but I have like 60 saves going back to the very beginning of my final project and the 5th save back doesn't have the issue.

So I was like I just need these 5 querys added to the older copy of the database where the form doesn't have an issue, so I copy pasted the SQL info from SQL view on the 5 querys on the newest database where the querys are good. Attempted to copy/paste the SQL info to a new query on the older database so I don't have to manually reconstruct the 5 querys all over again, and it gives me an error that states that there is a syntax error with the SQL information.

I looked at the pasted SQL information and its all correct and its a exact match between the source where it came from where it was working with no problems and the destination where I placed it into a new querys SQL view to make the query follow instructions that I know are good.

However I keep getting this error with syntax which makes no sense. Was wondering if anyone has any suggestions on how to get around this?

Worst case scenario I write down everything onto paper and reconstruct the 5 querys all over again. But it really bothers me that I believe I should be able to take good SQL instructions for the query from a known working database and bring it to an earlier version of itself where dependencies are a exact match and it should work, however its really messed up that its giving me an error about syntax. I have a screen shot of it on my computer at home and wish I had that with me to show the exact message. I could share that later today if needed to show the actual complaint by Access 2016.

The good thing is that the final is due on 8/7 and its currently 8/3 so I have some time, but Access 2016 has been a nightmare all semester with stuff breaking without reason and me basically going back to an earlier saved state and performing exactly the same thing forward that lead up to it breaking and it then works and I save it as a new rev number knowing that a crash or error or something is bound to happen again and its nice to have restore points from known good database states when it breaks to not have to fight it out with an unstable Access 2016 on multiple computers using Access 2016 as supplied by the College.

[DaveLembke]

Here is the exact error message which i believe is complaining about syntax when the syntax is correct.

[BC_Programmer]

I'm not sure how well Access SQL supports SQL but at least with the ones I'm familiar with, using a subquery like that requires an alias. (It also needs to be it's own query-  though again maybe Access does something weird that lets it understand "Brands INNER JOIN ..." as a subquery without a select clause?)

You should be able to use multiple Joins on the same query, so something like this might work:

Code: [Select]

select Products.Item, Products.UPC, Products.Price,Types.Type,Brands.BrandName
From Types
INNER JOIN Products ON Products.[TypeID]=Types.[TypeID]
INNER JOIN Brands ON Brands.[BrandID]=Products.[BrandID]


[DaveLembke]

Thanks BC for help with this. 

Will see if its happy with this:

select Products.Item, Products.UPC, Products.Price,Types.Type,Brands.BrandName
From Types
INNER JOIN Products ON Products.[TypeID]=Types.[TypeID]
INNER JOIN Brands ON Brands.[BrandID]=Products.[BrandID]

Its the oddest thing that the SQL query instructions are from a newer version of the same exact database that they function properly in. And when copy/pasting to an earlier version of the database in which the Tables, Data within, and Relationships are the same between earlier version and latest version, that it would be broken for all 5 queries.

I was thinking that I'd save myself time just pasting the SQL from a newer version of itself to an older version of it and not have to manually recreate the queries all over again.

Will test what you suggested and will report back. Thanks Man! 

[DaveLembke]

Gave it a try and got an error with syntax again.

But also just want to let you know that I just ended up manually creating the queries all over again from scratch because Access doesn't seem to like SQL instructions pasted to it I guess. If Access 2016 would have accepted the copy/paste of good querys it would have been a quick fix, but because it has a fit with it, I decided to try your suggestion and then recreated them from scratch by looking at one computer with newer database and another computer with older database and then just recreated them all and then I was all set.