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.

 

[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