A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Question about a default table or method



 
 
Thread Tools Display Modes
  #11  
Old November 14th, 2005, 05:33 AM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Don't worry about it. I've kind of had a problems since the beginning
getting my questions accross. I either give too much info or not enough.

PC Datasheet wrote:
Go back to Visio, you don't know what you are talking about (just
like all the posts you have made!!) Joe asked about default values
not global variables.


"John Marshall, MVP" wrote in message
...
Take a look at "Understanding the Lifetime of Variables" in the VBA
help to get an understanding of how to set up a variable that will
last for a session.

As Duane mentioned, data should be kept in tables. Try and limit the
use of global variables to the smallest scope possible and load them
from a table. Remember, the data in the table could change and what
is in the variable will be obsolete.

John... Visio MVP

"Joe Cilinceon" wrote in message
...
What is the best way to have defaults in one location but available
to the whole application. Some examples would be Sales Tax rate,
Late Fee charge in addition to things like scanners/printers or
other things used with in the program.

--

Joe Cilinceon


--

Joe Cilinceon



  #12  
Old November 14th, 2005, 06:58 AM
tina
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Joe, are you describing certain values that you use throughout your
database, but that don't really belong in a specific table? such as one, and
only one, current Sales Tax rate - with no need to store *prior* rate
values.

