Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Unanswered: I Need Some Help with Auto Generate String

    I need some constructive input to be able to finish my program.



    I have a field called (1) DateRec, another (2) Municipality and another (3) Type. These three fields invariably will automatically generate a string of number for another field that is called FileNumber. This FileNumber is already the Primary Key in the program. The (1) is the date received, that is the date the application of the client was received. (2) The Municipality field will be programmatically structured to produce one or two letter characters that will represent each Municipality in the FileNumber for every application within the County where the applicant is from. Right now in the program, all the 23 Municipalities are listed in a combo box with their full names. And (3) which is the Type is the state of the application, it will only be involved in the FileNumber, when it is an "EXEMPT", then, the nomenclature will be; an 'E'.



    Here comes the drill! When the user enters the date received, it will be a complete date, with day, month and year. The only thing that will go to the FileNumber will be the year, like 2006. When a Municipality is chosen by the user, for instance Atlantic City, it will generate a nomenclature like 'AC', if it is for Brigantine it will be a 'B'. Each Municipality will have a unique identifier of one or two characters only. And there are twenty-three Municipalities in all. Then the program will run a query that will check for the last FileNumber under that Municipality the user has chosen and update the middle number with one number increment. So the FileNumber will be something like AC-2-2006 or B-1-2006, for another entry on this same Municipality it will be AC-3-2006 or B-2-2006, AC-4-2006 or B-3-2006 etc. Hence, the middle number will be like a counter that will help the user to know the number of applications for each municipality within a certain year. Then, when there is an Exemption in the application, the user will select EXEMPT from Type, it will emerge the 'E' with the middle number; like AC-5E-2006. For this to work the program will enforce the user to enter Date received, and the Municipality and check for Type before he exit, the program. This is all that is needed.



    I definitely, need a help to get this working. The purpose for this is to debar the user from entering wrong File numbers.



    Thanks in anticipation for your help.

    Anthony

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Anthony - welcome to the forum

    I am afraid that you have broken one of the fundamental rules of relational database design. Please have a gander at this. Basically - you don't want to make you PK a derivation of other fields. You could, of course, have a composite key but not this.

    if you sort out your primary key and also your relationships between tables you will probably be most, if not all, the way there to answering your question

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    2
    Hi Pootle,

    I appreciate your effort to help, but your answer is quite hazy to me. I don't understand what really you want me to do.

    Are you saying that by my sorting the primary key and the table relationship will solve my problem? How please?

    This program is not in SQL, it's MS-Access platform. I need more input and clarifications.

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Anthony

    Did you read the link? Your issue is about normalistion which the article gives a good introduction to.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Anthony Eze
    This program is not in SQL, it's MS-Access platform.
    What's the difference?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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