Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Updating selected fields (XP and 2000)

    I know I've read about this sort of thing here before, but...where to find it!? Here's the problem: I have a table of students, and we've recently added 30 new ones. The names and payroll IDs for these people are in the table, but until today I did not have certain other key pieces of information about these new students (like Social Security numbers). Today I got an Excel spreadsheet that has that information, and I want to update my table. Of course, one way would be to just spend half an hour keying in the numbers, but why do that...?

    An append query doesn't seem the way to go. I think I need an update query, but Access Help just talks about updating a price field by X percent. I want to fill in missing information. Here's what I've done so far:

    1. <LI>I've imported the spreadsheet into the database.
      <LI>I've formatted the new imported table to exactly match the existing tblStudents (field names and properties are the same).
      <LI>I've made the PID (payroll ID number) field the Primary Key in both tables.
      <LI>In query design view, I've added both the existing tblStudents and the table that contains the missing information, and I've linked the two tables in a one-to-one relationship
    So now I have two tables: One has 597 student records, including the 30 newly-hired employee/students, but the new records have some empty fields; the other has just the 30 new records, but all the fields are complete. You get the picture. Where do I go from here?

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating selected fields (XP and 2000)

    <P ID="edit" class=small>(Edited by D Willett on 29-May-03 16:31. )</P>I think what you need is an Append Query, to append the data into your existing table.
    If you create a new query, add your tables and then select append query from the tools menu, you will probably be ok from there.

    Check the help files within Access for "Query" you will find some good info on all the various types.

    Before you do anything, be sure to back up your database, and work on a copy first.
    If all goes wrong, you can start over.
    Hope this helps a little.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating selected fields (XP and 2000)

    You do need the Update Query.
    Once you select the "Update query" from the "Query" menubar, Access will add an extra row called "Update To" in the query grid.
    In the grid add the PID field plus the rest of the fields that you want to update from tblStudents.
    In the "Update To" box under each field that you wish to update add the table/field name that you are getting the data from Ie. [newTable]![SSN].
    When you click the Run button this will then update your records.

    Hope that makes sense!

    It would be a good idea to back up your data first just in case!!

    Peter

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

    Re: Updating selected fields (XP and 2000)

    Thanks for that quick response. What you're saying makes sense, but here's what happened. Per instructions in Access Help, I put into the query window the table from which I want to take the new information (tblMay2003NewHires). Then I clicked on Query > Append. In the Append Query dialog box, I chose the table to which I want to append the information (tblStudentsUpdate--a copy of tblStudents). I added the fields that I want to update: PID, EMPLID [employee ID] and SSN. The names in both tables are identical, so those same names appeared in the "Append to: " row. So far, so good. Then I hit the Run icon.

    Now I get an error message: [my database] "...set 0 fields to Null due to a type conversion failure, and it didn't add 30 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations." Seems the problem is key violations, whatever that means. I checked both tables, and I noticed that my "New Hires" table had its PID (primary key) field set to Number, just like the other table, but its Field Size property was set to Double. The existing table's PID field is Number/Long Integer, so I changed the setting of the New Hires table to match the other one. Ran the query again--same result.

    I previewed the query without running it, and I notice that it displays all the new records. The primary key field (PID), of course, is complete for all records. However, the two fields that I want to fill-in (EMPLID and SSN) display only the data that already exists in the target table!! The blank fields are still blank!

    I know I'm missing something really basic, but I can't see it.

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

    Re: Updating selected fields (XP and 2000)

    Hello, Peter, and thanks for the help. Your information also makes sense, but maybe I'm jinxed or something. I chose Update Query and the design grid behaved just as you described. I followed the steps as you described, but when I hit Run, I get the dreaded parameter box asking for the values of the three fields I've placed in the grid: PID (primary key), EmplID and SSN.

    Now what?

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

    Re: Updating selected fields (XP and 2000)

    Lucas,

    If (A) tblMay2003NewHires (the table you imported from Excel and then modified), contains ALL needed info about the new students, and ([img]/forums/images/smilies/cool.gif[/img] you don't have records in related tables referring to the new students yet, you can go with Dave Willett's idea of using an append query. You will have to delete the incomplete records from tblStudents first (to avoid the duplicate key problem) and then run the append query.

    If (A) or ([img]/forums/images/smilies/cool.gif[/img] is not satisfied, or if you are not sure about them, an update query as suggested by Peter is the ticket. You should start with the query you described in the first post in this thread, that is based on tblStudents and on tblMay2003NewHires, linked on the PID field. This query should return 30 records (namely, only the new students) - if it returns more records, or less, review its design. Next, change it to an update query. You don't want to update the PID field, because that is already in tblStudents. Only enter something in the Update To: row for fields in tblStudents that are empty at the moment, and have to be populated from tblMay2003NewHires. So, under EmplID (in tblStudents), enter [tblMay2003NewHires].[EmplID], and under SSN (in tblStudents), enter [tblMay2003NewHires].[SSN], and so on if necessary, where tblMay2003NewHires must the exact name of the imported table, and the field names must be the exact names of the fields in this table. Any typo will cause the parameter prompt.

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

    Re: Updating selected fields (XP and 2000)

    The update query is what I want to use. I'd thought about just deleting all the fragmented records and going with the append query. In this case, that approach might be the ticket. However, I have other similar situations where I want to use the update query approach, and I want very much to learn how to do it.

    Just to make sure I'm following your advice: (1) I do need to place BOTH tables into the query design window, and I need to establish a link between them on the PID field (primary key); (2) I should drag into the grid only three fields from tblStudents: PID, EmplID and SSN; and (3) I should then mark them for update as you described. I can't swear I didn't have an typos, but I'll check very carefully. I've found that even an extra space at the end of a word can throw things off-track.

    I'm not at work now and won't be back at my desk till Tuesday morning. With the information that you, Dave and Peter have given here, I think I can lick this problem. Thank you, Hans, and thank you, Dave and Peter!

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

    Re: Updating selected fields (XP and 2000)

    Lucas,

    Your description is correct. Let us know if you still have problems with this next week.

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

    Re: Updating selected fields (XP and 2000)

    Hello again, Hans: I believe I have followed the steps we've discussed earlier, and I'm not getting the results I expected. To wit: I built the query as above, then ran the preview. There I see all 26 records (it's 26--not 30, as I said earlier) and just two fields: EMPLID and SSN. That's the good news. The bad news is that, of those 26 records, the only ones that contain any data are the records where I already have data in the table I'm trying to update! That is, if the record for Jeff Taylor had both an EMPLID and an SSN before I started this query, then those numbers appear in the query preview. If Jeff Taylor's record lacked that information, then that record is blank in both fields! I'm sure I'm overlooking some minor detail, but....

    Any ideas?

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

    Re: Updating selected fields (XP and 2000)

    1. If you create an update query and switch to preview mode, you are looking at the existing data, not at the data as they will be after updating.

    2. I thought that NONE of the 26 (or 30 or whatever) "new" records had existing data in the EmplID and SSN fields. You wrote earlier on that you had only added the PID for the "new" records. If you would execute the query now, the existing values will be overwritten by the values from the imported table. If they are the same, that is no problem, but otherwise...

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

    Re: Updating selected fields (XP and 2000)

    Hello, Hans: I apologize for any fog I've left on this trail. Just for the record, here are some details:

    <UL><LI>The table I want to update is tblStudents, but for this exercise I'm working with a copy that I've named tblStudentsUpdate
    <LI>tblStudents has 597 records, and, of course, all have a PID (payroll ID)--that's the primary key field. Among those 597 records are 26 records for newly-hired employees.
    <LI>Ten of the 26 new-hire records are complete--that is, they have PID, EMPLID and SSN. The other 16 new-hire records have PID, but do not have EMPLID and SSN. Those 16 records are the focus of my efforts.
    <LI>tblMay2003NewHires has exactly 26 records, which exactly match the 26 new-hire records in the base table. These 26 records have all the data I need: PID, EMPLID, and SSN.[/list]I think I have constructed the query as you recommended--the screen shot (attachment) shows the query in Design view. When I click Run, I get the usual "You are about to update 26 records..." message, and I click Yes...but nothing happens. The empty fields are still empty. Obviously, I'm overlooking something. I hope you can help me solve the puzzle. Thanks for your patience!
    Attached Files Attached Files

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

    Re: Updating selected fields (XP and 2000)

    Lucas,

    Frankly, I don't understand, your query design looks OK. You can limit the query to the incomplete records by adding Is Null in the Criteria: row under EmplID (or SSN). If you then select Query | Run or click the Run button, you should get a message that you are about to update 16 records. After clicking Yes, either the records should be updated, or you should get a message that so many records couldn't be updated for one reason or other. If neither of these happen, and if - as you write - tblMay2003NewHires contains valid values in EMPLID and SSN, I have no idea why...

    What happens if you:
    1. Create a new blank database.
    2. Import just tblStudentsUpdate, tblMay2003NewHires and qryTryToUpdateStudents into it.
    3. Run the query.

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

    Re: Updating selected fields (XP and 2000)

    Hi, Hans: Well, I added Is Null, and that in fact did focus the query on the 16 records that were missing the data in question. When I switched to Datasheet view, I saw 16 records with two empty fields: EMPLID and SSN. (PID, even though it's in the design grid, does not display.) I ran the query again, and...same results. Then I tried your suggestion of importing the two tables and the query into a fresh database. Same results--nuthin'.

    In the time I've spent fiddling around with this update query, I could've manually entered all 597 SSNs! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> Well, consider this development: I thought I'd try the new database idea again, but this time I imported only the table structure, not the data. Then I put in some dummy data--just 10 records in each table. In tblStudents, I left eight of the EMPLID and SSN records blank. In tblMay2003NewHires, I built complete records. In other words, I replicated the situation in my original database. With the query, I had to clear the grid and re-build it from scratch because in this dummy database I used tblStudents instead of tblStudentsUpdate. At that point, I was going to attach the dummy database so you and others could have a look at it. Then it occurred to me to run the update query and...guess what...the <img src=/w3timages/censored.gif alt=censored border=0> thing worked!!

    Any idea what's going on? (Gee, this is fun! <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> )

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

    Re: Updating selected fields (XP and 2000)

    Lucas,

    We'll probably never discover from a distance what is causing this. It might be some form of corruption in the original data, or something none of us has thought of.

    Since the query works on tables with exactly the same structure with dummy data, and it doesn't work on the real data, I don't see a way of getting to the bottom of this, since the real data contain sensitive information (SSN). <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Note: it's normal that you don't see the PID field when you switch the update query to datasheet view. This will only display the current values of those fields that are about to be updated.

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

    Re: Updating selected fields (XP and 2000)

    Hans,

    I remember once I had an awful time with a table I'd created with data that I'd imported via Word. I can't speak the language, but I remember you gave me the solution. It had something to do with Microsoft's "Smart Quotes." The data I'm working with here did not come out of Word, but I did get it from an Excel spreadsheet. Before I saw it in Excel, it existed in a PeopleSoft database. Is it possible that Excel or PeopleSoft did something to the formatting that affects this situation?

    Whatever the cause, I appreciate your help! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Page 1 of 2 12 LastLast

Posting Permissions

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