Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-01-2019, 01:32 PM   #16
RhiannonM
Newly Registered User
 
RhiannonM's Avatar
 
Join Date: May 2019
Location: Tassie, Australia
Posts: 19
Thanks: 11
Thanked 2 Times in 2 Posts
RhiannonM is on a distinguished road
Re: Primary Keys...Autonumber or Field Name?

Oh dear...what did I start?

First off...please don't complicate things...not for my sake at least...I have diminished capacity and am only using this database project as a way to retrain my brain as I once understood it and hope that by learning it again I can repair some damaged connections in my brain.

Secondly...it's just a pathetic albeit comprehensive recipe database and when I read the Access 2010 Bible they had a table that only used the category name as a primary key instead of an autonumber and I just wanted to make sure I was understanding it correctly.

I have several tables that are basically "single field tables"...they will be used to specify different aspects of the recipe and used as search parameters...primarily in combo boxes on a form and group headings in reports.

I don't plan on this database being for anyone else's use at this stage...it's more just to prove to myself I can do it...but I want to do it right.

So I apologise if I don't react with glee over your answers or thank anyone because unfortunately in this instance...i have no idea what you're all talking about LOL

Thank you for your time in answering in tho
Cheers
RhiannonM

RhiannonM is offline   Reply With Quote
The Following User Says Thank You to RhiannonM For This Useful Post:
Tera (06-01-2019)
Old 06-01-2019, 02:56 PM   #17
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,450
Thanks: 0
Thanked 571 Times in 567 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Primary Keys...Autonumber or Field Name?

Not the first time this topic has provoked a firestorm.

I offer this as summation: what you have set up with text fields as PK is just fine.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
RhiannonM (06-01-2019)
Old 06-01-2019, 06:11 PM   #18
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,676
Thanks: 93
Thanked 1,702 Times in 1,575 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Primary Keys...Autonumber or Field Name?

Quote:
Is there any benefit in having everything 'the same way'? especially if multiple/new developers
That's a loaded question. I happen to think that for group-oriented projects, you need some type of uniform style guide, to be set by the project leader. If you have multiple forms, for example, you probably want the same "look and feel" on all of the forms in a single project. I accomplished this by using template forms that were already set up with the right command buttons for our apps, and for which a control formatter was available to be called with a control name and a state name. It would do the work of determining the color scheme for that state and that class of control, and then applying it. That way, no matter what table was being managed, the forms that did the work were predictable. This reduced the learning curve for the user base and also took some of the detail-management load off the developers.

Even for a single-user, single-developer project, there is a benefit for trying to keep things more or less the same, because there is the time factor. As in, you get something working, then have to go work on something else. Then two months later, something is found to have been broken and suddenly you have to go back and work on it. If you maintained some consistency then it is easier to immerse yourself in that "blast from the past."

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.

Last edited by The_Doc_Man; 06-01-2019 at 06:16 PM. Reason: addendum for single-user single-developer case
The_Doc_Man is offline   Reply With Quote
Old 06-02-2019, 02:13 AM   #19
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Primary Keys...Autonumber or Field Name?

Don't forget that YouTube can be a good resource as well as Google.

https://www.youtube.com/watch?v=tIvAHNZr_d4

https://www.google.com/search?q=acce...hrome&ie=UTF-8
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-02-2019, 02:33 AM   #20
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,814
Thanks: 56
Thanked 1,028 Times in 994 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Primary Keys...Autonumber or Field Name?

I think I misunderstood a point @Micron made. I thought he was talking about a table with just one row, rather than table with a single field. I certainly wasn't attempting to start an argument - not even a 5-minute one.

In point of fact, I don't think it really matters whether you have a text PK or a numeric PK, it will still work.

I think in some cases text values can ultimately present some difficulties. Eg the periodic table has some unexpected (and maybe illogical) choices for the identifiers. So does the US state table. So do the 3LAs used for sports team, and the Alpha PostCodes used for British cities and postal areas. This happens when you have a relatively large number of options, with a limited alphabet to choose from, and the users need a very good familiarity with the abbreviations chosen.

