Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Thanked 1 Time in 1 Post

    Date cleaning (2000sr1a)

    Data cleaning problem. One of my fields is "Date of Visit". Sometimes these are wrong or missing. We usually visit one village on one day, so it's relatively easy to correct, and as we only visit 50 villages we can do this by hand, but it would be nicer to get an update query to do it.

    I've made a cross tab of date against hamlet, but I can't find a way to return the value of the column head (date) for the maximum value. Doing it another way, I can't seem to use the MODE function in a query.

    So to be completely explicit about my aim, if there are 10 observations for a village, 7 on the 10th August, 2 on the 8th August and one blank, I want to design an update query that will determine the most frequent date for that village, and change all the date fields for that village to 10th August, and I want it to carry out this process for all 50 villages.

    Hope someone can help.


  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts

    Re: Date cleaning (2000sr1a)

    Is there a Mode function in Access ? When I look up Help, it mentions the Mode worksheet function, which means it is referring to the Excel mode function.

    One approach would be to create a series of queries : one that counts the number of visits per village, one that finds that max count per village, one that finds the date associated with this max, and then one that updates dates to that date.

    What do you want to happen when some dates get the same score? A Mode function would average them - you probably don't want that.

    Another approach would be write a function to create a recordset of the dates for each village, loop through the records counting the number of records for each date, find the max, then loop though again updating them. The code would allow you the flexibility to consider what to do about date withing matching counts.
    You would then need to loop though each village, and call the function for each one.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Altnau, Thurgau, Switzerland
    Thanked 0 Times in 0 Posts

    Re: Date cleaning (2000sr1a)

    Have a query to group on the Hamlet and date of visit and another column has a name (eg iCount:1) that is a count field. Sort decending on the count field to find the date with the most visits for each Hamlet. That should get you started.

Posting Permissions

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