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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Custom Autonumbers    
 
   
BananaRepublic
post Oct 7 2010, 11:31 AM
Post#21


Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic


Just to provide a perspective: In Oracle and PostgreSQL where we have the concepts of sequences, basically a database object that spits out a number. We can associate any number of tables to a single sequence. I don't know how often they actually associate a sequence to more than one tables but there are some scenarios where they like to re-use sequences across more than one table. Typically, when we create a new record, we have to call a nextval() and for any subsequential records in other tables, we call currval(). Both functions has to identify which sequence we want to obtain the number from, so if I had a sequence named "city_seq_id", and this was used to generate primary key for a table "city", the column "city_id" would have a default value of "nextval("city_seq_id"), and if I were to populate child records associated with city (let's say a table of neighorhoods?), I would call "currval("city_seq_id")" as part of the application logic. The latter is very similar to how we use "SELECT @@identity;" in Access to retrieve the last inserted ID but with more granularity.
Hope that helps.
Go to the top of the page
 
BananaRepublic
post Oct 7 2010, 11:50 AM
Post#22


Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic


I agree that the specific encoding I suggested would not work for any scenarios involving multiple invoices with same customers in a given day and to keep the same encoding would require adding more digits (e.g. time value, perhaps). I guess we just have our preferences WRT which is easier to read; a pure numeric ID or an encoded ID. I'm a big believer in working well with humans who don't always necessarily memorize a large sequence of numbers and need a quick'n'easy reference to locate the documents needed. This may also have been colored by the fact that I worked in Case Management, where everything is much more human-oriented - we never refer to our cases as ID = XXXX but rather by the name of the client, and by same token, we never refer to any documents associated with casework with numbers but rather dates and some significant information (e.g. "locate the service plan I created for client John Doe in month of September - it should contain something about obtaining the financial assistance").
also speculated that for a small business, it would make more sense to have human-friendly encoding but maybe I'm the minority here.
O110% agree. We wouldn't want to try and search & find a full record & all its associated records using that encoded ID - I think that in general, it should be OK to locate the record using encoded ID then use the internal autonumber to collect all other related data. But you're absolutely correct - comparing a sequence of alphanumeric characters is always more expensive than comparing a pair of numbers.
Wasn't it me? I mentioned it earlier up in the thread but in regards to the control number? Or did you mean outside the thread?
Ahh, makes perfect sense. Thanks.
I agree that in financial section, the sheer volume of transactions probably will make it very unlikely that a encoded ID will be useful and numbers are simpler to deal with. I'm coming from customers' viewpoint. Consider how one would log in to their bank online to do some statement stuff - they don't actually enter their account number (at least my banks hasn't tried to get my account number that way), I enter an username and password, and I see my accounts but only by last four digits, for obvious security reasons. Yet, I am more likely to mentally think of my accounts as "Personal Checking", "Personal Saving" and "Money Market" than *0001, *0002, *0003, and that is how my bank internet site would describe those accounts. Though I may need to use my full account number to perform a wire transfer and make a deposit, when I'm talking with a banker (after validating myself which may or may not include sharing the full account number) or working online, I don't actually use the full account name but more human-friendly name of "Personal Checking" and in fact, it is possible to name an account to some kind of custom name. (I don't think that's useful outside the internet site, though but I've never bothered with it, though.) Anyway, when we look from the customer's viewpoint, it *seems* to me that it makes more sense to supply a human-friendly ID that will improve chance of it being memorable and readable in future interactions. The same conversation would certainly not apply between say, an auditor and a banker who can only talk about data in terms of account numbers (the fact that an account is owned by John Doe has no bearings to either of them unlike the earlier scenario where I'm talking about my account).
I don't think it'd be wise to suggest that we have both control numbering and human-friendly encoding so most likely, we'll have to choose what best suit the business, I guess.
Go to the top of the page
 
argeedblu
post Oct 7 2010, 12:37 PM
Post#23


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


HI Banana,
I'm just going to make a general reply. I think we are pretty much in agreement that the solution has to fit the scenario and its business rules. You are coming from a perspective of finding information about people and I am coming from a perspective of accounting and audit controls.
Oagree that referring to people as numbers is awkward and disrectful of the person. However, in the electronic data management realm, I see no need to create artificially encoded identifier to help with the search for a person's information. (My comment about paper filing systems may well have been referring to your earlier post in this thread.) When I am implementing people searches, I use combos or listboxes and let the user initiate the search by typing the person's surname for starters and then displaying distinguishing information to help narrow down the search.
Glenn
Go to the top of the page
 
datAdrenaline
post Oct 7 2010, 12:47 PM
Post#24


UtterAccess Editor
Posts: 18,006
Joined: 4-December 03
From: Northern Virginia, USA


Wow ... lots of good dialog ...
Back to the what seems to be the start: "You shouldn't use an actual Autonumber field, as per best practices they shouldn't ever be seen by the user", in short, the visibility of an Autonumber.
To that end, I beleive our varied discussion supports my response that the statement of "shouldn't ever be seen by the user" is not a "best practice". Again, I say we should implement what is best for the application's scope and purpose and evaluate how a replacement of an Autonumber will differ from the one Access (Jet/ACE) already provides you. Note that in an application, it may be just fine to show an Autonumber for a table, and in another table of the same app its more appropriate to hide it. For example, on a very simple side of things, I show the Autonumber quite often on my "top level" (aka: Primary or Core) tables, however, rarely, if every, would I show the Autonumber associated with records from Domain (lookup) tables or the Autonumber associated with records from "child" tables. I often don't like absolutes when it comes to design practices since there are always scenarios in which a deviation is appropriate, or non-harmful.
----
I am with Jack, rarely have I found that companies use the mentioned methods as the primary search mechanism. Also, those mechanism's just don't apply in several application. I personally develeped an manufacturing process managment app, there is NO WAY we could use those mechanisms. The numbers were the key ... all other searches were secondary. With respect to training, now the focus is on how to verify the record you think is a match to what you seeking is indeed the record you are seeking.
PS ... when I know the website, I just type in its address, no need for my favorite search engine ... When I am on my personally network, I type in the IP addy's
With the NewValues property set to increment, it will take a LONG time before you get to the negative numbers (but you can get there!). If you Replicate the data, an Increment setting for New Values is automatically changed to Random, or the designer could specify Random, and thus introduce negatives. Any behavior other that what is specified in New Values is/was caused by bugs or inappropriate appending of data into the Autonumber field. Inserting records, could prove to be problematic for either system of numbering (Autonumber or code generated number). As I stated earlier, in a couple of replicated systems I've supported, I display the autonumber values as HEX (I was given the idea from a UA discussion quite sometime ago), thus avoiding the surprize of users when/if the experience negative id numbers.
This seems to assume the only record is on paper. Any audit I have been involved in (two) has solely been focused on the financials --- PO's matched to withdrawals, Invoice's matched to deposits... both were a nightmare, but neither audit required a contiguous series of numbers. I am not saying its not benificial, but I do know that gaps in series numbers was not an issue. Possibly because there was never any claim (via company policy or statement of adherence to a standard) indicating the numbers were contiguous. I am not an accountant/auditor/etc. so please do not use my experience as "the law"!
In a theoretical sense an identifier of "BANREP20100905" is a calculation and should not be stored Others may view it as a violation of normalization since it represents more than one attribute. I see an identifier like that and to me, its all metadata that should be easy to gather by looking at the document it represents. Also, I find it very efficient and self explanatory really to tell the user --- type in the id here, then hit enter. If the id in not known, then you can search by <blah> or <blah> over here.
Again these types of nuances go back to what specification do you need to adhere to; what do your users expect and what will they know (or can gather) when searching; and what type of information are you searching for. In your examples, you are assuming a two person interaction to find data --- likely a scenario that will involve different mechanisms of searching. Where as, if your job is order fulfillment and you are looking at a list of orders to fulfill, you'll likely want to search by the id number.
In my experience, the auditors did not care. It was based on financials, the internal mechanisms of tracking was only mentioned for the auditors to understand our processes.
I think they can garantee generation in sequence as well as continuity --- BUT --- the values generated may NOT be stored, or once stored, actions (like deletions) create the scenario of non-contiguous numbers. But, if the app your creating requires neither spec (continuity or sequence), then the Autonumber works very well and may warrent its display to, and use by, the user.
----
Ultimately, it is our responsibility to know how to implement what is required by the application that is being created in an efficient manner and avoid scope creep. Knowing the behaviors/constraints/etc of using Autonumbers, code generated numbers, surrogate keys, adherance to normalization, DAO, ADO, etc, etc ... helps us implement what is needed to keep the data clean, the application solid, and the client satisfied --- and thus reach Expert status, and leave everything grey (simplicity often rules the day).
Go to the top of the page
 
BananaRepublic
post Oct 7 2010, 01:50 PM
Post#25


Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic


Well, I don't know the specifics of your process but I'd think that even for purely numeric data, they still do encode some information. They may name a part 0013-49-0138, in which each components means something (e.g. part number, model number, batch number for instance). I'd be interested to know if a purely arbitrary number is actually used instead of some kind of encoded number as mentioned. Even for something complicated as screws, there are already published standard classifications that companies use to specify what kind of screws they want for their assembly and they are more likely to use that standard encoding than an arbitrary numbering, I would think.
Oagree that this is technically a calculation and thus we have to give consideration as whether it's in fact appropriate. It should certainly not replace any data (e.g. Invoice date for example) which should still be in its own column. It also has the unfortunate effect of opening a can of worm in that if we revise an invoice on 2010-09-06, do we need to change the identifier to this or keep the original calculation (thus implying that it has to be stored at the time of creation albeit never ever updated).
Actually while I'm assuming two person interaction, we also need to take in consideration of who those people are. For example it's not uncommon that a client walks in, and find that the primary caseworker is not available for whatever so has to work with a different case worker who do not have all the information of where the client stands on the case, so the caseworker has to depend on the client to supply the information to locate the record. This won't be of much interest to your and Glenn's cited examples of people working in audit or fulfillment where they won't really care about the actual data (e.g. this order needs 10 widgets, this account belongs to a John Smith, etc. Not interested in those data). However, this would also, I think, apply to customer service where it is unlikely that the customer representative will have all the information and is dependent on the client to supply the information but if client isn't exactly rigorous in their record-keeping, it tends to come out as "bad customer service" if the representative told customers that they can't do anything because the customer did not write down the invoice number even if that was the only invoice that customer made with the company in the entire year. So even though you may know the website of UtterAccess or IP of your personal computer, it doesn't mean that your cousin knows the same thing. <
I would also suggest that by having a encoded ID that contains some of metadata, we can help simplify the search scope. To illustrate quickly, on the form we can have two cascading comboboxes, one listing like:
CODE
BANREP -- Banana Republic

and the other cascading comboboxes would then list all dates of invoices filed by "Banana Republic". It's much more probable that the customer in question know that he's a representative of Banana Republic (if he didn't know, we have a MUCH bigger problem!!! wary.gif, and only need to be approximate with the dates. This is at least in my mind more efficient (from a human user perspective, not necessarily in terms of query time) than a freeform search where we need to search on different columns. Of course we have to keep in mind the can of worms I spoke about.
I absolutely agree that we have to flexible and adapt to the specific scenarios. I do think we also have to remember that once we've trained a group of users does not necessarily guarantee confusion won't arise in future when the turnover has replaced most of those employees and the "corporate knowledge" becomes lost. If we can architect a structure that does not assume pre-existing knowledge (e.g. knowing that autonumber aren't meant to be contiguous), I think we'll be better for it.
EDIT:
I should clarify the last statement - when I talk about corporate knowledge, it's very clear that a new hire must be trained in nuances of company's processes so that component of knowledge cannot be afforded to be lost (else, the company goes out of business summarily), so it's very reasonable to expect that user adhere to the company's specific process and understand the logic behind it. Therefore, when I was talking about losing knowledge, I was referring more to the "extraneous knowledge" that is not strictly required as part of user's job (e.g. knowing about autonumber not being contiguous). If the application requires its own training apart from the mandatory training required to perform the job for company X, then I do not feel that the application is well-designed. To make this clear - nobody is required to actually read a car manual in order to drive it - one should be able to go from one car to other car and drive it just equally well without any additional training. If a car company designed a car that required different way of driving it (and it's not a 18 wheeler or anything non-car-like) then nobody will want to buy that car. I feel that same principle ought apply to custom application we author for other people.) I hope that helps clarify things....
Go to the top of the page
 
jleach
post Oct 7 2010, 01:58 PM
Post#26


UtterAccess Editor
Posts: 10,225
Joined: 7-December 09
From: St Augustine, FL


For inventory items, yes, there's generally a pre-set number that represents something, but that's about it. Certifications, Test Reports, Purchase Orders (coming and going), Quotes, Invoices, Packing Slips, etc etc etc, all ID'd by a number which is not related to anything else.
Go to the top of the page
 
rabroersma
post Oct 7 2010, 02:00 PM
Post#27


UtterAccess VIP
Posts: 1,301
Joined: 1-January 07
From: Whittier, California, USA


>Don't forget to add check digits to prevent data-entry errors. < Also if encoding schema are to be used it would be good to consider what conventions are already establish for the industry whether they be ISO (international), national, or discipline specific.
Go to the top of the page
 
datAdrenaline
post Oct 7 2010, 03:58 PM
Post#28


UtterAccess Editor
Posts: 18,006
Joined: 4-December 03
From: Northern Virginia, USA


Ok ... I chuckled with 'BANREP' ... Where's that hammer! ... Made me think of when I had to write code that prevented writing values that appeared as 'BAD' or 'DEAD' when viewed with a Hex editor.
Go to the top of the page
 
debmars
post Oct 23 2010, 02:34 AM
Post#29



Posts: 121
Joined: 28-October 09
From: South Australia


This is an interesting debate. With my accounting system I have to copy the purchase orders and invoices supplied by the company I'm dealing with. One Company has this 657890899 as their purchase order number. So I cant have an auto-number sequence without modifying it. So I have to have to be able to duplicate that. With this date idea how would it work with this type of numbering because I would Imagine that the whole line in each identifier would be too long. Just a curious question
Cheers
Mars
iconfused.gif
Go to the top of the page
 
argeedblu
post Oct 23 2010, 04:47 AM
Post#30


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


Mars,
For what it's worth, yours is a situation where I would definitely use an autonumber as the record identifier (used internally by the database to manage the data) and a separate field for the invoice number (however it is formed) as the external information identifier.
That said, I haven't answered your question about the 'date idea' and I am not sure what you mean by 'the whole line in each identifier'.
Glenn
Go to the top of the page
 
adb_seeker
post Feb 11 2012, 12:04 PM
Post#31



Posts: 12
Joined: 1-September 09



I ran into a problem when I changed the data in the module to what is in mydb. When I test the function by running ?getautonum(ERNum) I get compile error "Variable not defined" and dbtext is selected. The Enum portion of the module is highlighted in yellow. What am I missing?
Go to the top of the page
 
jleach
post Feb 11 2012, 12:12 PM
Post#32


UtterAccess Editor
Posts: 10,225
Joined: 7-December 09
From: St Augustine, FL


Did you add the ERNum as a member of the Enum as described in the instructions?
th
Go to the top of the page
 
adb_seeker
post Feb 11 2012, 12:38 PM
Post#33



Posts: 12
Joined: 1-September 09



Hi, Jack
After placing my information in the fields indicated, I compiled and and saved my database. When testing the function, I get compile error "Variable not defined". dbText is selected and the getautonum portion of the module is highlighted in yellow. What am I missing?
Go to the top of the page
 
adb_seeker
post Feb 11 2012, 12:39 PM
Post#34



Posts: 12
Joined: 1-September 09



Yes. I replaced dsAutoNumQuote = 1 with ERNum = 1
Go to the top of the page
 
jleach
post Feb 11 2012, 12:51 PM
Post#35


UtterAccess Editor
Posts: 10,225
Joined: 7-December 09
From: St Augustine, FL


I'll have to look into it... last time I looked at this module was when I posted it here, so it may take me a day or so to get refreshed with it. I'll be in touch...
Go to the top of the page
 
adb_seeker
post Feb 11 2012, 12:55 PM
Post#36



Posts: 12
Joined: 1-September 09



Thanks. I appreciate your help.
Go to the top of the page
 
jleach
post Feb 11 2012, 03:57 PM
Post#37


UtterAccess Editor
Posts: 10,225
Joined: 7-December 09
From: St Augustine, FL


Couple questions:
That version of Access are you using?
All modules compile correctly after you import them? If so, do you use Option Explicit at the start of your modules?
Do you have DAO selected in references? (for 2003 or before this will be "Microsoft DAO 3.6 Object Library", or 2007/2010 will be "Microsoft Office #.0 Access database engine Object")
Go to the top of the page
 
adb_seeker
post Feb 13 2012, 10:51 AM
Post#38



Posts: 12
Joined: 1-September 09



I use Acess 2000. The modules compile correctly if I make no changes and test in the database you posted. When I change the data, it no longer works (even in the database you posted).
Although I've used Access for many years, I'm uncertain as to where to go to select DAO in references. Thank you for your help.
inda
Go to the top of the page
 
jleach
post Feb 13 2012, 06:38 PM
Post#39


UtterAccess Editor
Posts: 10,225
Joined: 7-December 09
From: St Augustine, FL


Hi,
The DB I posted would have had the DAO reference - maybe this is why it isn't working in yours?
In the VBA Editor, go to Tools -> References, and you'll see a list. You're looking for "Microsoft DAO 3.6 Object Library". If it's not selected, find it in the list and select it. If it is selected, then it's some other problem.
Let me know,
hth
Go to the top of the page
 
mefrogs8
post Apr 25 2012, 02:38 PM
Post#40



Posts: 11
Joined: 29-March 12



I am using Access 2007 will this work? And do I copy the information in your zip file to my file or do I just create from your instructions into my db? Thank you!
Go to the top of the page
 
3 Pages V < 1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2019 - 12:03 AM