Writing SQL (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 03:32
Joined
Oct 30, 2008
Messages
1,257
With a Select statement, do you really need TableName.FieldName ?
It seems to work with just the field name, but maybe there's a downside to this ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:32
Joined
Oct 29, 2018
Messages
21,358
You only need to add the table name if you have same name fields in multiple tables. That's why you might see something like this:
SQL:
SELECT Field1, Field2, Field3
FROM Table1
INNER JOIN Table2
ON Table1.ID=Table2.ID
The above assumes Table1 has the following fields: ID, Field1, and Field2
And, Table2 has the following fields: ID, Field3
 

kirkm

Registered User.
Local time
Tomorrow, 03:32
Joined
Oct 30, 2008
Messages
1,257
Thank you DBGuy, that's good to know. Changing it now.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:32
Joined
Oct 29, 2018
Messages
21,358
Thank you DBGuy, that's good to know. Changing it now.
You're welcome. However, I just did a quick test, and I was off a little bit. It seems the JOIN expression requires the table names. Only the SELECT part doesn't. So, the ON clause has to be like this, even if the field names are different. Otherwise, you'll get a "JOIN expression not supported" error.
SQL:
ON Table1.PK=Table2.FK
Cheers!
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 14:32
Joined
Feb 19, 2013
Messages
16,555
other tip to save a lot of typing and making the code easier to understand if you have long table names and/or duplicate field names is to use short aliases for the tables.

To paraphrase DBG's example

Code:
SELECT A.ID, Field1, Field2, Field3
FROM Table1 A
INNER JOIN Table2 B
ON A.ID=B.ID

Note that if you return to the QBE and either save or execute the query, Access will automatically put the table names back - or the aliases if used

Code:
SELECT A.ID, A.Field1, A.Field2, B.Field3
FROM Table1 A
INNER JOIN Table2 B
ON A.ID=B.ID

but still better than

Code:
SELECT Table1.ID,Table1.Field1, Table1.Field2, Table2.Field3
FROM Table1 
INNER JOIN Table2
ON Table1.ID=Table2.ID
 

kirkm

Registered User.
Local time
Tomorrow, 03:32
Joined
Oct 30, 2008
Messages
1,257
I've removed all table names. Aren't using a join (just one table in the query) so should be ok but stuck on something else now!
CJ Sorry I don't understand that A B example.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:32
Joined
Oct 29, 2018
Messages
21,358
I've removed all table names. Aren't using a join (just one table in the query) so should be ok but stuck on something else now!
CJ Sorry I don't understand that A B example.
Good luck!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:32
Joined
Jul 9, 2003
Messages
16,245
With a Select statement, do you really need TableName.FieldName ?

As already explained here, not for single table statements.

For a Nifty way of shortening your SQL Statement, have a look at my "Nifty Tip" about the 6th or 7th down:- "Take out the Tables"

Here:-

 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:32
Joined
Jul 9, 2003
Messages
16,245
Another Tip!

MS Access Programmers don't write SQL

How on earth can anybody write these long complicated SQL strings? Well I’ll tell you a secret, I don’t! And even better, you don’t have to either!

For more info see the "Nifty Tip":-

"How to Get the Text Version of a Query"

Here:-
https://www.niftyaccess.com/nifty-tips/
It's the 4th one up from the bottom...
 

kirkm

Registered User.
Local time
Tomorrow, 03:32
Joined
Oct 30, 2008
Messages
1,257
Thank you Uncle Gizmo, I do have to create some long abd xomplex queries as part of this current project. So will be studying those then. And maybe Aliases too. I'm sure they've got merit but I've not missed them so far. I only have one table too, so no Joins etc.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:32
Joined
Jan 20, 2009
Messages
12,849
Access is an absolute crap platform to write SQL. The mandatory nested join structure is horrible and Access will trash any formatting which is the key to writing complex SQL. When I used to write complex SQL in Access I would Union a dummy query at the end so it would leave the formatting alone when it saved.

I write masses of SQL directly in SQL Server Management Studio. It has fabulous Intellisense and live syntax checking. Once you have experienced that environment you would never want to return to writing queries in Access. Especially with the vastly larger vocabulary and available constructs in TSQL.

The biggest query I have written in MSSMS is somewhere around 1500 lines long (including while space to make it readable). I never use its query builder.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:32
Joined
Feb 19, 2002
Messages
42,984
QBE certainly leaves a lot to be decided but i don't ever write embedded SQL unless I have to. Static SQL can easily be generated by the QBE as long as your where clause isn't too complex. If you are using querydefs, you don't care what a mess Access makes out of the formatting. All you care about is that you can build a query just by point and click in a matter of seconds and not have to memorize the table and column names of hundreds of tables and thousands of columns or worry about typos. It's a tradeoff. If I have complex SQL that I don't want Access to mess up, I switch to SQL view and format it. Then save the query without switching to QBE view. Access will not reformat the SQL unless you switch to QBE view. I keep a table to store formatted SQL in so in case I lose my mind and switch to QBE view for one of these queries, I can get it back from the table.

The only time I ever use embedded SQL is if I am building a dynamic query as I might for a search form. Back in the 80's when I first started writing SQL, I used to fantasize about having a tool like the QBE and this was before PC's let alone Access:). Why would I ever bother to write queries myself when I have a tool like the QBE, substandard though it is.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:32
Joined
Jan 20, 2009
Messages
12,849
What is "QBE" ?
Query By Example.
 

Users who are viewing this thread

Top Bottom