Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update table (Access 2000)

    Can i update a table depending on the value n the first row? I have to update the table tblClients. I use the folowing general fnction:

    Public Function DoSomething()
    Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "UPDATE tblClients SET afid = 9 "
    Set db = Nothing
    End Function

    However i want to ask is it possible to update according to the value in the first row? For example the client in the first row is client number 3 and this client has afid = 2. Can i update all the clients in the table to have afid = 2?

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

    Re: update table (Access 2000)

    A table in Access doesn't have an intrinsic sort order, so you'll have to define what the "first row" is. Is there a field on which the records ought to be ordered?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table (Access 2000)

    Thank you for your reply.Unfortunately there is no field on which the records ought to be ordered.I am trying now to use the count function.Actually i want to update on the basis of the greatest number of the values. For example let us assume that most of the customers in the tables have afid = 2. In that case i want to update all the customers to have an aifd = 2. If we have a table where the greatest number of the customers have afid = 9, then i want to update all the customers to have an afid = 9
    Could you help me along these line s ?

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

    Re: update table (Access 2000)

    And what if there two (or more) afids that both have the maximum number of customers? For example

    <table border=1><td>afid</td><td>number of customers</td><td align=right>1</td><td align=right>13</td><td align=right>2</td><td align=right>16</td><td align=right>3</td><td align=right>22</td><td align=right>4</td><td align=right>19</td><td align=right>5</td><td align=right>21</td><td align=right>6</td><td align=right>11</td><td align=right>7</td><td align=right>16</td><td align=right>8</td><td align=right>22</td><td align=right>9</td><td align=right>7</td></table>
    The highest count is 22, but this is for both afid = 3 and afid = 8.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table (Access 2000)

    Thank you so much for your kind attention.It is not posible.In each case, that is for each affiliate, only one afid number is predominant.However, due to some errors, several cases , which is a minority,have the wrong afid numbers.
    let us take the example with the afid = 2. In my case there are 90 customers. From these 90 customers 80 have afid = 2 and 10 customers have the wong afid = 9.
    I have another case with an office where the afid should be 8. There are 180 customers in total, and only 7 of them have afid = 1, and 2 have afid = 3. All the remaining customers have afid = 8
    Kind regards
    Peljo

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

    Re: update table (Access 2000)

    It has to be done in several steps:

    1) Create a query based on tblClients to return the number of clients for each afid:
    <code>
    SELECT afid, Count(*) AS Cnt
    FROM tblClients
    GROUP BY afid
    </code>
    Save this query as qryCount.

    2) Create a query based on qryCount to return the afid that has the highest number of clients:
    <code>
    SELECT afid
    FROM qryCount
    WHERE Cnt=(SELECT Max(Cnt) FROM qryCount)
    </code>
    Save this query as qryMax

    3) Create an update query based on tblClients that sets afid to the value returned by qryMax:
    <code>
    UPDATE tblClients SET afid = DLookUp("afid","qryMax")
    </code>
    Running this query will make all afids equal to the one that occurs most. I would test it on a copy of your database first, to check that it does what you want.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table (Access 2000)

    it is a perfect method,a stroke of genius indeed. Thank you so much. I could never do that myself and this is indeed a powerful tool.In this way i will clean up al the defects in the tables with one and the same code.

    Kind regards

    Peljo

Posting Permissions

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