Results 1 to 9 of 9
  1. #1
    Bdowns is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    28

    Group by 1 minute intervals, and count number of unique entries

    My records are observations of organisms and the time when they were seen (hh:nn:ss). I would like to group my records into whole minute intervals and summarize the number of unique (different) organisms per minute. Ultimately, I am trying to produce a graph that shows the number of minutes it takes until no more unique organism are observed. The purpose of this graph is determine the amount of sampling time required before all of the biodiversity present in the ecosystem has been observed. Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    First, what is the datatype of the field where you are recording the time? Is it a date/time datatype or a text datatype?

    If you are using a date/time datatype (long time), you should be aware that Access does not actually store the time in the format (hh:nn:ss), but rather as a number that represents the fraction of a day. So 12:00:00 noon is stored as 0.5, 2:23:33 AM would be stored as 0.0996875 and so on. You will have to convert your time to something you can work with. I would probably convert the time from the hh:nn:ss format to the fraction of the day. Then I would convert that to minutes, so to find the number of minutes in the fraction of the day of interest, you would multiply the fraction of a day by 1440 (24 hours * 60 minutes/hour). In terms of an expression, it would look like this: cdbl(timefield)*1440. The cdbl() function just converts the format to the actual fraction of a day that Access stores. So for example, the 2:23:33AM would convert to 143.55 minutes (since midnight). You can then apply the INT() function to return the whole minute 143. So the entire expression would look like this: INT(cdbl(timefield)*1440), you would then group on this expression.

    BTW, I did not include the date. You will have to be carefull if your series of times include more than 1 date/day.

  4. #4
    Bdowns is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    28

    Group by time, summarize unique entries: Species Accumulation Curve

    Quote Originally Posted by June7 View Post
    Thanks, but im still not sure how to do this because my records don't occur in even intervals. I can't say 60 records is a minute because during some minutes no species were observed (0 records), and during others there could be 20+ records. I need to group them by time interval. I also need to count not the total number of organisms in a one minute bin, but the total # of unique organisms. Each species has a unique ID, so if minute one had 4 records (sp.1, sp.1,sp.2,sp.2), then the total number of unique organisms in that minute is just two. Ultimately, I would be taking the average number of unique species per 1 min bin. Do the same for 2 min bins, 3min bins,....... The desired end product is a Species Accumulation Curve. They are used in ecology to determine appropriate sub-sampling periods that capture the biodiversity of a region. Thanks

  5. #5
    Bdowns is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    28
    Good stuff, thanks! I think this will work for grouping. Would you by chance know how to summarize each group by the number of unique observations. For example, if there are 20 records in the [Organism] field during the same minute, but there are only 2 different organisms (same organisms over and over), the desired summary result would be -2-. I would average the # of unique species per 1 minute interval. Next, I would group into two minute intervals...three minute intervals......and calculate average unique orgs for each interval. The end goal is to produce a species accumulation curve and determine an appropriate sampling period to capture the biodiversity. Thanks

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can you provide sample source data? A spreadsheet or the Access file, attach to post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    ... if there are 20 records in the [Organism] field

    I'm not sure I follow what you are saying but if you are saying that each record has only 1 organism identified along with a time interval, you would just group by the time interval and then the organism.

    Assuming that your data looks like this:

    organism|timeInterval
    organism1|21
    organism1|21
    organism2|21
    organism1|22
    organism2|21

    ...your query would look like this

    SELECT organism, timeinterval, count(organism) as OrganismCount
    FROM tablename
    GROUP BY organism, timeinterval


    if there are 20 records in the [Organism] field
    This might be more difficult since you will have to define each interval since each encompasses a different range i.e 21 to 23 minutes then 24-26 minutes. If you have predefined starting points for each interval then you can probably do it by storing the starting points in a related table and comparing each time in minutes to the starting point otherwise you may have to go the Visual Basic for Application (VBA) route.

  8. #8
    Bdowns is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    28

    The Database

    NCC_DataEntry_120314b.zip
    Quote Originally Posted by June7 View Post
    Can you provide sample source data? A spreadsheet or the Access file, attach to post.
    Here is the database. I was able to group by minute following jzwp11's advice. I was then able to summarize by count. This is usefull, but I then want to be able to summarize by unique count. The database contains a master query that has organisms stored by the unique ID from the respective tables, a input review query that shows actual names, a query grouping mobile inverts by minute, and the query that counts # of mob inv's per minute. It would be great if I could next group by two minute intervals. All critique, suggestions, advice, insults, complaints...... will be appreciated. Thanks

  9. #9
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    In looking at your database, I see an issue that will probably cause some problems. You have separate tables for different types of organisms. All organisms should be in 1 table. If you need to distinguish the various groups of organisms in some way, you should add a field to handle that. In fact, a related table may even be better.

    tblOrganismTypes
    -pkOrganismTypeID primary key, autonumber
    -txtOrganismType

    tblOrganisms
    -pkOrganismID primary key, autonumber
    -txtOrganismCode
    -txtOrganismCommonName
    -txtOrganismScientificName
    -fkOrganismTypeID foreign key to tblOrganismTypes

    Since an organism can have 2 names: common and scientific, technically speaking that is a one-to-many relationship and the names should be in a related table, but I'm not sure that that might be carrying normalization a little too far in this case.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Unique queries - Unique Count
    By bigfish in forum Queries
    Replies: 7
    Last Post: 03-14-2012, 01:28 PM
  2. Replies: 1
    Last Post: 02-22-2012, 04:59 PM
  3. List box doesn't allow Unique data entries
    By Delta223 in forum Forms
    Replies: 2
    Last Post: 01-24-2011, 07:24 PM
  4. Query to group and count field entries
    By newtoAccess in forum Queries
    Replies: 4
    Last Post: 11-29-2010, 09:19 AM
  5. Calculation on unique entries
    By cjbuechler in forum Programming
    Replies: 3
    Last Post: 06-24-2010, 09:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums