Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update query (2000)

    I was using the update query that somebody took me through step by step. Alas, it doesn't seem to be working any more. What I am doing is importing the Excel spreadsheet as different people send in their lists and save it as Ed (see attachment), overwriting the previous table. Is this the problem?

    Can anybody help me?

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

    Re: Update query (2000)

    What do you mean, it isn't working any more? What happens, do you get an error message, is the data not updated, is it updated incorrectly, or what? And the SQL view of the query is more useful than a partial graphic of the query grid in helping you sort out the difficulty.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2000)

    The new information that has been added to the table "Ed" does not get updated in tblAlumni. Nothing happens. Ed stays like Ed and tblAlumni stays the same, too.

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

    Re: Update query (2000)

    What happens when you double click the query in the datase window, or when you open it in design view, then select Query | Run? Do you see any message boxes, and if so, what do they say?

    As Charlotte remarked, it might be useful to post the SQL of your query.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update query (2000)

    We can't see the full details of your query, but what we can see indicates that you are asking the query to update tblalumni to the vale in tblalumni, rather than the value in table ED.

    I think you need the following. You don't need to include the two names in the query as they are the matching fields, and will be the same in both tables.
    Regards
    John



  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2000)

    This is what happens (see Word Document). I have expanded the update to field in one of the fields to show you what I have put. When I run the query, nothing changes in either table. I want tblAlumni to reflect the changes in Ed. I hope this helps.

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

    Re: Update query (2000)

    As near as I can tell (I really urge you to post the SQL, the graphics aren't terribly helpful), you want to update tblAlumni, but you are telling it to update the tblAlumni field if it is null. If the field in tblAlumni is not null, then nothing happens. Without the SQL, we can't be much further help to you because we're simply guessing at how your query is structured and how to fix it.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2000)

    It's confession time, Charlotte. I would love to give you the SQL, but I'm not too sure what it is, where to find it and how to recognize it when I see it. Can you help a poor soul like me?

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

    Re: Update query (2000)

    Open the query in design view.
    Select View | SQL.
    The text you see there is the "code" behind the query. You can copy it to the clipboard and paste it into a reply.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2000)

    Got it. Thanks. Here is my SQL code.

    UPDATE ed INNER JOIN TblAlumni ON (ed.Surname = TblAlumni.Surname) AND (ed.FirstName = TblAlumni.FirstName) SET TblAlumni.FirstName = Nz([tblAlumni].[FirstName],[Ed].[FirstName]), TblAlumni.Surname = Nz([tblAlumni].[surname],[Ed].[surname]), TblAlumni.Street1 = Nz([tblAlumni].[street1],[Ed].[street1]), TblAlumni.Street2 = Nz([tblAlumni].[street2],[Ed].[street2]), TblAlumni.City = Nz([tblAlumni].[city],[Ed].[city]), TblAlumni.Prov = Nz([tblAlumni].[prov],[Ed].[prov]), TblAlumni.Code = Nz([tblAlumni].[code],[Ed].[code]), TblAlumni.HomePhone = Nz([tblAlumni].[homephone],[Ed].[homephone]), TblAlumni.Email = Nz([tblAlumni].[email],[Ed].[email]);

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

    Re: Update query (2000)

    Your query updates only blank (null) fields; all non-blank fields are left as they are. Chances are that you have fields that LOOK blank but in fact contain an empty string (or perhaps one or more spaces); this can happen with tables imported from Excel. Access does not consider an empty string or a space to be a blank (null), so once a field in tblAlumni contains an empty string or space, it will never be updated.

    Try the following: make a backup copy of the database first. Replace Nz([tblAlumni].[FirstName],[Ed].[FirstName]) with

    <code>IIf(Trim([tblAlumni].[FirstName] & "")="",[Ed].[FirstName],[tblAlumni].[FirstName])</code>

    and similar for the other fields.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2000)

    Worked like a charm. Thanks to all who were patient with me.

Posting Permissions

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