How to select values within defined ranges and match them against others?

Question

I have an Access database, built from a survey of university students. Among other things, there are fields for how many hours a week the students dedicates to reading class material, and, if they have a job, how many hours a week they work.

What I need to do is create a table. Each column in the table should be a range of hours of study. For example, column 1 would be "up to 5 hours", column 2 should be "between 5 and 10 hours", and so on. The rows should be the same, but with working hours. The idea is that, for example, one can ask "Of the students who study very little, how many of them do so because they don't have time because they work?", and then you check the "Up to 5 hours of study a week" column, and you see how many students don't work, how many work a bit, and how many work a lot.

I realize this may not be very clear, so maybe a rough representation of the table would help (the numbers are made up):

             |   Hours of study         |
             | < 5 | 5-10 | 10-15 | 15+ |
-------------+-----+------+-------+-----+
Hours  <10   |  35 |  15  |   10  |  5  |
 of   10-40  |  30 |  18  |   7   |  2  |
work   40+   |  40 |  10  |   3   |  1  |
-------------+-----+------+-------+-----+

Here we can see that very few manage to study and work a lot, while many have plenty of time but don't study much.

What I need is a way to build a table like this, using Access or maybe Excel if it's better suited for this. I know some SQL, so it's okay to use it for this.

Answer 1

There are a couple of ways to do this. Easiest way is to convert your data to those labels and make a pivot table out of it.

So load your data into Excel into a spreadsheet with studentName, hoursStudy, hoursWork

Then I punched out a "quick" VBA macro. I'm mostly a C3 guy but I was surprised on how hard it was to find a decent Excel VBA tutorial. All of them were for non-programmers and none were for programmers who just didn't know how to navigate Excel. So I apologize if this doesn't work the first time. . .

Fire up the VBA editor, paste and run it. . .

' Takes your spreadsheet of hours in sheet 1 and turns them into your measured values
' Then simply take the second worksheet and turn it into a pivottable

Sub StudyAndWork()
    Dim i as Integer
    i = 0
    Do
        Select Case Worksheets(1).Range("B" & i).Value
            Case Is < 5
                Worksheets(2).Range("B" & i).Value = "<5"
            Case 5 To 10
                Worksheets(2).Range("B" & i).Value = "5 to 10"
            Case 10 To 15
                Worksheets(2).Range("B" & i).Value = "10 to 15"
            Case Else
                Worksheets(2).Range("B" & i).Value = ">15"
        End Select
    i = i + 1
    Loop Until IsNull(Worksheets(1).Range("B" & i).Value)

    i = 0
    Do
        Select Case Worksheets(1).Range("C" & i).Value
            Case Is < 10
                Worksheets(2).Range("C" & i).Value = "<10"
            Case 10 To 40
                Worksheets(2).Range("C" & i).Value = "10 - 40"
            Case Else
                Worksheets(2).Range("C" & i).Value = "40+"
        End Select
    i = i + 1
    Loop Until IsNull(Worksheets(1).Range("C" & i).Value)
End Sub

You should be able to pivot table this. Just use Column B for Columns. Column C for Rows Go to the second spreadsheet and insert table. It should select all the data and turn it into a table. Add appropriate labels "Hours of Study" and "Hours of Work." Next, click Pivot Table.

• Drag "Hours of Study" to the Column Labels box.
• Drag "Hours of Work" to the Row Labels.
• Drag "Hours of Study" to SUM(Sigma) values. Make sure it says COUNT, and not SUM.
• Do the same with "Hours of Work"

Hope that works.