Best Practice for tables?

[Douglas J. Steele]

They are?

See what Allen Browne's got at http://www.allenbrowne.com/subquery-01.html

Now that I've seen it, I like Jamie's terminology of "derived tables".

 

[Tony Toews [MVP]]

See what Allen Browne's got at http://www.allenbrowne.com/subquery-01.html

I'm confuse. Allen Browne's page are my definition of subquery as is
David Fenton's. So I don't see how they are "a little different"

Now that I've seen it, I like Jamie's terminology of "derived tables".

I don't. Let's stick with Microsoft terminology. From A97 help.

"A subquery is a SELECT statement nested inside a SELECT,
SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside
another subquery."

....

SELECT * FROM Products
WHERE UnitPrice > ANY
(SELECT UnitPrice FROM OrderDetails
WHERE Discount >= .25);

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/

 

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

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.

Are you assuming that one only ever uses tables and never any
queries in these virtual tables?

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.

Well, if your queries have spaces in the names, it would make it
pretty much impossible, which would be something to generate
discomfort, no doubt.

 

[David W. Fenton]

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

Exactly. Using a subquery result for a column or in criteria (as in
IN operations) is not at all the same thing as a virtual table. And,
of course, correlated subqueries are another thing entirely.

I have always used the term myself and I know others use it (there
was a thread on it using the term in one of the Access groups just
this past week), and I've seen it used by people using other
dialects of SQL (including non-MS dialects), but I know there's
another term for it (subselects?), but can't quite dredge it up from
memory.

"Virtual table" describes the way it works, as it is a case where a
SQL SELECT is used to replace what would otherwise be a table or
query name in the FROM clause. It makes it possible to do in a
single on-the-fly SQL statement what would normally require a saved
query to do. I most commonly use it in the FROM of GROUP BY queries,
where putting your criteria for one table messes up the results of
what you're aggregating.

(I actually did that too much back in the day, because I never
realized you could have WHERE criteria -- I thought you could only
have HAVING criteria; this was because I learned everything I knew
about this up to that point from using the Access QBE, and the WHERE
choice in the dropdown was way at the end and I'd just never seen
it! But some GROUP BY operations still require a virtual table or a
saved query to get the right results, especially when the virtual
table itself is a GROUP BY and has variable criteria -- that can
never be done with save QueryDefs, well, unless you use parameters,
I guess, and even then, it's not always possible).

 

[David W. Fenton]

See what Allen Browne's got at
http://www.allenbrowne.com/subquery-01.html

Now that I've seen it, I like Jamie's terminology of "derived
tables".

Yes, that's the term I was trying to come up with.

 

[Tony Toews [MVP]]

Are you assuming that one only ever uses tables and never any
queries in these virtual tables?

No, that's not what I said.

Well, if your queries have spaces in the names, it would make it
pretty much impossible, which would be something to generate
discomfort, no doubt.

True, but then I'd end up changing the query name so it didn't have
spaces so it would work.

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/

 

[David W. Fenton]

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.

I do this by maintaining a table that maps real report names to list
name (with spaces) and a human-friendly description of the report.
The table also records how to launch the report (call it directly,
open a dialog form that then will in turn open the report, or
execute code that does all of this). It's part of every report-heavy
application I ever distribute.

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

So, you're doing with controls the most basic part of what I do with
report names. Shouldn't be much of a stretch for you to do it with
reports, then.

You could also use things like the report description field to
provide a report name.

To each thier own.

It seems to me that you make more problems for yourself than you
solve. To me, having to use brackets all the time is *much* worse of
a maintenance issue than translating the coder-friendly names into
human-friendly names.

Hmm. JUst off the top of my head here, seems to me that one could
easily convert a camel-case report name into a human-friendly one
with spaces:

rptReceivablesAging

You'd strip off the first 3 characters with Mid() and then put a
space before every capital letter, and then trim off the first
space.

The only downside of this would be that some words that should be
lower case would be capitalized:

rptEventDetailByChair

should really come out for humans as:

Event Detail by Chair

So, your conversion function could convert certain words to lower
case (by, of, the, to, etc.).

Problem solved for both users and coders, and without needing to
maintain a translation table or extra properties -- all you'd need
to do is follow your naming conventions and camel case everything at
each word break.

 

[David W. Fenton]

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?

Well, I don't name then qry1, qry2, qry3, etc.

After qry, the name is just the same as the rest of you might use,
so the list presents itself in alphabetical order.

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.

Yes, and if all of your queries are prefaced with qry, they will
sort by name.

But, again, you need to distinguish somehow between tables and
queries that have similar names. My example was in a secured
application where you'd have tblPerson, which is secured, and
qryPerson which is simply "SELECT tblPerson.* FROM tblPerson WITH
OWNERACCESS OPTION". You'd definitely want to use the query in all
your user interface objects, though in many contexts you might very
well still use the underlying table, depending on your security
setup. If you completely lock the user out of even read-only access
to the underlying table, you would always use qryPerson.

In conversion situations, where I have to support some things for
backward compatibility, but want to build on a solid situation, I've
even taken existing tables and written queries that I name
"tblWhatever," because I know that someday, the tables used to
create the query named "tblWhatever" will eventually be replaced by
an actual table (at which time the query that impersonates a table
will be deleted.

In tables, the prefixes allow me to segregate tables by function
(the two most common I use are tbl and tmp), and I always know what
I'm looking for. I don't segregate query types at all, though.

 

[David W. Fenton]

What does "snarky" mean?

Google "define:snark".

 

[Tony Toews [MVP]]

It seems to me that you make more problems for yourself than you
solve. To me, having to use brackets all the time is *much* worse of
a maintenance issue than translating the coder-friendly names into
human-friendly names.

Not at all. If it was a PITA putting in square brackets then I would
have noticed. It's not.

When I need an object name I go to the database container window,
pretend I'm going to rename the object and grab the name. I'm done.
What could be simpler?

Problem solved for both users and coders, and without needing to
maintain a translation table or extra properties -- all you'd need
to do is follow your naming conventions and camel case everything at
each word break.

But not worth it to me. My system works and it's no trouble at all.
You're trying to solve a problem that doesn't exist.

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]]