And very good luck with your project @Rhiannon.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-02-2019, 06:04 AM   #21
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,676
Thanks: 93
Thanked 1,702 Times in 1,575 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Primary Keys...Autonumber or Field Name?

Your examples for difficulties with alphabetic keys includes a couple of cases I find interesting, and both of them have natural key implications.

The periodic table uses abbreviations of element names from multiple languages. So hydrogen is H - easy choice. C is carbon, no work there. S for sulfur, not hard to guess. But what about sodium and potassium? Respectively, Na is an abbreviation of Natrium, Latin for salt, and K from Kalium, the Latin name for that mineral. And then there is antimony - Sb for "Stibnum" meaning "mark" in Latin - because it would make a silvery smear of a mark on something used for testing such things.

The mixed origins of the names led to one-letter abbreviations in some cases and two-letter abbreviations in most other cases. Using the abbreviations therefore would lead to some ugly keys. Fortunately, there is ANOTHER natural key there that makes it easier - atomic number, which corresponds 1-to-1 with names. So there is a natural key anyway - just not the abbreviated name. We have to consider ourselves lucky. Dimitri Mendeleev formulated the modern periodic table but didn't insist on using the RUSSIAN names of the elements even though he could have. Back then, the world used the common Latin names because chemistry was already multi-national. Can you imagine trying to get modern countries to agree over what to call certain elements?

https://www.rigb.org/insideout/eleme...dic/index.html

But states? Holey Moley was THAT ever a mess to get started. It was over a generation ago that the USA devised the state-name abbreviations, about the time that the US Post Office switched to computer-driven character-recognition mail sorting. But there, we had no natural numeric key to use because 13 of our states were created at the same time. At least there was SOME semblance of reason in picking the abbreviations, though Alaska got screwed. AL would have worked but Alabama got that one. Alaska got AK. Leaving Arkansas and Arizona to fight. Arizona took AZ because Arkansas got AR. All sorts of 2nd-letter collisions got resolved and we have learned to live with what we got. BUT... at least with those abbreviations, you only need two bytes, which is a really nice, short "natural" key even if it was derived from unnatural selection.

So net result, Dave, is that your first two examples still use natural keys, not synthetic ones. One is text, the other is numeric.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-02-2019, 10:48 AM   #22
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,298
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Primary Keys...Autonumber or Field Name?

In some applications, I find that I have tons of simple lookup lists - status, rank, business type, category, transaction code, etc. After a while, it gets tedious to keep creating new tables and new forms for the user to maintain the values. Most of these "little" tables should be under the control of the user and the programmer never needs to be involved. I solved this problem back in the early 80's - Long before Access came into my life. My original version of this app was COBOL with IMS/DB as the underlying database. I reproduced it a second time using COBOL with DB2 and the version I am posting is obviously Access with ACE tables although the tables can be in whatever BE you want them to be. They should reside in the same database as the other tables of your application.

The concept of the app is to store all the "little" tables in a single table so that I only have to create a single interface. So there is a table of "tables" and the table details table. I import the forms/reports/tables into each new application so I never have to think about this basic requirement again. The app has changed very little over the years. It assumes a certain level of user authorization and that allows you to use the same concept for ALL tables, even those that must be controlled by the programmer because code needs to be written to handle different options. Things like States can also be stored here if you don't need links to cities or countries. A common solution like this one is per force limited in what it will support so not all tables can be stored here. Some of them need extra fields like countries or cities. And so I still make separate tables for the ones that don't fit my standard mold.

The codes table includes a sequence # field which is optional and allows you to specify a sort order different from alpha on the name field if that makes sense to the users. Sometimes a particular value is used so frequently, the users want it at the top of the list. The table also includes both short and long names which helps if you have limited room on a form or report. It also includes an active flag. You cannot delete an item once it is added so the only way to get rid of it is to flag it as inactive. When you create a query to use the list on your application forms, your primary sort is by the Active flag which makes all the True (-1) values sort to the top and then sort by sequence number and long name. That leaves the inactive items in the list (necessary or they will disappear when looking at an old record that includes one of these values) but gets them out of the way of current work. I also add code to the beforeUpdate event of the combo that uses one of these lists to prevent the user from selecting an inactive value.
Attached Files
File Type: zip TableMaintExample180204.zip (637.8 KB, 10 views)

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 06-02-2019, 01:52 PM   #23
RhiannonM
Newly Registered User
 
