Writing SQL

[kirkm]

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]

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:

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]

Thank you DBGuy, that's good to know. Changing it now.

 

[theDBguy]

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.

ON Table1.PK=Table2.FK

Cheers!

 

[CJ_London]

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

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

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

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

 

[kirkm]

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'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!

 

[CJ_London]

don't understand that A B example.

don't worry - you will do one day

 

[Uncle Gizmo]

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:-

Nifty Tips - Nifty Access

Nifty Video Tips Using an excellent new feature of Elementor Page Builder I am now able to show you time indexed clips from my MS Access Videos. This way you can quickly and easily see a video demonstration of just a few minutes, a Demo that gives you a leg up in your MS Access

www.niftyaccess.com

 

[sonic8]

CJ Sorry I don't understand that A B example.

A and B are aliases defined for the table names which can thereafter be used instead of the full/original table names.

 

[Uncle Gizmo]

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]

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]

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]

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.

 

[kirkm]

What is "QBE" ?

 

[Galaxiom]

What is "QBE" ?

Query By Example.

Query-by-Example in Microsoft Access

The objective of this activity is to learn how to query the database using Microsoft Access’s query-by-example (QBE) feature. (This practice is a warm-up for later discussion of SQL queries.) In particular, you are to do the following:

usafa-compsci364.github.io

 

[CJ_London]

also

Query Builder Environment