Best Practice for tables?

"Douglas J. Steele" wrote:

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.

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/

"Tony Toews [MVP]" wrote in message
...
"Douglas J. Steele" wrote:

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.

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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Douglas J. Steele" wrote:


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.

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

They are?

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/

"Tony Toews [MVP]" wrote in
:

"David W. Fenton" wrote:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Douglas J. Steele" wrote in
:

"Tony Toews [MVP]" wrote in message
...
"Douglas J. Steele" wrote:

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.

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Douglas J. Steele" wrote in
:

"Tony Toews [MVP]" wrote in message
...
"Douglas J. Steele" wrote:

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.

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

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

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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" wrote:

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?

No, that's not what I said.

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.

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/

"Tony Toews [MVP]" wrote in
:

"David W. Fenton" wrote:

Look at the object names. Queries have spaces in them and are
longer. Tables don't.

Huh. I didn't notice.

How do you process a list of tables and queries in code, then?
Do you use InStr()?

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.

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.

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.

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Rick Brandt" wrote in
:


"David W. Fenton" wrote in message
. 1...
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?

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Steve" wrote in
:

What does "snarky" mean?

Google "define:snark".

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/