Re: Best Practice for tables?
![Arvin Meyer [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjWNeBaELXBA4T-Yh0e0NpfK3HiG4ga607fRgadqcjevhVaQ=s40-c)
Arvin Meyer [MVP]
the 3 tables, use the ID or Primary Key value.The tblRoutingSystem should
contain:
CountryID
DispatchMethodID
SupplierID
Now if the Supplier decides to sell the company or change its name, you
simply change it once in the Supplier table, and it propogates throughout
the database from the queries used to display the SupplierName.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"Kamitsukenu" <Kamit...@discussions.microsoft.com> wrote in message
news:72266BF7-C758-4089...@microsoft.com...
> Hi there,
>
> The company I work for mail items dependent on destination, weight and
> speed
> of delivery.
>
> I'm trying to make a system in Access, and during setting up all the
> tables,
> I'm getting myself more and more confused.
>
> I have the following tables;
>
> (tblCountry) - holds the Country names
> (tblDispatchMethod) - the service the item is sent by
> (tblSupplier) - who the item is mailed by
>
> Now, as dispatch of items are dependent on the weight, I have another
> table
> which combines all the above information.
>
> TblRoutingSystem contains
> 'CountryName' which refers to 'tblCountry'
> 'DispatchMethod' which refers to 'tblDispatchMethod'
> 'NameofSupplier' which refers to 'tblSupplier'
> 'WeightBandStart' and 'WeightBandEnd' which refer to (surprise, surprise)
> the start and end of the weight bands for deciding the correct supplier
> for
> mailing.
>
> Does this sound about right, or should I be split them out further or
> should
> I be merging more stuff together.
>
> in fact, can someone go through the pros and cons of creating more tables
> to
> suit the data?
>
> Thanks
>
> K
>
>
Steve
A service I provide is to design the table structure of a database for a
customer. I have done this for numerous customers. My fee is very
reasonable. I provide a map of the tables that shows all the tables in the
database, all the fields in each table, all the relationships between the
tables and the type of relationship for each relationship. The tables are
arranged on the map generally as the flow of information in the database. I
create a map of the tables for every database I do. The map visually shows
what forms and subforms are needed for data entry, shows what special forms
and subforms can be created for dispaying data in the database and shows
what reports and subreports can be created from the data in the database.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"Kamitsukenu" <Kamit...@discussions.microsoft.com> wrote in message
news:6277990C-CB4A-4D51...@microsoft.com...
> Hi Arvin,
>
> Thanks for clearing that one up for me Arvin. You're right though, it
> does
> seem too simple!
>
> Kind Regards,
>
> John
John Marshall, MVP
snake oil. There was nothing in Kamitsukenu's post that indicated he was
interested. It was a simple thank you to Arvin.
Please take your solicitations and phony testimonials somewhere else.
John... Visio MVP
"Steve" <so...@private.emailaddress> wrote in message
news:13cjndj...@corp.supernews.com...
> FYI in case you are interested ---
>
> A service I provide is to.....
> PC Datasheet
StopThisAdvertising
"Steve" <so...@private.emailaddress> schreef in bericht news:13cjndj...@corp.supernews.com...
FYI in case you are interested ---
100 First Time visitors only last week, 235 pageloads
You certainly get a lot of attention Steve...
Hmmm... do you also get lots of new customers ??
--
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3500+ pageloads, 2300+ first-time visitors (these figures are rapidly increasing)
Arno R
![Tony Toews [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjVFNFhAO8kxGtutFBSPYzx5Lbrrty7WRKPHNhjuWtTEHvsoQdk=s40-c)
Tony Toews [MVP]
>(tblCountry) - holds the Country names
>(tblDispatchMethod) - the service the item is sent by
>(tblSupplier) - who the item is mailed by
Don't bother with the tbl prefix. Basically it's a waste of time.
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]
>My fee is very reasonable.
Please stop soliciting customers in the forums.
Steve
QryDispatchMethod
QrySupplier
Would you also advise the OP to not bother with the Qry prefix?
Suppose he has Country, DispatchMethod and Supplier tables as well as
Country, DispatchMethod and Supplier queries. When he uses these as
recordsources for forms and reports or in code, how is he suppose to know if
the reference is to the table or query?
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:f89kc3dj2hl2u5s01...@4ax.com...
Tony Toews [MVP]
>QryCountry
>QryDispatchMethod
>QrySupplier
>
>Would you also advise the OP to not bother with the Qry prefix?
Yes.
>Suppose he has Country, DispatchMethod and Supplier tables as well as
>Country, DispatchMethod and Supplier queries. When he uses these as
>recordsources for forms and reports or in code, how is he suppose to know if
>the reference is to the table or query?
My queries have much more descriptive names than one word. I
frequently run up against the 50 or 52 character limit.
So this is a non issue.
Also see Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm
Gina Whipp
Just my opinion...
I use the object naming and then take it a step further...
tblOrders
oOrderID-PK
oDate
oClientID
etc...
tblOrderDetail
odOrderID-FK
odItemID
odDescription
etc...
qryOrders may contain
oOrderID (from tblOrders)
cpCompanyName (I know this comes from tblClientProfile)
oDate (from tblOrders)
odItemID (from tblOrderDetails)
odDescription (from tblOrderDetails)
If I have to do a DLookUp, I know whether I went to the table or the query
to get my information. I think that best naming convention is the one that
works for you and you will follow thru on.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:f89kc3dj2hl2u5s01...@4ax.com...
Steve
drop 3 characters from his table names???? Not much logic to that.
You need the advise rather than the OP. Shorten your table names and query
names and save a lot of time typing and reduce the probability of typos.
Unbelievable, Tony!!!!!!!
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:9emkc39jvbhv82kd2...@4ax.com...
Arno R
"Steve" <so...@private.emailaddress> schreef in bericht news:13cko2f...@corp.supernews.com...
> Unbelievable, Tony!!!!!!!
>
Too bad for you Steve that !!!!!!! is not allowed in the objectnames ...
Arno R
John Spencer
(usually just the letter q, but sometimes qry).
I do this since I tend to use the query wizards to get me started when I am
constructing my queries and the leading character lets me know when the
interface is displaying a query or a table.
Personal preference. On the other hand, I agree with Tony - I don't see any
reason to prefix tables with TBL or fields with fld. That usually just
means more typing for no real benefit that I see. Again, if it works for
you - go ahead and use it.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:9emkc39jvbhv82kd2...@4ax.com...
Arvin Meyer [MVP]
news:uwLg8U64...@TK2MSFTNGP03.phx.gbl...
> If I have to do a DLookUp, I know whether I went to the table or the query
> to get my information. I think that best naming convention is the one
> that works for you and you will follow thru on.
I agree with that. I use the Leszynski/Reddick naming convention, almost
exactly as it was published. I only do it because other programmers often
work on the same projects and I like to maintain some consistency with a
generally accepted method. If you are the only person working then whatever
consistency you use is really up to you.
I also keep my naming as short as possible, but still able to adequately
identify every object. I do have some very long names, but most are under 15
to 20 characters.
Tony Toews [MVP]
>50 some character table names and query names and you are advising the OP to
>drop 3 characters from his table names???? Not much logic to that.
Ah, one reason is that in the database container window you can just
hit the first character of a group of queries, forms, whatever. Then
you can quickly go to that set of objects. Such as P for PO or I for
inventory.
>You need the advise rather than the OP. Shorten your table names and query
>names and save a lot of time typing and reduce the probability of typos.
Huh? When I need a object name I click once on the object, wait a
second, click again as though I were going to rename the object and
copy the name. Done. No types.
>Unbelievable, Tony!!!!!!!
Clearly you have not a clue.
Tony Toews [MVP]
>I use the object naming and then take it a step further...
>
>tblOrders
>oOrderID-PK
>oDate
>oClientID
>etc...
With the exception of the tbl I agree with you.
Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm
Tony
Gina Whipp
Just curious... (Always willing to learn something new!)
What about in the case of DLookUp's (just one example), how would you know
where you are pulling the data from? Or do you table and query names never
match?
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:r3bmc3h3d3uer326d...@4ax.com...
Tony Toews [MVP]
>Just curious... (Always willing to learn something new!)
>
>What about in the case of DLookUp's (just one example), how would you know
>where you are pulling the data from? Or do you table and query names never
>match?
I never use DLookups but code my own using recordsets and queries
usually defined in VBA code.
My table and query names can never match as my query names are quite
lengthy and descriptive and always have spaces. (Well almost always).
For example in a simple system I built for memberships the table names
are
Activity
Emails
GlobalOptions
Member
MemberFamily
MembershipActivity
MembershipEmails
Status
And the query names are
Acknowledgements to be sent via email
Acknowledgements to be sent via snail mail
Action - Acknowledgements
Action - Renewals
Active members
Activity - Renewal
Activity Summary - MembershipActivityDetails
AGM Notices to be sent
AGM Notices to be sent - Actiivity Append
Integrity checker - Renewals but not active
Membership Acknowledgements - Outstanding
Membership Activity History
Membership Renewals - Outstanding
Membership Renewals Outstanding
MembershipDetailReport
MembershipNoneDetailReport
Name Badges to be sent via snail mail
Non members with Email Addresses
Renewal Acknowledgements to be sent via email
Renewal Acknowledgements to be sent via snail mail for Pending R
Renewal Notices to be sent
Renewal Notices to be sent via email
Renewal Notices to be sent via snail mail
The code I used to produce the above is (as adapted from A97 help)
Sub ListQueryNames()
Dim qry As QueryDef, db As Database
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
Debug.Print " " & qdf.Name
Next qdf
End Sub
Sub ListTableNames()
Dim qry As TableDef, db As Database
Dim dbs As Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
Debug.Print " " & tdf.Name
Next tdf
End Sub
Now I have a much larger system with 160 tables, 1200 queries, 450
forms and 350 reports. But that would be rather long for folks to
look at the names. <smile>
Tony Toews [MVP]
>Unbelievable, Tony!!!!!!!
BTW Steve I don't like that attitude of yours. If you are going to
discuss things in the newsgroups please keep a civil keyboard on your
desk.
David W. Fenton
> "Steve" <so...@private.emailaddress> wrote:
>
>>Unbelievable, Tony!!!!!!!
>
> BTW Steve I don't like that attitude of yours. If you are going
> to discuss things in the newsgroups please keep a civil keyboard
> on your desk.
Uh, what Steve wrote looks a lot like what I would have said if he
hadn't gotten there first.
Except, I probably would have been more harsh.
I think, because of the fact that Access presents a combination of
the TableDefs and QueryDefs collections in some contexts, that it's
crucial to distinguish them by a prefix (it could be just t or q),
and because they share a single namespace (i.e., you can't have a
query and a table with the exact same name).
Likewise, I think it's great to be able to have a table and a query
with the same base name, so you can tell that the query presents the
data from a particular table. This is particularly important when
you have ULS and need to use RWOP queries. Your tblPerson would be
edited via the RWOP query qryPerson. That seems to me to be an
extremely logical way of organizing things.
How would you do the same thing without prefixes? Suffixes?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton
news:OvbS3h#4HHA...@TK2MSFTNGP04.phx.gbl:
> Personal preference. On the other hand, I agree with Tony - I
> don't see any reason to prefix tables with TBL or fields with fld.
> That usually just means more typing for no real benefit that I
> see. Again, if it works for you - go ahead and use it.
I don't do it for fieldnames in tables, but I do use it for tables.
Indeed, I use tbl for regular tables, tmp for temp tables and arc
for archive tables, often all three in a single application. If
you're not distinguishing different table types, though, I guess
there wouldn't be much benefit.
I don't distinguish query types (e.g., append vs. select vs. update
vs. delete) with prefixes, so logically, it would make more sense
for *me* to use my 3 prefixes on tables, and no prefix on queries.
But that would likely confuse other developers. Using the standard
tbl and qry makes it easier someone else to understand my design, as
it makes things very obvious, and keeps the table and query
namespaces distinct.
David W. Fenton
news:epX4hl$4HHA...@TK2MSFTNGP06.phx.gbl:
> I use the Leszynski/Reddick naming convention, almost
> exactly as it was published. I only do it because other
> programmers often work on the same projects and I like to maintain
> some consistency with a generally accepted method. If you are the
> only person working then whatever consistency you use is really up
> to you.
Well, I think there's something to be said for using it even if
you're the only developer who is scheduled to work on the project.
It's a selling point to the client that you use the
industry-standard naming conventions, rather than some idiosyncratic
naming convention of your own, because someday in the future,
someone else may be working on the project.
I hate working on my very old projects, because I had no systematic
naming convention. It drives me nuts and eats up time while I figure
out what the hell everything is.
David W. Fenton
> Now I have a much larger system with 160 tables, 1200 queries, 450
> forms and 350 reports. But that would be rather long for folks to
> look at the names.
But querydefs and tabledefs have a shared namespace, i.e., you can't
have an object of the same name in both collections, and the Access
UI in some contexts presents a combined list of both. In a large
project, how do you tell which is which in those lists?
And how do you associate similar objects, such as a secured base
table and its corresponding RWOP query?
Tony Toews [MVP]
>> Now I have a much larger system with 160 tables, 1200 queries, 450
>> forms and 350 reports. But that would be rather long for folks to
>> look at the names.
>
>But querydefs and tabledefs have a shared namespace, i.e., you can't
>have an object of the same name in both collections, and the Access
>UI in some contexts presents a combined list of both. In a large
>project, how do you tell which is which in those lists?
Look at the object names. Queries have spaces in them and are longer.
Tables don't.
>And how do you associate similar objects, such as a secured base
>table and its corresponding RWOP query?
I don't use RWOP permissions or security. Although I'd figure
something out there.
Arno R
"David W. Fenton" <XXXu...@dfenton.com.invalid> schreef in bericht news:Xns9993B7EB2538Ef9...@127.0.0.1...
> "Tony Toews [MVP]" <tto...@telusplanet.net> wrote in
> news:u6imc3d3kkf3h0i86...@4ax.com:
>
>> "Steve" <so...@private.emailaddress> wrote:
>>
>>>Unbelievable, Tony!!!!!!!
>>
>> BTW Steve I don't like that attitude of yours. If you are going
>> to discuss things in the newsgroups please keep a civil keyboard
>> on your desk.
>
> Uh, what Steve wrote looks a lot like what I would have said if he
> hadn't gotten there first.
Unbelievable, David!!!!!!!
> Except, I probably would have been more harsh.
I guess so yes... but why!!!!!!!
Arno R
Tony Toews [MVP]
>Uh, what Steve wrote looks a lot like what I would have said if he
>hadn't gotten there first.
>
>Except, I probably would have been more harsh.
Ok, maybe I'll let Steve get away with that one then.
>I think, because of the fact that Access presents a combination of
>the TableDefs and QueryDefs collections in some contexts, that it's
>crucial to distinguish them by a prefix (it could be just t or q),
>and because they share a single namespace (i.e., you can't have a
>query and a table with the exact same name).
>
>Likewise, I think it's great to be able to have a table and a query
>with the same base name, so you can tell that the query presents the
>data from a particular table. This is particularly important when
>you have ULS and need to use RWOP queries. Your tblPerson would be
>edited via the RWOP query qryPerson. That seems to me to be an
>extremely logical way of organizing things.
>
>How would you do the same thing without prefixes? Suffixes?
Yes, I would use some kind of suffix.
Granted to each thier own.
But to me on a large FE it is very, very handy to hit the first letter
of a group of objects in the database container window and get there
fast.
That overrides any of the q and t stuff. I still maintain that's a
complete waste of time.
Steve
you are going against "learned" advise and putting spaces in query names????
So besides wasting a lot of time typing long object names, you have to
remember to put square brackets around your query names. Do you put square
brackets around table names or do you use another set of rules for table
names?
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:cahmc3pmp2o533keb...@4ax.com...
Steve
Frm and prefix all subreport names with SFrm. I get two distinct advantages
from this. First, when I look at the forms database window, all form names
appear first and all subform names are grouped separately from form names.
Second, it helps to catch errors when referencing forms and subforms..
The way I name reports and subreports is similar to the way I name forms and
subforms. I prefix reports with Rpt and I prefix subreports with SRpt. The
advantages for reports are the same as the advantages for forms.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"Kamitsukenu" <Kamit...@discussions.microsoft.com> wrote in message
news:72266BF7-C758-4089...@microsoft.com...
> Hi there,
>
> The company I work for mail items dependent on destination, weight and
> speed
> of delivery.
>
> I'm trying to make a system in Access, and during setting up all the
> tables,
> I'm getting myself more and more confused.
>
> I have the following tables;
>
> (tblCountry) - holds the Country names
> (tblDispatchMethod) - the service the item is sent by
> (tblSupplier) - who the item is mailed by
>
> Now, as dispatch of items are dependent on the weight, I have another
> table
> which combines all the above information.
>
> TblRoutingSystem contains
> 'CountryName' which refers to 'tblCountry'
> 'DispatchMethod' which refers to 'tblDispatchMethod'
> 'NameofSupplier' which refers to 'tblSupplier'
> 'WeightBandStart' and 'WeightBandEnd' which refer to (surprise, surprise)
> the start and end of the weight bands for deciding the correct supplier
> for
> mailing.
>
> Does this sound about right, or should I be split them out further or
> should
> I be merging more stuff together.
>
> in fact, can someone go through the pros and cons of creating more tables
> to
> suit the data?
>
> Thanks
>
> K
>
>
Tony Toews [MVP]
>Repeatedly MVPs advise posters not to use spaces in object names and here
>you are going against "learned" advise and putting spaces in query names????
Correct.
I view myself as one of the "learned" advisors. And I hold a
contrary opinion to most on this topic. As I do others. So my
attitude varies somewhere between tough and who cares.
>So besides wasting a lot of time typing long object names,
Reread a previous posting on this topic. I do *not* type long object
names.
>you have to
>remember to put square brackets around your query names.
Whenever I use queries embedded in VBA I create the query the first
time using the query builder which takes care of everything anyhow.
So that's a non issue too.
>Do you put square
>brackets around table names or do you use another set of rules for table
>names?
No, no need.
Gina Whipp
> naming convention. It drives me nuts and eats up time while I figure
> out what the hell everything is.
I feel your pain... been there done that!
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9993B8ECF9A1Ff9...@127.0.0.1...
Steve
you request I keep a civil keyboard. Is this the brotherhood of MVPs that
you always have to support another MVP? Someone asked recently in a post if
the "P" in MVP means you are dedicated to displaying Professional conduct.
John Marshall's conduct is anything but Professional and yet you give him
your full support.
Unbelievable Tony!!!!!!!!!!
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:u6imc3d3kkf3h0i86...@4ax.com...
Gina Whipp
Interesting, I try to stay away from spaces when naming ANY object, just to
avoid those extra key strokes.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:cahmc3pmp2o533keb...@4ax.com...
Tony Toews [MVP]
>No one has yet mentioned form and report names. I prefix all form names with
>Frm and prefix all subreport names with SFrm. I get two distinct advantages
>from this. First, when I look at the forms database window, all form names
>appear first and all subform names are grouped separately from form names.
>Second, it helps to catch errors when referencing forms and subforms..
>
>The way I name reports and subreports is similar to the way I name forms and
>subforms. I prefix reports with Rpt and I prefix subreports with SRpt. The
>advantages for reports are the same as the advantages for forms.
And again, and for the same reasons, I view this as an waste of time.
Sub reports and sub forms I name with a suffix such as sbf or
something like " - Emails sbf" if there are more than one subform or
subreport.
In my opinion it's much, much easier to work with the parent
form/report and the sub form/sub report next to each other in the
database container window.
But then I routinely work with databases with hundreds of objects.
Tony Toews [MVP]
>Thanks for sharing!
You're welcome. This is an interesting discussion.
>Interesting, I try to stay away from spaces when naming ANY object, just to
>avoid those extra key strokes.
But if you read one of my other postings extra key strokes aren't a
problem for me.
"Whenever I use queries embedded in VBA I create the query the first
time using the query builder which takes care of everything anyhow.
So that's a non issue too."
and
"When I need a object name I click once on the object, wait a
second, click again as though I were going to rename the object and
copy the name. Done. No typos."
I also make extensive use of Notepad as a very temporary "clipboard".
Tony Toews [MVP]
Started a new thread as this has no bearing on the interesting topic
at hand.
>BTW Tony you always give your full support to Arno R and John Marshall but
>you request I keep a civil keyboard.
And I should really do the same for David Fenton. Sometimes he gets a
slight bit snarky for lack of a better word.
>Is this the brotherhood of MVPs that
>you always have to support another MVP?
Not at all. Most of the time we agree.
And you're soliciting customers in this forum is not acceptable.
>Someone asked recently in a post if
>the "P" in MVP means you are dedicated to displaying Professional conduct.
>John Marshall's conduct is anything but Professional and yet you give him
>your full support.
>
>Unbelievable Tony!!!!!!!!!!
<shrug> Oh well.
Steve
my
attitude varies somewhere between tough and who cares.>>
And so does this also apply to offering help to newsgroup posters when it is
clear the OP could use special help or needs more help beyond what he is
likely to get from the newsgroup?
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:5hrmc398v98prn54m...@4ax.com...
Steve
<<And I hold a contrary opinion to most on this topic. As I do others. So
my attitude varies somewhere between tough and who cares.>>
You said here in reference to MVPs--
<<Most of the time we agree.>>
Which is the true answer; number 1 or number 2?
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:r6tmc3d2ofljn3ge8...@4ax.com...
Steve
Tony said in his post just above that you "...gets a slight bit snarky..."
What does "snarky" mean?
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9993B87B9AD64f9...@127.0.0.1...
Tony Toews [MVP]
><<And I hold a contrary opinion to most on this topic. As I do others. So
>my attitude varies somewhere between tough and who cares.>>
>
>clear the OP could use special help or needs more help beyond what he is
>likely to get from the newsgroup?
Totally different situation Steve and you know it. That's called
soliciting for work in the newsgroups.
Tony Toews [MVP]
>And so does this also apply to offering help to newsgroup posters when it is
>clear the OP could use special help or needs more help beyond what he is
>likely to get from the newsgroup?
Responded to in another thread titled Question by Steve.
Tony Toews [MVP]
>You said elsewhere --
><<And I hold a contrary opinion to most on this topic. As I do others. So
>my attitude varies somewhere between tough and who cares.>>
>
>You said here in reference to MVPs--
><<Most of the time we agree.>>
>
>Which is the true answer; number 1 or number 2?
Depends.
Two different question, two different answer.
Douglas J. Steele
The two statements are consistent with one another.
"Most of the time we agree" implies that some times we have a difference of
opinion.
"I hold a contrary opinion to most on this topic" implies that this is one
of the times that there is a difference of opinion.
I guess they must not have taught English at your engineering school.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Steve" <so...@private.emailaddress> wrote in message
news:13cmu62...@corp.supernews.com...
Tony Toews [MVP]
>David,
>
>Tony said in his post just above that you "...gets a slight bit snarky..."
>
>What does "snarky" mean?
Steve
We're attempting to have a polite discussion here.
Move that kind of cr*p to another thread.
Tony Toews [MVP]
>BTW Tony you always give
Responded to in a thread titled Question by Steve.
This has been a quite nice discussion so far so let's move your off
topic questions elsewhere.
Gina Whipp
> problem for me.
I read that AFTER I typed my reply.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:tvsmc3hoka7htig7l...@4ax.com...
Gina Whipp
> form/report and the sub form/sub report next to each other in the
> database container window.
I never thought of it like that... However, I use the description in the
container window to associate the forms with the subform, which may also
include the query, module, etc... that it is attached to.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:5ormc3hvkbr8hteid...@4ax.com...
Tony Toews [MVP]
>> But if you read one of my other postings extra key strokes aren't a
>> problem for me.
>
>I read that AFTER I typed my reply.
<smile> I've done that myself occasionally.
Tony Toews [MVP]
>> In my opinion it's much, much easier to work with the parent
>> form/report and the sub form/sub report next to each other in the
>> database container window.
>I never thought of it like that... However, I use the description in the
>container window to associate the forms with the subform, which may also
>include the query, module, etc... that it is attached to.
Now I never look at the description and the dates as I have the list
view set. I want as many objects as possible on my screen.
Interesting how we, or in this case, I've been doing certain things in
certain ways that I have to start going back and explaining why doing
other things facilitates this thing.
If you get my drift.
Gina Whipp
Yes I do...
In my case I find it interesting, the people I get to help me from time to
time have no choice but to do it my way. So anytime I get to have a
discussion about different ways helps me to understand/learn why other
programmers choose another way and perhaps enlighten me to a technique I
never considered.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:l11nc3t5pag4pouvf...@4ax.com...
Tony Toews [MVP]
>> If you get my drift.
>
>Yes I do...
>
>In my case I find it interesting, the people I get to help me from time to
>time have no choice but to do it my way. So anytime I get to have a
>discussion about different ways helps me to understand/learn why other
>programmers choose another way and perhaps enlighten me to a technique I
>never considered.
Yup, always interesting to get some tips. For example, I once whined
about how sometimes Alt+Tabbing from VBA code window to form view in
A2003 only got up the property sheet. Someone responded that, umm,
was it Alt+F11 would work.
Gina Whipp
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:f22nc3t9jhsj0kp5m...@4ax.com...
Tony Toews [MVP]
>Okie dokie now... I didn't know THAT!
Thanks. Blogged.
http://msmvps.com/blogs/access/archive/2007/08/21/alt-tabbing-from-vba-ide-to-access-2003-window.aspx
Arno R
"Tony Toews [MVP]" <tto...@telusplanet.net> schreef in bericht news:5ormc3hvkbr8hteid...@4ax.com...
>
> In my opinion it's much, much easier to work with the parent
> form/report and the sub form/sub report next to each other in the
> database container window.
>
> But then I routinely work with databases with hundreds of objects.
>
Hmmm, quite a 'discussion' here with Steve ;-)
Yep, I do the very same here.
FrmCustomers
FrmCustomers_sub
Regarding queries:
Sometimes I use a couple of 'sequential' queries to perform a certain job.
I name them so that they will be grouped in the container
qryInvoice_1
qryInvoice_2
qryInvoice_3
(The underscore here is important to distinct from duplicates created 'behind my back' when importing from other db's)
Arno R
Tony Toews [MVP]
>Yep, I do the very same here.
>FrmCustomers
>FrmCustomers_sub
Whereas I would use
Customers
Customers - Payments sbf
Customers - Notes sbf
Customers - Invoices sbf
etc, etc.
David W. Fenton
> "Steve" <so...@private.emailaddress> wrote:
>
>>Repeatedly MVPs advise posters not to use spaces in object names
>>and here you are going against "learned" advise and putting spaces
>>in query names????
>
> Correct.
>
> I view myself as one of the "learned" advisors.
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.
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote:
>
>>> Now I have a much larger system with 160 tables, 1200 queries,
>>> 450 forms and 350 reports. But that would be rather long for
>>> folks to look at the names.
>>
>>But querydefs and tabledefs have a shared namespace, i.e., you
>>can't have an object of the same name in both collections, and the
>>Access UI in some contexts presents a combined list of both. In a
>>large project, how do you tell which is which in those lists?
>
> 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()?
Rick Brandt
news:Xns9993B94B6ABD0f9...@127.0.0.1...> news:cahmc3pmp2o533keb...@4ax.com:
>
>> Now I have a much larger system with 160 tables, 1200 queries, 450
>> forms and 350 reports. But that would be rather long for folks to
>> look at the names.
>
> But querydefs and tabledefs have a shared namespace, i.e., you can't
> have an object of the same name in both collections, and the Access
> UI in some contexts presents a combined list of both. In a large
> project, how do you tell which is which in those lists?
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.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Tony Toews [MVP]
>> 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?
Tony Toews [MVP]
>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.
What do you mean by "virtual table"?
Grover Park George
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.
My own naming conventions are quite traditional. I use three character
prefixes for tables, queries and reports and suffix ID for key fields.
However, I strongly believe the only crucial convention is
consistency.
George Hepworth, MS Access MVP
Douglas J. Steele
news:Xns9994D48274963f9...@127.0.0.1...
> "Tony Toews [MVP]" <tto...@telusplanet.net> wrote in
> news:5hrmc398v98prn54m...@4ax.com:
>
>> "Steve" <so...@private.emailaddress> wrote:
>>
>>>Repeatedly MVPs advise posters not to use spaces in object names
>>>and here you are going against "learned" advise and putting spaces
>>>in query names????
>>
>> Correct.
>>
>> I view myself as one of the "learned" advisors.
>
> 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?
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote:
>
>>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.
>
> 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
news:uI#6PYX5H...@TK2MSFTNGP06.phx.gbl:
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
> "David W. Fenton" <XXXu...@dfenton.com.invalid> 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.
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
news:4r5zi.4158$Oo....@newssvr17.news.prodigy.net:
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
news:1187853615.3...@x40g2000prg.googlegroups.com:
> 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
>> 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!
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.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
Tony Toews [MVP]
>>>> 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.
(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 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 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.
Rick Brandt
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> 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
news:OsciSBF5...@TK2MSFTNGP02.phx.gbl...
>
> 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
news:13cmue5...@corp.supernews.com...
> David,
>
Grow up FFS.
Keith Wilby
news:9tpoc35q2j6u7l9p2...@4ax.com...
> "Arno R" <arracomn_...@planet.nl> wrote:
>
>>Yep, I do the very same here.
>>FrmCustomers
>>FrmCustomers_sub
>
> 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
"Keith Wilby" <he...@there.com> schreef in bericht news:46ce8633$1...@glkas0286.greenlnk.net...
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
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" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:OsciSBF5...@TK2MSFTNGP02.phx.gbl...
>
> I guess they must not have taught English at your engineering school.
>
Douglas J. Steele
news:bf5sc3h3jq35vrpv7...@4ax.com...
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> 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)
Gina Whipp
Great site Arno!
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"Arno R" <arracomn_...@planet.nl> wrote in message
news:46cea281$0$25501$ba62...@text.nova.planet.nl...
Jamie Collins
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
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]
>>>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?
Douglas J. Steele
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> 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".
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.
>>>
>>>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);
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> 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
news:#q1TRLk5...@TK2MSFTNGP02.phx.gbl:
> "Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
> news:bf5sc3h3jq35vrpv7...@4ax.com...
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> 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
news:eNd5n#o5HHA...@TK2MSFTNGP03.phx.gbl:
> "Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
> news:id5uc3tupo4l7094d...@4ax.com...
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> 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.
Tony Toews [MVP]
>> 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.
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> 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
>
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns99958DD6A6FB6f9...@127.0.0.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
news:13cmue5...@corp.supernews.com:
> 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 Toews [MVP]
>>(tblCountry) - holds the Country names
>>(tblDispatchMethod) - the service the item is sent by
>>(tblSupplier) - who the item is mailed by
>
>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.
Kamitsukenu
The company I work for mail items dependent on destination, weight and speed
of delivery.
I'm trying to make a system in Access, and during setting up all the tables,
I'm getting myself more and more confused.
I have the following tables;
(tblCountry) - holds the Country names
(tblDispatchMethod) - the service the item is sent by
(tblSupplier) - who the item is mailed by
Now, as dispatch of items are dependent on the weight, I have another table
which combines all the above information.
TblRoutingSystem contains
'CountryName' which refers to 'tblCountry'
'DispatchMethod' which refers to 'tblDispatchMethod'
'NameofSupplier' which refers to 'tblSupplier'
'WeightBandStart' and 'WeightBandEnd' which refer to (surprise, surprise)
the start and end of the weight bands for deciding the correct supplier for
mailing.
Does this sound about right, or should I be split them out further or should
I be merging more stuff together.
in fact, can someone go through the pros and cons of creating more tables to
suit the data?
Thanks
K
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.)
Kamitsukenu
Thanks for clearing that one up for me Arvin. You're right though, it does
seem too simple!
Kind Regards,
John
"Arvin Meyer [MVP]" wrote:
> It sounds right if it's that simple. Instead of using the actual text from
> the 3 tables, use the ID or Primary Key value.The tblRoutingSystem should
> contain:
>
> CountryID
> DispatchMethodID
> SupplierID
>
> Now if the Supplier decides to sell the company or change its name, you
> simply change it once in the Supplier table, and it propogates throughout
> the database from the queries used to display the SupplierName.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
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]
>"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in
>news:elouc3lctivs9dcck...@4ax.com:
>
>> 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.
From what I recall I don't see any difference between your usage of
subqueries and the example from the A97 help.
Keith Wilby
news:%23nVM0Yk...@TK2MSFTNGP05.phx.gbl...
> You forgot.... For Freak's Sake (polite term)
>
Thanks for jumping in there Gina, been away for a few days :-)
Jamie Collins
wrote:
> > 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.
FWIW (quite a lot IMO) it's the term used in the ANSI/ISO SQL-92 spec.
Jamie.
--
Jamie Collins
> >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.
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.
--