Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Access updating more records than exist

    Hi,
    I am running several update queries on my access databse but a message box keeps appearing teling em the access is about to update more records than actually exist! So, I have 62770 records but access keeps telling me it is updating 96309 records. I've checked the output (well some of them) and it appears to have worked correctly but I am still wondering where the extra records have come from!

    One of the queries I am using is (and apologies if the syntax is messy, I am very new to using access):
    UPDATE E_1039_M_50 INNER JOIN precip_1039_50_M ON E_1039_M_50.UKCIP=precip_1039_50_M.grid_no SET E_1039_M_50.d1 = E_1039_M_50.day1+((E_1039_M_50.day1*precip_1039_50 _M.jan)/100);

    Thanks in advance for any help/advice
    Kate

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would expect that
    Code:
    select * from E_1039_M_50 INNER JOIN precip_1039_50_M ON E_1039_M_50.UKCIP=precip_1039_50_M.grid_no
    would return 96309 rows, and is the cause of the 'problem' you report
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    Thanks for the reply, but why would you expect that to return 96309 records, apologies for asking such a basic question but as I said before, my access knowledge is limited.

    Thanks,
    Kate

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't expect to return any rows as I don't know your data struture and I don't know your data.

    doing a join will bring together 'stuff' from different tables.
    say you have two tables persons and addreses
    if you do a join on those tables (presumable addresses.PersonID = Persons.ID) then you'd get a row for every addresses with the persons data duplicated).
    say we had a home & work address for me
    a home, work and parents address for you
    that would be two rows in the persons table, 5 rows in the address table
    eg
    me homeaddress
    me workaddress
    you homeaddress
    you parentsaddress
    you workaddress

    trying to run an update on a joined query will try to update every row ie 5 rows as their are 5 rows which intersect, even though the data we want to update may be say in the persons table as 5 rows meet the criteria.

    thats why I suggested you do a select rather than the update statement and see what is returned.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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