Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Perth, Western Australia, Australia
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated fields in a table (Access 97)

    I struggle with this program!!!!!!! Give me an old dBase app anytime!.
    Can somebody please advise me on how to get Access 97 to put the combined "surname" ", " firstname" into a fullname field in the same table????
    Or, how do I get a query to run that will do it when the appropriate data is entered?
    Thanks from an oldie
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Bob

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields in a table (Access 97)

    Instead of having a fullname field in your table, you can create one in a query by creating a calculated field. (example):

    FullName:[Surname]&", "&[FirstName]

    or in a report place the following calculated field into a textbox (example):

    =[Surname]&", "&[FirstName]
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Perth, Western Australia, Australia
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields in a table (Access 97)

    Thanks Judy, for your reply.

    I am however, using the fullname field as the link field in a relational database, and the sorting and displaying of the data would be done on this fullname field......

    ?? am I doing it the wrong way???

    Bob
    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

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

    Re: Calculated fields in a table (Access 97)

    You're doing it the wrong way for Access, yes. The names or any other data should only exist in a single table, never in more than one, that's a basic tenet of relational design. In other locations, you use a key that will allow you to link to the data, and in my experience, the autonumber is perfect for that sort of linking.

    Instead of using the full name, add a unique autonumber key to the table that contains the first and last names. Then use that autonumber as the link field to other tables. You'll still be able to use the concatenated first and last name for display purposes when the two tables are linked, but you won't have to worry about how to fix it if you misspell someone's name.
    Charlotte

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Perth, Western Australia, Australia
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields in a table (Access 97)

    Thanks Charlotte, thats basically what I've ended up doing at this stage.
    Perhaps you, or somebody, could provide a bit more advice.
    The database I'm working on is simply to record details of school truancies.
    Two tables
    ---- Student Data, containing:
    ID
    surname (25 chars)
    first (25 chars)
    contact Phone No. (10 digits)

    ----- Truancy Data, containing
    Date of Abscence (medium date)
    Category (Absent or Late, 6 chars)
    Sign-in time (if Late, medium time)
    Comments results of phone call to parents, memo field)

    As you can imagine, some kids are truant or late more than others...
    I need the system to detect if the name I am entering already exists in the database. If so, just add truancy details, else add student details then truancy details.

    As I said before, I grew up programming in a linear fashion (and the above problem was easily solved), but this old fogey <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> (although not quite an idiot <img src=/S/doh.gif border=0 alt=doh width=15 height=15>) is struggling to come to grips with Access and the modern, event/object oriented, GUI based stuff.

    Any assistance gratefully appreciated and recognised <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Bob<img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>

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

    Re: Calculated fields in a table (Access 97)

    Add your student ID to the Truancy data and you've got a one-to-many join. The simplest way is to use a form with a combobox on it for the name, assuming you don't have more than a few hundred students. If you have more, there are ways to filter the list down. The combobox would contain colums for the Student ID and their name, although you would set the width of the Student ID to 0" so that it doesn't actually show in the combobox. Then all you have to do is type in a name and the combobox will look for a match. If the student isn't in the list, you an use the NotInList event of the combobox to add the student to the list or you can do it using a popup form, among other methods.

    You'll need to find a way to keep names unique, so you may need an additional field with a number in it (not the autonumber, though) that you increment wheneve you run across still another John Jones. You would create a unique multi-field key on the first and last names and the incremental number, which could default to zero for new names and increment thereafter as needed.

    Does that help?
    Charlotte

  7. #7
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields in a table (Access 97)

    Bob,

    I would not put a duplicate check on the name, but instead on the phone number.

    If that is not workable, then you'll need to add more info to the student table to prevent duplicates or you may find that you cannot prevent them and will need the human touch to keep them from happening (ie. display a list of sudents when a name is keyed in and allow the user to pick one). It really all boils down to how do you really identify a student?

    Jim.

Posting Permissions

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