Creating a 'calendar matrix' in Access

create gmail account
create a gmail account
zelda creating a champion pdf
create new gmail account for others
creating a champion book
breath of the wild--creating a champion
creating a champion hero's edition
creating a champion champions' edition

I’m trying to create either a report or form that displays data in essentially a "calendar" form.

I have a course query that is (simplified) as "Course name"; "course days"; "course times"---

Course; Days; Times
PSY 1; MW; 8A-9A
SOC 150; M; 8A-11A
ANTH 2; Tu; 8A-9A
ANTH 199; MW; 8A-9A

In Access, I’m trying to create a form based on the query that would give me a matrix of the following:

  • Columns: Times in hour increments
  • Rows: Days of week

So, for example, with the above data, it would appear like this: Edit: Yargh, I can't submit an image unfortunately. So, here is a link to a "course schedule" that is essentially what I'm trying to do: Schedule

I have no idea where to start with this. Any tips (or links)?


One idea I have is to create a form with a field for every possible cell in the matrix (so, for example, there would be one "Monday, 8-9A" field--and that field would be a filter on the query that ONLY displays results where "day" contains "M" and BeginTime or EndTime or between 8A and 9A). Unfortunately, I'm not sure how to do that.

You can do something close to what you seem to want as an Access form, but it's not easy. This screen capture displays your sample data in a Datasheet View form whose record source is an ADO disconnected recordset. It uses conditional formatting to set the text box background color when the text box value is not Null. Your picture suggested a different color for each Course, but I didn't want to deal with that when more than one Course can be scheduled in the same time block ... my way was simpler for me to cope with. :-)

The code to create and load the disconnected recordset is included below as GetRecordset(). The form's open event sets its recordset to GetRecordset().

Private Sub Form_Open(Cancel As Integer)
    Set Me.Recordset = GetRecordset
End Sub

Note I stored your sample data differently. Here is my Class_sessions table:

Course   day_of_week  start_time  end_time
------   -----------  ----------  -----------
PSY 1              2  8:00:00 AM   9:00:00 AM
PSY 1              4  8:00:00 AM   9:00:00 AM
SOC 150            2  8:00:00 AM  11:00:00 AM
ANTH 2             3  8:00:00 AM   9:00:00 AM
ANTH 199           2  8:00:00 AM   9:00:00 AM
ANTH 199           4  8:00:00 AM   9:00:00 AM

This is the function to create the disconnected recordset which is the critical piece for this approach. I developed this using early binding which requires a reference for "Microsoft ActiveX Data Objects [version] Library"; I used version 2.8. For production use, I would convert the code to use late binding and discard the reference. I left it as early binding so that you may use Intellisense to help you understand how it works.

Public Function GetRecordset() As Object
    Dim rsAdo As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim db As DAO.Database
    Dim dteTime As Date
    Dim i As Long
    Dim qdf As DAO.QueryDef
    Dim rsDao As DAO.Recordset
    Dim strSql As String

Set rsAdo = New ADODB.Recordset
With rsAdo
    .Fields.Append "start_time", adDate, , adFldKeyColumn
    For i = 2 To 6
        .Fields.Append WeekdayName(i), adLongVarChar, -1, adFldMayBeNull
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
End With

strSql = "PARAMETERS block_start DateTime;" & vbCrLf & _
    "SELECT day_of_week, Course, start_time, end_time" & vbCrLf & _
    "FROM Class_sessions" & vbCrLf & _
    "WHERE [block_start] BETWEEN start_time AND end_time" & vbCrLf & _
    "ORDER BY day_of_week, Course;"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSql)

dteTime = #7:00:00 AM#
Do While dteTime < #6:00:00 PM#
    'Debug.Print "Block start: " & dteTime
    rsAdo!start_time = dteTime

    qdf.Parameters("block_start") = dteTime
    Set rsDao = qdf.OpenRecordset(dbOpenSnapshot)
    Do While Not rsDao.EOF
        'Debug.Print WeekdayName(rsDao!day_of_week), rsDao!Course
        rsAdo.Fields(WeekdayName(rsDao!day_of_week)) = _
            rsAdo.Fields(WeekdayName(rsDao!day_of_week)) & _
            rsDao!Course & vbCrLf

    dteTime = DateAdd("h", 1, dteTime)

Set rsDao = Nothing
Set qdf = Nothing
Set GetRecordset = rsAdo
End Function

Create a new channel - YouTube Help, Even if you have a Google Account, you need to create a YouTube channel to upload videos, comment, or make playlists. You can use a computer or the YouTube  Use a phone number instead Get a new email address

Actually, if you look at the following video of mine, you can see a calendar created in Access that runs inside of a browser with the new Access Web publishing feature.

So, all you really need to do here is format a form with text boxes and setup some code to fill them. VBA or even the above video shows this is quite easy for Access.

The Legend of Zelda: Breath of the Wild--Creating a Champion , The Legend of Zelda: Breath of the Wild--Creating a Champion [Nintendo] on *FREE* shipping on qualifying offers. The Legend of Zelda: Breath of  Learn how to create and convert any file into an animated gif. All it takes is 3 easy steps! Then watch the magic happen!

Creating a personal access token for the command line, You can create a personal access token and use it in place of a password when performing Git operations over HTTPS with Git on the command line or the API. Create Your Apple ID Create Your Apple ID. Apple Footer. More ways to shop: Find an Apple Store or other retailer near you. Or call 1-800-MY-APPLE.

10 Steps to Creating a Data-Driven Culture, Our work in a range of industries indicates that the biggest obstacles to creating data-based businesses aren't technical; they're cultural. Create flash cards online and we'll make studying fast and fun!

Creating a Purpose-Driven Organization, We're not talking about having a clear mission that focuses largely on how a business will generate economic value. DTE had one that set out the goal of creating  Sign up for Facebook and find your friends. Create an account to start sharing photos and updates with people you know. It's easy to register.

Creating a Sustainable Food Future, CREATING A SUSTAINABLE FOOD FUTURE. A Menu of Solutions to Feed Nearly 10 Billion People by 2050. Can we feed the world without destroying the  Proven by the success of thousands of students, Creating a Masterpiece is the perfect choice to empower students with the skills needed to succeed in creating fine art. Students of all ages will be challenged by the unique emphasis on producing beautiful, masterpiece quality artwork.

Coronavirus Is Creating a Food Security Crisis in Indian Country , 'There's no way this is going to end well.' The coronavirus is devastating Indigenous communities, long been shut out of traditional food supply  Email this graph HTML Text To: You will be emailed a link to your saved graph project where you can make changes and print. Lost a graph? Click here to email you a list of your saved graphs. TIP: If you add to your contacts/address book, graphs that you send yourself through this system will not be blocked or filtered.

  • Hmmm. Well, I'm not trying to set up a traditional "calendar"--as in, "where will I be on a day and at what time." What I am trying to do is set up a curriculum to use administratively to see our entire schedule (a couple hundred courses) and where the days and times are used the heaviest. We also will want to filter down to ONLY see certain subject areas, etc. I'm not sure if Google calendar would provide the flexibility.
  • Great answers to all, but this is exactly what I needed! Thanks SO much!
  • I know, I know, we are four years down the line BUT this just happens to be exactly what I was looking for. Unfortunately, my datasheet shows up empty. I am running MS Access 2016 and followed the answer to the letter. Could you please give some details on the form settings? Thanx
  • Thanks for taking the time to post. An answer should concisely, completely, and unbiasedly answer the question. If you have an answer that will answer the question please do post it.