Unsure about a part of a code for a select query

[DBlearner]

Dudes, you should check this out! I just downloaded a MS Access database titled "Sales Pipeline" and it's a great way of learning codes to do charts on the fly. I'm reading the codes and checking reference books, google, etc to understand what they mean and do. That way I can build my own and actually understand the underlying structure.

I'm trying to find some clarifications that the reference books are not on one code. This code is the row source for a combo box.

SELECT   [Customers Extended].ID,
         [Customers Extended].[Contact Name],
         [Customers Extended].Company
FROM     [Customers Extended]
ORDER BY [Customers Extended].[Contact Name];

Ok, the code clearly says that it is a select query and first part is selecting a table "Customers Extended" and going to the ID column. My first question is what does the dot just before ID mean?

Next part is more confusing, because it points to three queries separated by a dot. What exactly does this do? As for the rest, I understand their purpose.

Any insights would be so helpful.

Dang, I wish MS Access would give detailed instructions on how they built these awesome basic DBs.

Matt

[ChipR]

The period character is simply used as a separator between the table name and the field name. In SQL, the [ ] characters are not required unless you need to delimit a table or field name with spaces in it.

So imagine I have a table named myTable and it had field1, field2, field3. I can select all three fields by writing

SELECT Field1, Field2, Field3 FROM myTable

Now what if I have a otherTable, and it has field1 in it? I can't use

SELECT Field1, Field2, Field3, Field1 FROM myTable, otherTable

Access would yell at me and say, I don't know which table you mean when you write "Field1" because both myTable and otherTable have a Field1.
So, I might try to specify which table the field comes from with

SELECT myTableField1, otherTableField1...

Clearly, that's not right. The SQL engine is going to assume that myTableField1 is the name of a field. I need a period to separate them.

SELECT myTable.Field1, otherTable.Field1...

And that works. Now if the table name was "my Table" I can't write

SELECT my Table.Field1...

Once again the engine is going to look for a field called "my". This is when the braces are required, though it doesn't hurt to use them regardless, and many people believe they make the SQL easier to read.

SELECT [my Table].Field1...

Hope that helps some.

[NeoPa]

Perfect clarity :) Nice one again Chip.

As an illustration, I will post your SQL in a shorter, more readable form, that Access cannot do for you easily as it must allow for more complicated situations (and it's just not that good at making the SQL very clear) :

SELECT   ID,
         [Contact Name],
         Company
FROM     [Customers Extended]
ORDER BY [Contact Name]

Chip touched on the situation where multiple tables or record sources are included in the SQL. In such a situation it is probably wize to clarify each reference with the name of the record source (in this case a table). When a single source is involved it's generally clearer only to list it in the FROM clause.

BTW does this answer all your questions. It seems to but if there's anything more you need clarified we're happy to help.

[DBlearner]

Chip & Neo,

Your explanations went beyond what I was hoping to get! Thank you so much for taking the time to explain the structure of how people write/read codes.

Now, I am starting to become pretty good at understanding relatively large chuck of codes. I'm starting to see SQL is a real language, albeit with more strict rules compared to English.

How long do you think it'll take a person to be fluent in SQL?

Matt

[ChipR]

I still use the design view for Access queries when I can, because it's so much easier. I have no idea how long it will take to become fluent in SQL. I've been at this for more than a year and a half, and anything beyond simple select statements can take me some time to formulate correctly. Don't be too hard on yourself :)

[NeoPa]

I only ever use the SQL view for designing queries when I need to do something Access doen't handle easily.

If I'm trying to do something clever in SQL I will generally use a text editor with some decent features. ...And a non-proportional font. I think clarity is so helpful when dealing with SQL (well, any language really).