Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: inserting records -autonumber (access 2003)

    After finally realizing what autonumber is for (NOT to generate sequenced ID codes -thanks all), and getting the bare bones of most of the tables, queries and forms running... I'm encoding some data (yey).

    How do I insert a record into the middle of a table?

    I'm encoding old data (some from paper files made three years ago), which means they follow a certain chronological order. While I do have a separate field for dates which can be used to arrange the data properly, I would still like to keep the sequential order of the autonumber field and the datefield identical (I say date field, but it's just a textbox with a mask). This does not help much in the database operation, but I find it rather pleasing to follow.

    My problem is that since the paperwork is not properly organized, I would often find october forms in the december folder. This means that I would have to encode the new data, arrange rows by date, delete the autonumber field, make a new autonumber field and cross my fingers that the new autonumber field labels the latest entry as if it were an old one (and why does autonumber keep track of deleted records too?).

    My first field for some of the forms is the autonumber field named which I sometimes use as primary/foreign keys.

    I do this mostly for testing the DB with the actual data. I put in some junk info when I was building it. I'm testing my forms for user friendliness and I want to see if they would still be pleasing to the eyes when they are filled with giant walls of text (instead of wasting energy creating loads of new gibberish data, I figured I might as well use the real ones).

    btw, I strangled the person who was in charge of records. =D

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    There is no order to records in Access unless you supply one. Records are returned in the most expedient manner determined by the Jet engine. Think of your tables as a bucket of records. You give it order by using queries and supplying an OrderBy Clause. Therefore, inserting into the middle of a table depends on your sorting order only.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    I noticed that too. My little snag is that:

    this is my table:

    autonumber field, date
    1, oct1
    2, oct2
    3, oct3
    4, nov1
    5, nov2
    6, nov3
    7, dec1
    8, dec2
    9, dec3

    When I add a new record: (like oct4), it becomes

    10, oct4

    Of course, when I sort by date, they line up correctly date-wise (which is what I need anyway). Getting the date and autonumber to sync is a purely aesthetic notion I was playing around with.

    I did notice that even if I deleted any of the previous records, the next number will be 11. Maybe I'll try playing with dcount or something.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by coffeecat
    Getting the date and autonumber to sync is a purely aesthetic notion I was playing around with.
    srsly, you are just wasting your time

    use autonumber for what it was intended for -- uniqueness, nothing more

    either that, or stop using an autonumber

    if you want to see your data in some particular order, use ORDER BY in your SELECT query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the monthx notation is going to hurt you badly in the near future
    first off it limits you to 9 files on anyone month, if you have more than 9 files the sort order will fail
    assume you went nov1,nov2.....nov9,nov10,nov11
    if you try to sort on that you will get
    nov1,nov10,nov11,nov2....nov9
    it cannot handle years so so next years november files will be appeneded (say nov1..nov6 are last year, 7 on will be 2008....)

    if you need a refeence then arguably the autonumber is fine for that.. however Ive seen lots of organsisation obfuscate autonumber columns, particularly invoice, grn or order numbers (so that customers or suppliers cannot see what volume of trade they are doing).

    a way of doing that is to prepend some seeminlgy random characters in fron of the autonumber on reports etc...

    eg HK16000251 could be the row containing the autonumber 251 the HK16 identifies the year month and day.. the HK16 is totally meaningless in your system.. its just there to hide the number of 0's in front of the true reference 251. its a bit like a utility company reference, they don't want or need the full reference they 'just' chop out the bit they use from the number your quote them

    as others have said providing you have got a date in your row (and assuming you have stored it as a date not text) you can pull out whatever data you require in wahtever order you require

    you can pull out dates based on month
    say you wanted to pull out all files in Novemeber
    you can also apply a date order

    eg
    select my,column,list from mytable
    where month(mydatecolumn)=11 and year(mydatecolumn)=2007
    order by mydatecolumn

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2008
    Posts
    163
    @Healdem

    I really appreciate the help. And you also solved something I have not even asked too! =D

    This is the second time that the examples are causing me more trouble than intended (I keep forgetting that you guys are used to having samples of the actual data in question presented). I only used the "monthX" for that post (it makes for an easy example -and as I realized, also a misleading one too), I have no intention of actually using that as my format. Sorry for the confusion.

    select my,column,list from mytable
    where month(mydatecolumn)=11 and year(mydatecolumn)=2007
    order by mydatecolumn
    I was looking for the proper way to string something like that. Thanks again.


    @r937

    I've practically stopped using autonumber for any practical purposes. For some bizarre reason I seem to want to keep placing a field of it in my tables. It's sad, I know.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by coffeecat
    I've practically stopped using autonumber for any practical purposes. For some bizarre reason I seem to want to keep placing a field of it in my tables. It's sad, I know.
    thats odd...
    looking at most of the db schema I see Id expect to see autonumber used in at least half the tables. Its fairly rare to come accross a db schema where its virtually never used.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed. I use AutoNumbers quite a lot. Among other things, they can be quite useful for sorting records into "as entered" order.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jun 2008
    Posts
    163
    Among other things, they can be quite useful for sorting records into "as entered" order
    In my case, some of the data from as far back as 2005 have never been encoded on a computer. Since everything is on paper, it is often that receipts containing older data is found improperly organized with newer ones. Autonumber will label it according to as encoded, but not really according to when it was filed.

    I do want that 'as entered' functionality, but I want it to reflect the correct order of the transactions. which is more or less why I started this thread.

    @startrekker: the whole accounting table and its associated forms are on a separate DB, but it links to the one I showed you (and no, I'm still trying to understand linked tables =D )

    Its fairly rare to come accross a db schema where its virtually never used.
    oh.. is there anything I should be mindful of if my PK isn't an autonumber? =(

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make very sure it's truly unique.

    If you want to sort a list by the date it is filed, then the table needs a DateFiled field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StarTrekker
    Make very sure it's truly unique.
    actually, you don't have to, because the database will tell you if it isn't

    and it only has to be merely unique, not truly unique

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, but it's far better to realise the problem BEFORE the database tells you so that you can design around it... you won't have to "fix up the mess".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what i meant, and apparently failed to communicate adequately, was that if you declare a non-autonumber PK, then the database will tell you if you try to insert a value that isn't unique

    i do believe that this qualifies under the "realize before the database tells you" scenario, as you cannot turn a non-PK column into a PK if it happens to contain dupes, nor can you not design uniqueness into a PK, so there is nothing to design "around"

    and i trust you noticed my smiley in regard to the difference between unique and tryly unique

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My point is that if you made a non AutoNumber field the PK and then went ahead and created the rest of the application, rolled it out and had it in service for a while and then NEED to add another record with the same value as another one (like making CustomerName a PK and you now have two John Smith's) then you'd have to backtrack and modify things to overcome the issue. It's better to realise that CustomerName is not unique in the first place and have an AutoNumber, then build the application.

    And yes, I noticed the smiley
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i understand that

    but my point is that if you use an autonumber and fail to declare a unique constraint, then you are wide open to inserting duplicates

    what is the #1 most common question on database forums? (i'm on three others besides dbforums.com)

    it is this: "OMG HALP, i haz duplicates and i need 2 delete all but 1 of dem"

    so, what to do? declare a unique constraint? yes!!

    however, in practice, this is exactly the scenario you are advocating against when you decry the use of a natural key as the PK !!1!

    so we're screwed if we do, and we're screwed if we don't

    having said all that, i do use autonumbers myself from time to time

    but i do not blindly use autonumbers because i might some day have two John Smiths

    the real question is how do you know the two rows are for different John Smiths?

    solve that little problem (and come up with a good unique constraint), and then the problems that derive from the use of autonumber become moot
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •