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
> Lookup Query Or Lookup Table, Access 2016    
 
   
ntambomvu
post Apr 9 2020, 08:54 AM
Post#1



Posts: 57
Joined: 20-November 16



Hello Chaps-
Your experience will be able to guide me in a decision regarding the tables in my contacts database.
I capture the following data about a contact.
1. Type of contact ( business or personal )
2. type of business ( manufacture, merchant, service and repair, government )
3. field of business ( engineering , food, farming , )
4. size of company ( no of personnel , no of branches , local or international)
5. business sector ( transport , earthmoving , fishing , clothing , food, beverages )

The person capturing the data selects from a dropdown list and the detail is concatenated into
a profile.

my question is the following
At the moment I am keeping each of the categories (1-5) in a separate table and each of these tables
has up to 20 ( or more) records.

Is this a good way of keeping the data ?? it seems to me a bit clumsy and I would prefer a more elegant
solution

What do you think?
best regards
Fred






Go to the top of the page
 
GroverParkGeorge
post Apr 9 2020, 09:23 AM
Post#2


UA Admin
Posts: 37,265
Joined: 20-June 02
From: Newcastle, WA


That's the appropriate way to do it. I suspect you're wondering about a table design that would try to combine all of those categories and elements into one big "MUCK" table. I think the general consensus among experienced database developers is that that way lies danger.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kfield7
post Apr 9 2020, 12:24 PM
Post#3



Posts: 1,069
Joined: 12-November 03
From: Iowa Lot


An alternative table structure I've seen used but don't necessarily recommend might look like:

tblCatType
CatTypePK
CatType

1 Contact
2 Business
3 Field
etc.


tblCategories
CatPK
CatTypeNum
Category

e.g., a combined lookup table might look like: (I'm not sure if this is the MUCK table to which George referred, or if he meant a spreadsheet format)

1 1 Business
2 1 Personal
3 2 Manufacture
4 2 Merchant
etc.

While it might reduce the number of lookup tables, each combo box etc. has to filter the category type for its selection.
It can make the logic a little more complicated. I saw this structure in someone's FoxPro system once, I had to pull data from it, it drove me nuts. But that doesn't take much.

Actually, I currently interface with a system that does something like that - but I usually don't have to work directly with said lookup table.
This post has been edited by kfield7: Apr 9 2020, 12:33 PM
Go to the top of the page
 
GroverParkGeorge
post Apr 9 2020, 12:33 PM
Post#4


UA Admin
Posts: 37,265
Joined: 20-June 02
From: Newcastle, WA


That is exactly the structure the Muck Table article refers to....

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kfield7
post Apr 9 2020, 12:43 PM
Post#5



Posts: 1,069
Joined: 12-November 03
From: Iowa Lot


OK, I should have read the article.
It appears MUCK tables are fairly common - and the article reminded me of why I don't like to use them.
Just because I've seen them used - and still see it - doesn't mean it's a good idea.

Thanks, George.
Go to the top of the page
 
GroverParkGeorge
post Apr 9 2020, 12:46 PM
Post#6


UA Admin
Posts: 37,265
Joined: 20-June 02
From: Newcastle, WA


thumbup.gif

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ntambomvu
post Apr 10 2020, 12:54 PM
Post#7



Posts: 57
Joined: 20-November 16



Hello George-
I looked up the link that you gave to me about "MUCK" and I thank you for the information.
I must admit that while I can see the logic in what he is talking about - I think that I still
have a long way to go before i get to that level of competence. i will in the meantime continue
with my present structure
I am hoping that I can limit the level of relationships to an absolute minimum and that with
forcing relationship integrity all the time I should be able to keep everything as simple as
possible and still have a working program
regards

fred

PS after reading kfield7 - i fear that i am going to create "muck" but what is the alternative
This post has been edited by ntambomvu: Apr 10 2020, 12:58 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    30th May 2020 - 11:00 AM