Results 1 to 5 of 5
  1. #1
    DesCall is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    3

    Summary table query Access 2007


    Dear Forum,

    I have three columns of data (see attached file):
    'Site' - a unique code for each of three different survey sites (1, 2, 3).
    'SpCnt' - a running cumulative total of the number of different species of plants seen during a field survey of each site (1, 2, 3, 4, 5, 6, etc).
    'Time' - the time (in minutes) at which each additional species was seen since the start of the survey (1, 3, 6, 7, 10, etc).

    I wish to create a new summary table that includes three columns of data:
    'Site' - the site code.
    'TimePeriod30' - a code (1, 2, 3, etc) that indicates each 30 minute survey period (i.e. values of 1 to 29 from 'Time' = 1; values of 30 to 59 = 2; etc).
    'SpTotal' - the number of species found during each 30 minute survey period (i.e. a count of 'SpCnt' within each category of 'TimePeriod30').

    Any idea how I may do this? Many thanks for any help.

    Best wishes,
    Des

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not seeing any way to do this with a query alone. Here is one way to accomplish. Import the text file to an Access table. Add a field TimePeriod30. Run this VBA code to populate the new field (be sure to include 'Microsoft ActiveX Data Objects 2.8 Library' in the VBA references):
    Code:
    Public Sub Time30()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim siteID As String
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM SppSite;", cn, adOpenForwardOnly, adLockPessimistic
    siteID = rs!site
    While Not rs.EOF
        If siteID <> rs!site Then
            i = 0
            siteID = rs!site
        End If
        If rs!Time < i * 30 Then
            rs!TimePeriod30 = i
            rs.MoveNext
        Else
            i = i + 1
        End If
    Wend
    rs.Close
    End Sub
    Query the table to summarize data.
    SELECT Site, TimePeriod30, Val(nz(DMax("SpCnt","SppSite","Site=" & [Site] & " And " & " TimePeriod30=" & [timeperiod30])-DMax("SpCnt","SppSite","Site=" & [Site] & " And " & " TimePeriod30=" & [timeperiod30]-1),DMax("SpCnt","SppSite","Site=" & [Site] & " And " & " TimePeriod30=" & [timeperiod30]))) AS SpeciesTotal
    FROM SppSite GROUP BY Site, TimePeriod30;

    Since the SpCnt field is cumulative for each site, don't think can just count or sum the values. To get the additional species counted in each period must subtract the previous period. Unless I misunderstand your requirement and you do want to show cumulation through the periods. Adjust the query to suit.

  3. #3
    DesCall is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    3
    Many thanks for the reply, which looks very helpful. I'm afraid I am new to running VBA code. What do I need to do in order to run the code? Thanks for your help. Best wishes, Des

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I built the code as a Public Sub routine placed in a general code module. From VBA editor Insert module, paste this code into the module. Can run by positioning cursor anywhere in the procedure then clicking the Run button or from Run menu select Run Sub.

    Could also place the code within a button click event.

    Suggest you run through some VBA tutorials, maybe get a reference book. Access Help should have info, search it for 'VBA' or 'Get started with Access programming'.

  5. #5
    DesCall is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    3
    Many thanks for your further help. Much appreciated. Best wishes, Des

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

Similar Threads

  1. Replies: 0
    Last Post: 11-17-2010, 01:21 AM
  2. No Summary option in Query Wizard
    By LilMissAttack in forum Queries
    Replies: 1
    Last Post: 11-15-2010, 04:32 PM
  3. Summary Query - How to?
    By ritch in forum Access
    Replies: 16
    Last Post: 02-17-2010, 03:18 PM
  4. Replies: 7
    Last Post: 12-30-2009, 11:03 AM
  5. Product in a Summary Query
    By Fletch in forum Queries
    Replies: 0
    Last Post: 12-11-2008, 03:14 PM

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