UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Query Syntax, Access 2016    
 
   
mrpersonality
post Oct 22 2019, 09:41 PM
Post#1



Posts: 476
Joined: 4-December 02
From: NSW Australia


I have worked with access for over 10yrs and only just discovered this - wish I had known way earlier
heres 2 querys - they both work ,will leave it up to you to decide which one you would rather use,however why use all the brackets and the explicit table names when its not required
the second option allows for easy adding more criteria without having to worry about brackets


SELECT Brands.ID, Brands.[Brand Name], Brands.Type
FROM Brands
WHERE (((Brands.ID) Is Not Null)
AND ((Brands.Type)="1"))
ORDER BY Brands.[Brand Name];


SELECT ID,[Brand Name],Type
FROM Brands
WHERE ID Is Not Null
AND Type ="1"
ORDER BY [Brand Name]

Go to the top of the page
 
June7
post Oct 22 2019, 09:47 PM
Post#2



Posts: 1,015
Joined: 25-January 16



Access query builder throws in a lot of unnecessary brackets and parens and table/query name prefixes. Just the way it is.

Advise not to use spaces nor punctuation/special characters in naming convention nor reserved words as names.

This post has been edited by June7: Oct 22 2019, 09:48 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
mrpersonality
post Oct 22 2019, 09:54 PM
Post#3



Posts: 476
Joined: 4-December 02
From: NSW Australia


apart from the unnecessary stuff,reading the code is way more simple

Go to the top of the page
 
projecttoday
post Oct 23 2019, 12:38 AM
Post#4


UtterAccess VIP
Posts: 11,279
Joined: 10-February 04
From: South Charleston, WV


QUOTE
why use all the brackets

There is only one pair of brackets (used twice) in your first example and you repeated those in the second example (which you should have since it's a field name with a space in it).

--------------------
Robert Crouser
Go to the top of the page
 
mrpersonality
post Oct 23 2019, 12:44 AM
Post#5



Posts: 476
Joined: 4-December 02
From: NSW Australia


semantics
I should have said parenthesis ,square brackets,semi colons all unnecessary syntax
of course the square brackets in this case are necessary-because there is a space,but not necessary when there is no space
Go to the top of the page
 
projecttoday
post Oct 23 2019, 12:49 AM
Post#6


UtterAccess VIP
Posts: 11,279
Joined: 10-February 04
From: South Charleston, WV


Oh, yes, it would be nice not to have those unnecessary parentheses.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Oct 23 2019, 12:54 AM
Post#7


UtterAccess VIP
Posts: 11,279
Joined: 10-February 04
From: South Charleston, WV


Table names are necessary, of course, to remove ambiguity in joins. Even in joins Access / Ace doesn't require them if the same field name doesn't appear in more than one table.

Without the table names it would be possible to have a working query break down by adding an identical field name to one of the tables. A long shot, but possible.

--------------------
Robert Crouser
Go to the top of the page
 
dmhzx
post Oct 23 2019, 02:47 AM
Post#8



Posts: 7,115
Joined: 22-December 10
From: England


The reason for it is that Access creates the query SQL based upon the graphical query builder, and it is designed to be failsafe.

If you use saved queries then the unnecessary bits don't matter, since you may well not want to see the SQL at all.

If you want to put the SQL into your VBA, then leaving out the superfluous stuff improves readability no end.

I tend to use a mixture especially on those occasions when I want some more complex joins , and can take advantage of the fact that with Access you can use a query as an input to another.

I had one requirement a while ago that had to make a report from several different group by queries, and the query that ran the report used eight other queries as it's input. I don't think I cold ever have done that by writing my own SQL.

Another use for saving the query is where you want to build a user reporting form.

You can save a generic query that has all the field and layout you want, and pop up a screen for the user to select the criteria.

When they're ready and click the run button, your code the preparation of a where clause and

CODE
"Select * from SavedQuery "  & strWhere


produces the report


As to whether or not () are brackets or not, To me they are, - From Mathematics mnemonic "BOMDAS". These [] are square brackets to me, and these {} are curly ones.

And I do always put square brackets round field names even thought there isn't a space in them . --- Back to readability again.

But that's just my personal preference



Go to the top of the page
 
projecttoday
post Oct 23 2019, 04:29 AM
Post#9


UtterAccess VIP
Posts: 11,279
Joined: 10-February 04
From: South Charleston, WV


So if ( is a bracket and [ is a square bracket, does the word parenthesis even exist in the queen's English?

--------------------
Robert Crouser
Go to the top of the page
 
PaulBrand
post Oct 23 2019, 05:04 AM
Post#10



Posts: 1,747
Joined: 4-September 02
From: Oxford UK


Bracket [, Brace {, Parenthesis (

--------------------
Paul
Go to the top of the page
 
dmhzx
post Oct 23 2019, 07:05 AM
Post#11



Posts: 7,115
Joined: 22-December 10
From: England


Nothing wrong with the word Parenthesis if you can type it correctly first time



As to what we call things, it's probably cultural

Like Math or Maths

Fanny means very different things on different sides of the Atlantic. (as do Pants, and suspenders)

In the UK mathematicians use BOMDAS as a rule for the sequence of operations. I guess in the US they use POMDAS. (Or PEMDAS0



But we all knew what the question meant.



Go to the top of the page
 
GroverParkGeorge
post Oct 23 2019, 10:10 AM
Post#12


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


IMO, the key thing is that most of the time we are aware of the potential differences and don't automatically leap to the conclusion that the OP is using a term "wrong" .

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 23 2019, 10:16 AM
Post#13


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Actually, it's probably a good idea to retain table names most of the time when joining tables in a query, although in a single table query like that it isn't a big deal to omit them.

Consider:

SQL
SELECT tableone.ID, tabletwo.ID, tableone.FieldOne, tableTwo.FieldOne
FROM tableone Inner Join tabletwo ON tableone.ID = tabletwo.ID


Obviously, when you have two (or more) tables in a query, you must let the query engine know which fields to use. And that means the table names are required.

In addition, you can alias your table names:

SQL
SELECT t1.ID, t2.ID, t2.FieldOne, t2.FieldOne
FROM tableone t1 Inner Join tabletwo t2 ON t1.ID = t2.ID


Some people find the aliased table names easier to read, others seem to find them harder. I think it is, in part, what you are used to.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Oct 23 2019, 03:25 PM
Post#14


UtterAccess VIP
Posts: 11,279
Joined: 10-February 04
From: South Charleston, WV


Table names are not required unless the same field name is used in more than one table.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 08:24 PM