Don't bother with the tbl prefix. Basically it's a waste of time.

Another thing I came across that I instinctively do. When adding a
query while in the Query Builder I just hit the first letter of the
name of the query and zoom I'm there. No need to scroll, scroll,
scroll.

This works in all kinds of Access wizard type of list boxes.

Now what I could also do is resize the query window and drag and drop
from the database container window but that's a bit more awkward for
me.

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]]

K

Who would've thought one simple question would bring up, so far 83
postings. Congratulations.

I decided to consolidate my postings and create a web page.
http://www.granite.ab.ca/access/tonysobjectnamingconventions.htm

I also blogged it at
http://msmvps.com/blogs/access/archive/2007/08/25/tony-s-object-naming-conventions.aspx.
It'll be interesting to see what comments I get there.

(Note that I blog almost all changes, unless minor, to my website so
that folks can see what's new or changed.)

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/

 

[David W. Fenton]

I don't. Let's stick with Microsoft terminology.

But that terminology doesn't distinguish this particular type of
subquery from all the others, and I think it needs a name, as it's
doing something very different from the others, seems to me.

 

[Tony Toews [MVP]]

But that terminology doesn't distinguish this particular type of
subquery from all the others, and I think it needs a name, as it's
doing something very different from the others, seems to me.

From what I recall I don't see any difference between your usage of
subqueries and the example from the A97 help.

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/

 

[Keith Wilby]

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

Thanks for jumping in there Gina, been away for a few days

 

[Jamie Collins]

Yes, that's the term I was trying to come up with.

FWIW (quite a lot IMO) it's the term used in the ANSI/ISO SQL-92 spec.

Jamie.

--

 

[Jamie Collins]

Not at all. If it was a PITA putting in square brackets then I would
have noticed. It's not.

I too find it a PITA to add square brackets (a proprietary feature;
Standard SQL uses double quotes). Are you the only person who will
*ever* write SQL against your mdb's? I always consider the person who
will 'inherit' my code as well as the current and future users. What
about when you ask for help in the 'groups <g>?

Jamie.

--