if this is what you're talking about, then read on for the solution i use:
i sometimes find myself with a few "miscellaneous" values that are used in
various places in my database but don't really "belong" in any of my data
tables. when that happens, i create a miscellaneous table, with *only one
record* (if you find that you need multiple records of one value, then
that's a legitimate table that should be built as such), and a separate
field for each value that i need to store. the table is not linked to any
other table, and the fields in the table usually don't have anything to do
with each other.

having built and populated tblMisc, you can retrieve a particular value
wherever you need it in your database, with a simple DLookup() function -
remember that the table has only one record in it, so all you need to do is
look up the field holding the value you're after, as

DLookup("ThisField", "tblMisc")

hth


"Joe Cilinceon" wrote in message
...
What is the best way to have defaults in one location but available to the
whole application. Some examples would be Sales Tax rate, Late Fee charge

in
addition to things like scanners/printers or other things used with in the
program.

--

Joe Cilinceon





  #13  
Old November 14th, 2005, 08:15 AM
Vincent Johns
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Tina's "miscellaneous" Table has worked well for me, too. This is one
case in which having dozens of unrelated fields in a record makes a lot
of sense. I do try to give the fields suggestive names (not "Value1",
"Value2", etc.) and of course I add comments (in Table Design View) to
provide some explanation of what the datum means. (Those comments are
not just for someone else's benefit -- it also helps me, six months or
more later.)

-- Vincent Johns
Please feel free to quote anything I say here.

tina wrote:

[...]
if this is what you're talking about, then read on for the solution i use:
i sometimes find myself with a few "miscellaneous" values that are used in
various places in my database but don't really "belong" in any of my data
tables. when that happens, i create a miscellaneous table, with *only one
record* (if you find that you need multiple records of one value, then
that's a legitimate table that should be built as such), and a separate
field for each value that i need to store. the table is not linked to any
other table, and the fields in the table usually don't have anything to do
with each other.

having built and populated tblMisc, you can retrieve a particular value
wherever you need it in your database, with a simple DLookup() function -
remember that the table has only one record in it, so all you need to do is
look up the field holding the value you're after, as

DLookup("ThisField", "tblMisc")

hth

  #14  
Old November 14th, 2005, 08:49 AM
tina
external usenet poster
 
Posts: n/a
Default Question about a default table or method

This is one
case in which having dozens of unrelated fields in a record makes a lot
of sense.


well, i don't know about *dozens* of fields in a tblMisc, LOL. i think i've
had maybe five or six fields in a single miscellaneous table, tops.
sometimes a field to hold some arbitrary date that has some significance to
part or all of the database, maybe another field for the current user - just
so i don't have to keep looking it up on the system, maybe the name of the
company (that the db belongs to) for use in report headers - so it can be
changed easily in one place. that's about it - just a few odd little bits of
data that don't belong anywhere, but shouldn't be hard-coded because they
will/may need to be changed frequently/occasionally.


"Vincent Johns" wrote in message
nk.net...
Tina's "miscellaneous" Table has worked well for me, too. This is one
case in which having dozens of unrelated fields in a record makes a lot
of sense. I do try to give the fields suggestive names (not "Value1",
"Value2", etc.) and of course I add comments (in Table Design View) to
provide some explanation of what the datum means. (Those comments are
not just for someone else's benefit -- it also helps me, six months or
more later.)

-- Vincent Johns
Please feel free to quote anything I say here.

tina wrote:

[...]
if this is what you're talking about, then read on for the solution i

use:
i sometimes find myself with a few "miscellaneous" values that are used

in
various places in my database but don't really "belong" in any of my

data
tables. when that happens, i create a miscellaneous table, with *only

one
record* (if you find that you need multiple records of one value, then
that's a legitimate table that should be built as such), and a separate
field for each value that i need to store. the table is not linked to

any
other table, and the fields in the table usually don't have anything to

do
with each other.

having built and populated tblMisc, you can retrieve a particular value
wherever you need it in your database, with a simple DLookup()

function -
remember that the table has only one record in it, so all you need to do

is
look up the field holding the value you're after, as

DLookup("ThisField", "tblMisc")

hth



  #15  
Old November 14th, 2005, 09:07 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Joe, you've already got some good answers. Here's a couple more ideas.

Application and User settings
=====================
A disadvantage of creating a Field in a miscellaneous table for each default
is that as the application grows you have to modify the table every time you
want to add a new default. While that's doable, it's messy when you have
multiple users at different locations. It might be better to create a more
generic table with fields such as these:
TheVariable Text The name, such as "SalesTaxRate".
TheUser Text Zero-length string if it applies to all
users.
TheValue Text The current value to use for this variable
DataType Long A value that is a member of vbVarType
Descrip Text Description of what this variable is used
for.

Advantages:
- Add new variables without redesigning the table.
- Store application-level preferences.
- Store user user-level preferences (e.g. suppress this warning for this
user), adding new users whenever needed.
- Have the user's preferences available from any machine they log into, and
keep them when the front end is updated (because they are not kept in the
workstation's front end.)

Disadvantages:
All values are stored as text. Use VarType(), IsNumeric(), and IsDate() to
validate that the entry is suitable, but it still requires a bit of
manipulating. For example, percentages need to be stored as fractions, e.g.
0.1 for 10%, and dates need to be converted to double so the regional
settings of one user are not interpreted differently by another.

Suggestions:
- Use a 2-field primary key: TheVariable + TheUser.
- Set these properties for the TheUser field:
AllowZeroLength Yes
Default Value: ""
As part of the primary key, neither field can be null, but TheUser defaults
to a zero-length string which represents an application-wide preference.

Object-level settings
===============
Sometimes you want the software to remember a setting per object, such as
which one of a workstation's printers should be used as the default for
printing a label report. You might want to consider creating a custom
property on the report itself.

Advantage: The property stays with the report even if it is copied or
renamed.

Disadvantage: the setting is lost when yor replace the front end with an
update.

You can download an example of this approach for Access 2002 or 2003:
Printer Selection Utility
at:
http://allenbrowne.com/AppPrintMgt.html

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Cilinceon" wrote in message
...
What is the best way to have defaults in one location but available to the
whole application. Some examples would be Sales Tax rate, Late Fee charge
in addition to things like scanners/printers or other things used with in
the program.



  #16  
Old November 14th, 2005, 09:15 AM
StopThisAdvertising
external usenet poster
 
Posts: n/a
Default Question about a default table or method


"Tom" schreef in bericht ink.net...

Who is Tom ?? Again an alias for *too* stupid questions Steve ??

Arno R

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


  #17  
Old November 14th, 2005, 09:16 AM
StopThisAdvertising
external usenet poster
 
Posts: n/a
Default Question about a default table or method


"PC Datasheet" schreef in bericht ink.net...

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


Is Tom doing homework now ??

Arno R
  #18  
Old November 14th, 2005, 01:26 PM
external usenet poster
 
Posts: n/a
Default Question about a default table or method


Allen Browne wrote:
It might be better to create a more
generic table with fields such as these:
TheVariable Text The name, such as "SalesTaxRate".
TheUser Text Zero-length string if it applies to all
users.
TheValue Text The current value to use for this variable
DataType Long A value that is a member of vbVarType
Descrip Text Description of what this variable is used


Sounds a bit too close to the "One True Lookup Table" design flaw to
me:

http://www.dbazine.com/ofinterest/oi-articles/celko22

  #19  
Old November 14th, 2005, 01:40 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Thank you tina and that is exactly what I was asking. I was looking for
methods of doing this efficiently.

tina wrote:
Joe, are you describing certain values that you use throughout your
database, but that don't really belong in a specific table? such as
one, and only one, current Sales Tax rate - with no need to store
*prior* rate values.

if this is what you're talking about, then read on for the solution i
use: i sometimes find myself with a few "miscellaneous" values that
are used in various places in my database but don't really "belong"
in any of my data tables. when that happens, i create a miscellaneous
table, with *only one record* (if you find that you need multiple
records of one value, then that's a legitimate table that should be
built as such), and a separate field for each value that i need to
store. the table is not linked to any other table, and the fields in
the table usually don't have anything to do with each other.

having built and populated tblMisc, you can retrieve a particular
value wherever you need it in your database, with a simple DLookup()
function - remember that the table has only one record in it, so all
you need to do is look up the field holding the value you're after, as

DLookup("ThisField", "tblMisc")

hth


"Joe Cilinceon" wrote in message
...
What is the best way to have defaults in one location but available
to the whole application. Some examples would be Sales Tax rate,
Late Fee charge in addition to things like scanners/printers or
other things used with in the program.

--

Joe Cilinceon


--

Joe Cilinceon



  #20  
Old November 14th, 2005, 01:41 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Thanks Vincent I going to give it a try. It seems pretty simple to
implement.

Vincent Johns wrote:
Tina's "miscellaneous" Table has worked well for me, too. This is one
case in which having dozens of unrelated fields in a record makes a
lot of sense. I do try to give the fields suggestive names (not
"Value1", "Value2", etc.) and of course I add comments (in Table
Design View) to provide some explanation of what the datum means. (Those
comments are not just for someone else's benefit -- it also
helps me, six months or more later.)

-- Vincent Johns
Please feel free to quote anything I say here.

tina wrote:

[...]
if this is what you're talking about, then read on for the solution
i use: i sometimes find myself with a few "miscellaneous" values
that are used in various places in my database but don't really
"belong" in any of my data tables. when that happens, i create a
miscellaneous table, with *only one record* (if you find that you
need multiple records of one value, then that's a legitimate table
that should be built as such), and a separate field for each value
that i need to store. the table is not linked to any other table,
and the fields in the table usually don't have anything to do with
each other. having built and populated tblMisc, you can retrieve a
particular
value wherever you need it in your database, with a simple DLookup()
function - remember that the table has only one record in it, so all
you need to do is look up the field holding the value you're after,
as DLookup("ThisField", "tblMisc")

hth


--

Joe Cilinceon



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add New Field to DB Karen Database Design 7 October 19th, 2005 08:03 PM
Survey Results SAm Running & Setting Up Queries 10 May 17th, 2005 08:32 PM
Access combo box-show name, not ID, in table? write on New Users 30 April 30th, 2005 09:11 PM
Table Design A. Williams Database Design 3 April 29th, 2005 07:02 PM
unable to repair inobox Sudheer Mumbai General Discussion 1 February 20th, 2005 12:55 PM


All times are GMT +1. The time now is 12:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.