UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Store Options, Access 2016    
 
   
ludde__
post Nov 27 2017, 04:06 AM
Post#1



Posts: 7
Joined: 25-December 14



Hi

First of all, my first topic here but I have used the tremendous knowledge on this site for years and it has helped me a lot with my databases that I have built. I have been "offline" from Access a while but I am starting a new database and I have came across a question that I want some input on. I use Access2016.

I have multiple combo boxes in different forms that handles options, like contact type, access type, access limitation, information type, country etc. Country is easy, I have put all countries in one table with countries_id;countries_name;countries_code; etc currently holding 240 posts. But for other types of data I have earlier treated this in 2 different ways, sometimes I have created a specific table for lets say contact type, containing contact_typeId and contact_typeName, having maybe 3 posts in total. Sometimes I have added it to a generic table called data, in this table I have data_typeId;data_typeName;data_typeString where the typeName could be contactType for one post and the typeString could be "MainContact". Latter choice will have less tables but the data table will grow over time as more options are given in forms.

Now my question is obviously what is the best way of doing this? Should I have lots of tables just containing a few posts or is it better to have a generic table with lots of data in that is used for various options? One thing that might affect the answer is that one form can contain multiple combo boxes picking data as well as the use of subforms that also can contain multiple combo boxes.

One idea I have is to use the option that puts the least pressure on the database but I am to basic in my knowledge to answer this question myself.

Looking forward to some input from this amazing community!

Björn

Edit, just checked my profile, looks like I have posted another topic, totally forgot about that smile.gif
Go to the top of the page
 
ranman256
post Nov 27 2017, 08:20 AM
Post#2



Posts: 788
Joined: 25-April 14



if you have LOTS of options, I use a single table with options for different things.
Countries are a stand alone table,
but for like for a classroom, I use 1 table for all the options:
Faculty types use a query to pull FAC options only
Class status: ClassStat
Attendance: Attend

OPTION, BATCH
---------------------
Teacher, FAC
Custodian, FAC
InSession, ClassStat
SnowDay, ClassStat
Holiday, ClassStat
Present, Attend
Absent, Attend
Sick, Attend

this way I dont have to make dozens of little tables for options.
Go to the top of the page
 
ScottGem
post Nov 27 2017, 08:31 AM
Post#3


UtterAccess VIP / UA Clown
Posts: 32,183
Joined: 21-January 04
From: LI, NY


I've used both methods. A single lookup table with a type field and separate lookup tables. I've found the separate to work better. The only time I've found a single table to work better is when there are lots of overlaps.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
projecttoday
post Nov 27 2017, 08:41 AM
Post#4


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


I would go with multiple tables unless there is some reason for combining them.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Jeff B.
post Nov 27 2017, 08:49 AM
Post#5


UtterAccess VIP
Posts: 9,885
Joined: 30-April 10
From: Pacific NorthWet


Are the "options" likely to change over time? That is, more, fewer, changed?

An example of the types of data would give folks here more to go on...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Nov 27 2017, 09:01 AM
Post#6


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


My thinking on this subject was heavily influenced by this discussion of what the author calls "MUCK" tables. (You may have to register to read this, but SQL Server Central is worth the trouble, IMO.)

--------------------
Go to the top of the page
 
ludde__
post Nov 27 2017, 10:32 AM
Post#7



Posts: 7
Joined: 25-December 14



Hi Jeff

QUOTE
Are the "options" likely to change over time? That is, more, fewer, changed?

An example of the types of data would give folks here more to go on...


They are likely not to change, however I find no big issue in this as I have a key, for example any option text for contactType have this key, so I will will only change the specific context and not risk in changing something completely else. However, as I write, I think I see a point here, if I would to have similar options there is a risk for reusing and if so any changes can be catastrophically.

Some examples:

contactType: Main, IT, HR, Finance
accessType: Full, Limited, Revision
informationType: All, Sales only, Emergency
endReason: Lost procurement, Inhouse, Bankruptcy, Disappointed

Hopefully this gives you an idea on what types of options I am talking about.
Go to the top of the page
 
ludde__
post Nov 27 2017, 10:47 AM
Post#8



Posts: 7
Joined: 25-December 14



Hi Grover

QUOTE
My thinking on this subject was heavily influenced by this discussion of what the author calls "MUCK" tables. (You may have to register to read this, but SQL Server Central is worth the trouble, IMO.)


Thank you for the link, that is very useful in my search for answers in this question. I still like the idea of these "MUCK" tables, however I start to see the issues with it and a particular line I got stuck at was

Some might say that these examples are far-fetched and that no reasonable person would go that far. But that is exactly the point, how do you know when you have gone too far?

My aim is that this will not be the biggest database ever, but I do not know where it will end up eventually. The scope is somewhat limited now, but I know since older builds that they tend to grow and as I understand the article it is when something grows that the problems starts to appear.

Not sure how I will go on with this, but I am definitely taking this article into consideration
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 05:41 AM