RhiannonM's Avatar
 
Join Date: May 2019
Location: Tassie, Australia
Posts: 19
Thanks: 11
Thanked 2 Times in 2 Posts
RhiannonM is on a distinguished road
Re: Primary Keys...Autonumber or Field Name?

How do i stop what I've started??

Seriously guys...this is way more information than my brain can deal with...I appreciate that you all have different opinions but I can't process any of it.

I just wanted a simple answer...certainly not 3 pages of posts to sift through.

How do I close the thread?

Cheers
RhiannonM
RhiannonM is offline   Reply With Quote
Old 06-02-2019, 02:02 PM   #24
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,450
Thanks: 0
Thanked 571 Times in 567 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Primary Keys...Autonumber or Field Name?

It's a discussion, it's the nature of forums. You aren't the only reader and this thread might be useful to others. You can ignore it from now on if you want since you have the info you needed. You might revisit it in a few months after working with db for a while and see if it makes more sense then. Or keep reading each post just to tickle your brain.

To reiterate summation: consensus is using text field as PK is fine and your single field tables are appropriate.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 06-02-2019 at 02:08 PM.
June7 is offline   Reply With Quote
Old 06-02-2019, 02:37 PM   #25
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,194
Thanks: 10
Thanked 226 Times in 214 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Primary Keys...Autonumber or Field Name?

not a bad idea to mark it as solved though. Prevents some/most people from opening it to contribute something when it's already solved. Maybe that's in the thread tools drop-down? I dunno because I don't think I've ever started a thread here.
Micron is offline   Reply With Quote
Old 06-02-2019, 03:01 PM   #26
RhiannonM
Newly Registered User
 
RhiannonM's Avatar
 
Join Date: May 2019
Location: Tassie, Australia
Posts: 19
Thanks: 11
Thanked 2 Times in 2 Posts
RhiannonM is on a distinguished road
Re: Primary Keys...Autonumber or Field Name?

I apologise if I came across as rude.

Was not my intention.

RhiannonM
RhiannonM is offline   Reply With Quote
Old 06-02-2019, 06:32 PM   #27
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,676
Thanks: 93
Thanked 1,702 Times in 1,575 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Primary Keys...Autonumber or Field Name?

RhiannonM, YOU were not rude. We were merely overly entusiastic at the chance to jump in and discuss something that might have been helpful. If that overwhelms you at first, let us know. But also, as you progress, June7's advice will probably be right. When you come back to the thread for review, you might find that it makes more sense.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
RhiannonM (06-02-2019)
Old 06-02-2019, 10:17 PM   #28
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,235
Thanks: 115
Thanked 3,073 Times in 2,791 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Primary Keys...Autonumber or Field Name?

Hi Rhiannon
We rarely close threads here at AWF as others may wish to add something new in the future.
However I have marked it as solved in line with your earlier comments.

Good luck with your project.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 06-03-2019, 12:49 AM   #29
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Primary Keys...Autonumber or Field Name?

You can also unsubscribe from the thread, so even if others contribute you will not get the emails.
The link is at the bottom of the email.?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help, need to update table with 2 foreign keys with autonumber from primary keys. meelos Tables 3 11-23-2009 07:05 AM
The Use of Primary Keys and Autonumber The_Doc_Man Access FAQs 0 05-24-2007 09:18 AM
[SOLVED] Autonumber Primary Keys Hickups Tables 2 08-20-2004 02:15 AM
2 Primary Keys + AutoNumber field help EPD Hater Tables 4 07-20-2003 10:55 AM
Primary Keys - Autonumber or Unique Field Value Cosmos75 Tables 8 06-26-2002 12:05 PM




All times are GMT -8. The time now is 04:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World