Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Adding field to table (2K and XP)

    I now know how to append records to a table with an action (append) query. I understand the concept of an update query, whereby I can increase the price of items by, say, 2 percent. What I haven't figured out is how to use a query to add a field to a table or to add data to records where some fields were empty.

    Case in point (1): We have a table of courses that we offer in our learning labs. Each course has a unique six-digit identification number that never changes, but it also has a five-digit number that identifies the semester during which a student completes it. For Fall 2002, a particular course uses 20923; for Spring 2003, the same course goes by 19829. The table has an empty field for Summer 2003, and very soon we'll get a list of course ID numbers for the new term.

    Those numbers come out of a system to which we cannot link directly--we have to export the data from that other system and import it into our Access DB. Once we have the new list, we can assign someone to manually add those numbers to our table. Or...what? I think I've figured out one way to do it. First, I import the new semester's list of course ID numbers and create a table with just 2 fields: (1) The primary field [six-digits] and the (2) new Summer semester ID number. Then I relate the existing Courses table and the new table, joining them on the primary field. Next I build a query containing everything from the existing Courses table and just the Summer semester field from the new table. Then I run a make-table query and replace the Courses table with a new table of the same name, but which now has the Summer semester field populated. Is there another--perhaps better--way to do this?

    Second example: Say you have a list of 800 students, and each student has a unique ID number. We recently got a list of 35 new students, but the list did not yet have ID numbers. I needed to get the names into the system, so I appended the list to our existing table. Two weeks later, I got an updated list with ID numbers. I took the easy way out: I deleted the 35 records I'd entered earlier and appended the new list. That solved the problem, of course, but isn't there a better way?

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

    Re: Adding field to table (2K and XP)

    Hi Lucas,

    What you need is an update query, not an append query. With update queries, you can modify fields in existing records a table (this includes setting the value of empty fields). But you must have a way of connecting the new data with the existing data. In your second example, you first added 35 students without StudentID. If you want to set the StudentIDs later from the updated list, you must be able to link the records in the updated list with the corresponding records in the Students table. Ordinarily, you use a unique ID for that, but that's precisely what don't have yet. So you must find a field, or combination of fields, that uniquely identifies a student. Social Security Number, if you have that, would do fine. Otherwise, depending on the situation, last and first names might be sufficient, or last name, first name and birth date, etc.

    First create a standard (selection) query; add the students table and the list to update from. Join the tables on the identifying field(s). Check that the query returns the records to be updated. Then change it into an update query using the query menu. The display grid will show a new row: Change To (or something like that). Fill only the entries for fields you want to be updated. In the above example (your example #2), set the Change To entry for StudentID to tblUpdate.StudentID (where tblUpdate is the name of the updated list)

    Make a backup copy of the database before modifying the data, so that you can go back if you make a mistake. I also recommend playing around with update queries in a test database a bit before using them in production work, to get a feeling of how they work.

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

    Re: Adding field to table (2K and XP)

    There is a big difference between adding a field to a table and adding data to an existing field in a table. It appears to me that your question is really about the latter. Is that correct, or are you asking for a better way to add something like a semester field to an existing table? Rather than adding fields, why not just have a table with the necessary fields and just populate them with an update query instead of creating a whole new table? Is there a reason you aren't doing it this way? Make table queries cause serious bloat in the database, especially if you use a 2002 format and there doesn't seem to be any need for them in this case.

    As to your second question, if the names were already in your table, couldn't you have linked the list iwth IDS to the table with names but no IDs on the student name and then updated the empty IDs?
    Charlotte

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding field to table (2K and XP)

    I'll respond here to both Charlotte and Hans. My thanks to both of you for your advice.

    All along, I've thought that an update query sounded like the way to go, but whenever I go looking for documentation on update queries, I see how to modify all the records in a single field (e.g., increase/decrease the price of items in an inventory table). All I want to do--in the one example--is populate a field (student ID) for records where that information was missing.

    You both have clarified the matter rather nicely. I tried to simplify my explanations, and in so doing I failed to mention that I do have a unique identifier for the students table: the AutoNumber serves as the primary key, although we never use it for anything in the "real world."

    I was asking about both situations, Charlotte. I need to update an existing table by filling in missing information (student ID). In the other situation, I need to add a field (course ID numbers for a new semester). My table does have fields for Summer 2003, Fall 2003, Spring 2004 and so on. Those fields are empty for now, but I'll populate them once I have the data.

    Hans: I've learned the hard way not to monkey around with a working table or especially a working DB! Fact is, I was playing with a dummy DB this morning when I came upon the idea of using a make-table query. It looks as though an update query is the better way to approach both these situations. I'll experiment with my dummy DB till I'm comfortable with how update queries work.

    Charlotte: One more question about "database bloating." You say make-table queries are a primary cause of this affliction. That makes sense, but if one were to take this approach, can't you avoid the bloat by later deleting the old tables and the queries themselves, once the dust settles? Then, of course, there's compact-and-repair to clean up some of the debris, n'est pas?

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding field to table (2K and XP)

    Hans: I've responded to both you and Charlotte in one reply to Charlotte's post. Many thanks!

  6. #6
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding field to table (2K and XP)

    <hr>I need to add a field (course ID numbers for a new semester). My table does have fields for Summer 2003, Fall 2003, Spring 2004 and so on. Those fields are empty for now, but I'll populate them once I have the data.
    <hr>


    This looks to me as if you have not dealt with a many-to-many relationship properly unless I've misunderstood you. Student to Course is many-to-many. To represent this properly within your relational database you need to create a linking table (maybe called tblEnrolment) which stores the StudentID and the CourseID. The approach you have adopted (a new field within the Student table for each semester) will cause you problems later on. What if a Student does more than one Course during a semester? What happens when you've used up your 255 fields in the Student Table? (unlikely I know but possible) . How do you show a listing of Students on a Course?
    I'm not suggesting that you change your table design now (that would probably involve major surgery <img src=/S/doctor.gif border=0 alt=doctor width=25 height=33> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>) but for future reference you might want to take a look at my handout on normalisation attached to <post#=185086>post 185086</post#>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding field to table (2K and XP)

    Steve: Thanks for that contribution, but this DB hasn't gotten to that level yet. I probably muddied things pretty well in my original post. I'm dealing with two separate tables (Students and Courses) which do exist in the same DB. However, at present our system is not a complete system for tracking academic records. Our operation is officially part of a public community college, but our courses are strictly non-credit and the credits are strictly clock hours.... I could leave your head spinning if I tried to explain it all in detail.

    Suffice it to say that my question focuses on (1) adding a field (summer 2003) to the Courses table and (2) filling in missing information in an existing field (Employee ID) in the students table. It appears that I can use update queries to solve both problems.

    However, I appreciate the link to your other information. We're looking very seriously at using Access to build our own record-keeping system to replace an off-the-shelf application that we bought about six years ago. I haven't looked at your handout on normalisation yet, but I have no doubt your information will help us! Thanks!

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

    Re: Adding field to table (2K and XP)

    <hr>can't you avoid the bloat by later deleting the old tables and the queries themselves, once the dust settles?<hr>
    The answer is a qualified "yes". Assuming the compact and repair works, you should be able to reclaim most of the space, however, until you compact, that space is still allocated to those deleted objects. Creating a new table or query doesn't reuse any of that "deleted" space, and if you wait too long to compact, you could wind up with a monster that won't let you compact it because it has reached the 2Gb size limit.
    Charlotte

Posting Permissions

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