Best Practice for tables?

[Douglas J. Steele]

That means you don't often (or ever) use virtual tables. I couldn't
code one day in Access without writing SQL with at least one virtual
table, and queries with spaces in the names make that completely
impossible. You could get the same effect with the underscore
character.

Can you not just automatically put square brackets around all names?

 

[David W. Fenton]

What do you mean by "virtual table"?

SELECT tbl1.field1, vt.field2
FROM tlb1 INNER JOIN [SELECT tbl2.field2 FROM tbl2 WHERE ...]. AS vt

Because of the []. AS syntax, you can't have brackets inside there.

You can get around it for a while by using parentheses in some later
versions of Access (2000 and later) but if you save the query it
does get altered to brackets and you end up with the problem,
anyway.

 

[David W. Fenton]

Can you not just automatically put square brackets around all
names?

Not in virtual tables. Brackets inside the virtual table definition
cause the whole thing to fail.

Geez! I didn't realize so many people don't use them!

 

[David W. Fenton]

Why would I need to?

There are any number of circumstances where you might present a user
a list of queries and tables and need to do different things with
the choice, where you'd need to know the difference.

If you never do those, I guess it doesn't matter, but I just can't
get over the idea of putting spaces in the name of anything in
Access.

 

[David W. Fenton]

Speaking for myself I have never looked at a list of queries and
tables where I did not already know the name of the item I was
looking for. How else would I know which one to select? That
being the case, I already know whether it's a query or table.

I just fail to see where this is an issue.

You're depending on your memory and knowledge of the application to
supply for you the information that could be more plainly indicated.
I, for one, don't remember all the names of the objects in some of
my 10-year-old apps, nor recognize their functions. I'm very glad to
have an indication of what's a table and what's a query.

 

[David W. Fenton]

Almost the first thing I learned from one of my earliest Access
mentors was the "rename/copy" trick to ensure that I got the right
spelling of an object name to use elsewhere. It's become second
nature to the point where, even if I know the object name, I
usually do the rename/copy method almost out of habit.

I do that, too, and always have. I don't see that it has any bearing
on the question of meta information in the names of table and query
objects.

 

[Douglas J. Steele]

Not in virtual tables. Brackets inside the virtual table definition
cause the whole thing to fail.

Geez! I didn't realize so many people don't use them!

Now that I've seen your reply to Tony, I know what you're talking about (and
why you can't use square brackets).

You sure "virtual table" is a common way of referring to that? I've never
heard that term used for that scenario.

 

[Tony Toews [MVP]]

There are any number of circumstances where you might present a user
a list of queries and tables and need to do different things with
the choice, where you'd need to know the difference.

I do have the situation where I have a report selection criteria form
with a bunch of combo, list boxes, date ranges and such. I start off
with a combo box of all the report names that start with the word
"user". Once the user selects the form I then enable the controls
appropriate for that report. So yes I do occasionally present such a
list but I want the list of report names to be user readable so spaces
work in that situation.

(BTW I have a table that maps control names on reports to control
names on that form. So when I update a report or add a new report I
run a bunch of code which analyzes the reports control source queries.
This then enables/disables the controls on the above mentioned form.)

If you never do those, I guess it doesn't matter, but I just can't
get over the idea of putting spaces in the name of anything in
Access.

To each thier own.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

What do you mean by "virtual table"?

SELECT tbl1.field1, vt.field2
FROM tlb1 INNER JOIN [SELECT tbl2.field2 FROM tbl2 WHERE ...]. AS vt

Because of the []. AS syntax, you can't have brackets inside there.

You can get around it for a while by using parentheses in some later
versions of Access (2000 and later) but if you save the query it
does get altered to brackets and you end up with the problem,
anyway.

I don't have spaces in table or field names. In query, form and
reports I do. So this isn't a problem for me.

I must admit I've never quite comfortable with the sub queries as you
are using above so I don't use them. I know I should but never have.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

You sure "virtual table" is a common way of referring to that? I've never
heard that term used for that scenario.

To me those are sub queries.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Rick Brandt]

You're depending on your memory and knowledge of the application to
supply for you the information that could be more plainly indicated.
I, for one, don't remember all the names of the objects in some of
my 10-year-old apps, nor recognize their functions. I'm very glad to
have an indication of what's a table and what's a query.

But if you don't remember what the object is named but you DO prefix queries
with "qry" you STILL have to choose from amongst all of the objects so prefixed.
How do you do that unless you already know what you are looking for? Do you
just pick the one that seems to jog your memory?

Seems to me that one should figure out exactly which object they need (to the
point of examining its design) before they go picking it from some list.

 

[Keith Wilby]

I guess they must not have taught English at your engineering school.

Perhaps English isn't covered in his "large collection of reference books"
;-)

 

[Keith Wilby]

David,

Grow up FFS.

 

[Keith Wilby]

Whereas I would use

Customers
Customers - Payments sbf
Customers - Notes sbf
Customers - Invoices sbf
etc, etc.

FWIW I use

tblTableName

qryTableName
qfrmFormName
qcboComboBoxName
qrptReportName

etc.

 

[Arno R]

Grow up FFS.

Hi Keith,

I looked up FFS in at acronymfinder.com
You mean Fee For Service or For Further Study ??

Or maybe Fumble Finger Syndrome ??

Arno R

 

[John Marshall, MVP]

I highly doubt he went to engineering school. When I went through
engineering, one of the key courses and a thread that was present in most of
the other courses was ethics, something steve fails to understand.

John... Visio MVP

 

[Douglas J. Steele]

To me those are sub queries.

Yeah, that what I usually call them too (although technically subqueries are
a little different)

 

[Gina Whipp]

You forgot.... For Freak's Sake (polite term)

Great site Arno!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Grow up FFS.

Hi Keith,

I looked up FFS in at acronymfinder.com
You mean Fee For Service or For Further Study ??

Or maybe Fumble Finger Syndrome ??

Arno R

 

[Jamie Collins]

What do you mean by "virtual table"?

SELECT tbl1.field1, vt.field2
FROM tlb1 INNER JOIN [SELECT tbl2.field2 FROM tbl2 WHERE ...]. AS vt

a.k.a. derived table.

Because of the []. AS syntax, you can't have brackets inside there.

You can get around it for a while by using parentheses in some later
versions of Access (2000 and later) but if you save the query it
does get altered to brackets and you end up with the problem,
anyway.

You can bypass this behaviour by creating the SQL object using SQL DDL
i.e. CREATE VIEW (a.k.a. 'virtual table'!) or CREATE PROCEDURE as
appropriate.

Jamie.

--

 

[Tony Toews [MVP]]

Yeah, that what I usually call them too (although technically subqueries are
a little different)

They are?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/