How code two LEFT JOINS in UPDATE query ???

[ZABADAK]

I have tried everything I can think of and get syntax errors. I need to do

UPDATE tblA LEFT JOIN tblB ON tblA.X = tblB.X LEFT JOIN tblC on tblA.Y = tblC.Y
SET ...,
SET ...,
etc. etc.

 

[Remou]

Hi
I got a syntax error with a similar query when I left out the brackets. This seems to work:
UPDATE (tblA LEFT JOIN tblB ON tblA.X = tblB.X) LEFT JOIN tblC on tblA.Y = tblC.Y

 

[BNPMike]

This is Access. Build your queries in the Query Builder. I can't remember it ever making a syntax error.

 

[ZABADAK]

Thanks for the input. I never use the query builder. Never have, never will. I would have much less idea how to do it in the query builder than in raw SQL.

 

[lespaul]

unfortunately, typing apparently valid SQL syntax in Access won't work all the time. Access likes lots of parens in it's joins. I have never been able to figure out how they go together.

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[BNPMike]

I would have much less idea how to do it in the query builder than in raw SQL.

It would have taken about 60 seconds to generate your query with QBE. It took you longer than that to type your posting on this forum. I admire you for doing things manually, but only if you succeed.


QBE is I believe one of the greatest inventions in the History of Computing. Microsoft weren't the inventors but the Access impementation is the best I've seen so far.

As for speed. QBE is like a modern F1 racing car compared to SQL: something from the 50s.

 

[ZABADAK]

None of my SQL books mention QBE. I thought QBE went out with Borland Paradox. I code in raw SQL because I switch between many languages and development environmants and a knowledge of the Access query builder is just redundant effort for me.

 

[jsteph]

...just another opinion here...
I used to think that writing SQL manually was the best and only way--in part because I did work in Oracle, (which didn't use the JOIN keyword), and other dbms's as well as Access.

And it was a good exercise--but now I use Access as a tool when developing in any language with any backend. Partly for browsing data while developing, but also partly for building sql statements with the query builder--especially long and complex ones. It has saved me so much time, and I don't give a hoot if it's not for 'purists'. In the time it takes to hand-type in a sql statement that updates 40 fields in a table I could make 20 of them in the QBE--plus have time for a coffee break.

Sure, if I'm doing a quick update or select in SQL-server's query-analyzer or Oracle's sql-plus--I'll hand type it. But for anything more--I see it almost like a calculator: I'll add 2+2 in my head, but if I'm going to multiply a few 3-digit numbers--why be a purist and do it by hand when you've got a calculator?
--Jim

 

[BNPMike]

If you're not familiar with QBE within Access then most likely you don't know about the many other programmingless features Access has. It's worth getting a book.