Results 1 to 15 of 15
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Autonumber, odd and even (A2k SR1)

    Table one is linked to Table 2 as a one to many relationship. Both tables contain an autonumber field. I need to ensure that the two autonumber fields can never contain the same numbers. Is there any way to easily set the autonumber field in the first table to odd numbers and the autonumber field in the second table to even numbers?

    I Miss the Search Feature... <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autonumber, odd and even (A2k SR1)

    In short, no. If you are using SQL Server you can set the initial and increment values, but in Access you get one of two choices - incrementing by one or random. You might consider the latter, or you could put a flag in a field adjacent to the autonumber and use the combination as your key.
    Wendell

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Autonumber, odd and even (A2k SR1)

    Thanks Wendell.

    I thought maybe there was something I missed. Using a flag may work but I think I will just use some code to generate the field.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber, odd and even (A2k SR1)

    I can't say that I understand the need for this, Gary. Autonumbers by their nature are only unique in that particular table. There really isn't any way to make them unique across tables. Even in SQL Server, it doesn't really make sense to do it this way.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Autonumber, odd and even (A2k SR1)

    Do they need to be AutoNumbers?

    If not, you could setup a table with the last number used for each table, one being an even number and the other being the odd number. When you require an even number you just add 2 to the last used even number and use that, same obviously goes for the odd number.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonumber, odd and even (A2k SR1)

    You could set the autonumbers to Random rather than sequential. This would make duplication VERY unlikely.

    Or, you could insert a record in one table with a very high ID# (like 10,000,000).

    But as Charlotte asked, why does it matter?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autonumber, odd and even (A2k SR1)

    I only know of one scenario where we found this kind of thing useful. We build a system that had addresses linked to both People and Organizations. So that we could tell on the fly whether a given address link belonged to a person or an organization, we started the Organization table at -2,000,000,000 and the People table at 1. It actually worked fairly well - but that was in SQL Server.
    Wendell

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber, odd and even (A2k SR1)

    Yes, and I've built systems like that as well, but I didn't put autonumbers in the individual tables, I put an autonumber in a single third table that held a record for the customer and then carried that value as a foreign key to the appropriate one-to-one subordinate table. The minute you make the autonumber significant in any way as data, you change its whole original purpose, which is merely to provide a unique key, not meaningful data.
    Charlotte

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Autonumber, odd and even (A2k SR1)

    Charlotte,

    Regarding the autonumbers being unique between tables. I am using code to fill a treeview control recursively by extracting data from a self referencing table (Obtained from the Microsoft knowledge base). I started with an example Hans provided but found that since there is an almost endless possibility of levels that the recursive approach works best. (Less code to maintain)

    This worked well until the users made a change to their requirements.

    Original requirements:
    Table 1 is created to store material data as follows:
    - Material one (Parent) makes Materials Two (Child) and Three (Child). Material Two (Now a Parent) makes Material Four (Child) and so on. No problems here. The data in stored in one table with fields for Parent and Child that reference each other.


    New Requirements.
    Table 1 stores Materials and is linked to Table 2 that stores categories as a one to many. Herein lies the problem.

    - Table1.Material One (Parent) is made up of Categories Table2.Cat One(Child), Table2.Cat Two(Child), and Table2.Cat Three (Child).
    Table2.Cat Two(Now a Parent) is linked to Table1.Material Two. and so on. The parent child relationship is now between two tables.

    What is happening is that the children in Table 2 now become a parent to data in Table 1. Using the self referencing table approach, I have a field for parent and child that drives the data for the treeview. As long as these fields are unique, the treeview code works as planned. (Thus the question on the autonumber being odd and even - I figured this would be an easy workaround if it could be done).

    Perhaps you and others could tell me if this approach deems following or would you suggest another. I may be going back to an original approach suggested by either Hans or Wendell (or was it you) that said to store the relationships in a different table.

    Of course, I must add my typical caveat that I apologize if this entire post makes absolutely no sense whatsoever.

    Thanks for the help.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber, odd and even (A2k SR1)

    What you are doing is going to give you fits. Put the relationships in a separate table. If you have a one-to-many between table1 and table 2, you can't logically make a table 2 record a parent of a record in table 1.
    Charlotte

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Autonumber, odd and even (A2k SR1)

    Charlotte,

    As I am already having fits with the second set of requirements, I will be putting the relationships in a separate table as suggested and building the treeview from there.

    BTW - do you know of anywhere I can find a treeview app that would show a many to one relationship? I searched the web using google and reviewed several but could not find one. Any ideas?

    ... Yes, showing a many to one is still a requirement (which I may ignore for now).
    Regards,

    Gary
    (It's been a while!)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autonumber, odd and even (A2k SR1)

    I won't repeat my earlier joke, but from your descriptions, I get the impression that you don't have a many-to-one relationship, but a many-to-many relationship. The Treeview control is not really suited to handle that, you would need some kind of diagramming tool or control. See for instance http://www.infragistics.com/products...x/interact.asp, but a Google search for diagramming ActiveX will find many others.

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Autonumber, odd and even (A2k SR1)

    Thanks Hans.

    Now that you mention it, this could be considered a many to many with the possibility that many of the many can go back to one. (Sorry - I couldn't resist that) On the positive side, the many going to one will always be to a new child. In all seriousness though, I will be looking at the link provided (thanks) to see if I can come up with a way to do this that meets the requirements. If not, then I may just have to plead temporary insanity and have the database transferred to our IT department for development which should take about 5 years to complete.

    Thanks again.
    Regards,

    Gary
    (It's been a while!)

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber, odd and even (A2k SR1)

    <hr>have the database transferred to our IT department for development which should take about 5 years to complete.<hr>
    Yes, but if you do that, you'll have 5 years to relax while *they* struggle. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> My suspicion is that the requirements are not thought through enough for you to really know what you have. A snake swallowing it's tail is not a good model for a data structure of any kind.
    Charlotte

  15. #15
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Autonumber, odd and even (A2k SR1)

    Yes, but it is a really long snake where the requirements constantly change.

    This is typical here. We are never able to get a really good set of requirements other then on the back side of a napkin. Typically, the request for an application is made and we talk about it. After several prototypes the app is developed for use and then you get, it would be neat to do this and this and this and this... which should have been mentioned in the first place so the requirements are constantly floating. Nobody has the time to sit down and define what they really want; they just expect you to use your ESP powers to figure it out.

    This particular application will be an interesting one to tackle.

    After more consideration, I am going to combine the Treeview with three listboxes. The treeview will show the parents which will always be a one to one or one to many relationship.
    Upon selecting a parent in the treeview, queries will obtain the data and show all of the children that created the parent selected and its children in the listboxes. The listboxes will be able to represent the many to one. I will still use the separate table to store the relationships.

    Thanks to everyone for your suggestions and ideas.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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