[X]My Assistant

[mrpersonality]

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]

[June7]

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

[mrpersonality]

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

[projecttoday]

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).

[mrpersonality]

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

[projecttoday]

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

[projecttoday]

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.

[dmhzx]

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

[projecttoday]

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

[PaulBrand]

Bracket [, Brace {, Parenthesis (

[dmhzx]

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.

[GroverParkGeorge]

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" .

[GroverParkGeorge]

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.

[projecttoday]

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