Complex query

[PB90]

I have a table it looks something like

recNum user Day Hour

1. I need to find out the Min Hour & Max hour on a given day for a given user.

2. If there is a break between hours, I need a new record that has the min hour & max hour for that period
i.e. if user1 has 8/22 hours 2 through 4, and 8/22 6 through 9 I want: 2 records.
user1 2, 4
user1 6, 9

Is there a way to do this with a query?

 

[GingerR]

1) What have you tried so far? What's not working?

2) You'd have to write a little vba code. How is "break in hours" defined? I see in your example that "break in hours" could also = between 9 and infinity and there is a missing hour of 1, so is that also a "break in hours"? Is there some sort of limit?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PB90]

A break in hours is any missing hour(s) in the same day. so if: user1 6-9, user1 11-12 that would be 2 records
user1, day1, 6, 9
user1, day1, 11,12
Since this is based on hours in the day, could be a max of 24 hours. No limit on how many hours can be "missing".
I'm not really sure how to do it. I figured I would have to user the Min & Max functions, not sure if I can do those both on the same record at the same time. Not even really sure how to use them in a query.

Any suggestion on what the vba code might look like?

 

[GingerR]

1) Min/Max--you're not sure if you can do both in the same query. Have you tried it? You can, but it won't work for what you want here though.

2) Yes I can help with the VBA, I just needed to know how you were defining "missing" hours. Please give sample data. Thanks.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[GingerR]

Ok--this assumes your existing table name is "TableName", and assumes the following fields based on your previous posts. You will have to tweak as necessary.

Current Table Name: TableName
Field: User (Number)
Field: Day (Date)
Field: Hour (Number, example 2,3,4 etc)
Field: recNum (Autonumber)

Make a second table which will hold the result:
Table Name: MissingHours
Field: User (Number)
Field: Day (Date)
Field: MissingHours (Memo)

make a new code module, and paste this into it:

    Function GetMissingHours()
    
    'Delete existing records
    CurrentDb.Execute "Delete * from MissingHours"
    
    
    Dim rs, rsUserRecords, rsMissingHours As DAO.Recordset
    Dim intMinHour, intMaxHour, j As Long
    Dim strMissingHours As String
    
    'Open existing table, get first User
    Set rs = CurrentDb.OpenRecordset("Select Distinct User, Day from TableName")
    rs.MoveFirst
    
    While Not rs.EOF
    
        'Open existing table for only the records for the current user and day
        Set rsUserRecords = CurrentDb.OpenRecordset("Select * from TableName where Day = #" & rs!Day & "# and User = " & rs!User & " Order By Hour")
        rsUserRecords.MoveFirst
        intMinHour = rsUserRecords!Hour
        rsUserRecords.MoveLast
        intMaxHour = rsUserRecords!Hour
            
            'Loop thru user/date records; any missing hours, put into string strMissingHours
            rsUserRecords.MoveFirst
            
            While Not rsUserRecords.EOF
                For j = intMinHour To intMaxHour
                    If rsUserRecords!Hour <> j Then
                        If strMissingHours = "" Then
                            strMissingHours = j
                        Else
                            strMissingHours = strMissingHours & ", " & j
                        End If
                    Else
                        rsUserRecords.MoveNext
                    End If
                Next j
            Wend
            
            'Write result to table MissingHours
            Set rsMissingHours = CurrentDb.OpenRecordset("Select * from MissingHours")
            
            rsMissingHours.AddNew
            rsMissingHours!User = rs!User
            rsMissingHours!Day = rs!Day
            rsMissingHours!MissingHours = strMissingHours
            rsMissingHours.Update
            Set rsMissingHours = Nothing
            strMissingHours = ""
       Set rsUserRecords = Nothing
       rs.MoveNext
    Wend
    Set rs = Nothing
End Function

Run the code--it will put the info into table MissingHours for you.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html