Calculated fields in a table (Access 97)

Topic

New Reply

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

Bob

Reply | Quote

Replies

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]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 (although not quite an idiot ) is struggling to come to grips with Access and the modern, event/object oriented, GUI based stuff.

Any assistance gratefully appreciated and recognised
Bob

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote