创建在Access中的“日历矩阵” [英] Creating a 'calendar matrix' in Access

问题描述

我想无论是创建报表或表格，在本质上是一种日历的形式显示数据。

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

在Access中，我试图创建一个基于查询的形式，会给我下面的矩阵：

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

• 列：以小时为单位时报
• 行：本周日

因此​​，例如，与上述数据，它会出现这样的： 编辑：Yargh，我不能提交的图像很遗憾。所以，这里是一个链接到一个课程表，也就是基本上我想要做什么：的日程表

So, for example, with the above data, it would appear like this: 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)?

编辑：

一个想法我是用一个场为在基质中（因此，例如，将有一星期一，8-9A字段每一个可能的细胞中产生的一种形式 - 而该字段将在是过滤器只显示结果，其中天包含M和的BeginTime或结束时间或8A和9A之间）查询。不幸的是，我不知道该怎么做。

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.

推荐答案

您可以做一些接近你似乎想为接入的形式，但是这并不容易。该屏幕截图显示在数据表视图的形式，其记录源是一个ADO连接记录您的样本数据。它使用条件格式设置文本框的背景颜色，当文本框值不为空。你的照片提出了不同的颜色为每个的课程的，但我不想应付，当不止一个的课程的可以安排在同一个时间块...我的方法是简单的，我应付。 ： - ）

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. :-)

在code创建并加载连接记录如下列为 GetRecordset（）。该形式的开放活动将其记录到 GetRecordset（）。

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

请注意我不同的存储你的样本数据。这是我的 Class_sessions 表：

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

这是函数来创建连接记录这是关键部分的这种做法。我使用早期绑定要求为 Microsoft ActiveX数据对象[版本]库的一参开发了这个;我使用的2.8版本。供生产使用，我会转换code使用后期绑定，丢弃参考。我把它作为早期绑定，让你可以使用智能感知，以帮助您了解它是如何工作的。

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
    Next
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Open
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.AddNew
    rsAdo!start_time = dteTime
    rsAdo.Update

    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
        rsAdo.Update
        rsDao.MoveNext
    Loop

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

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

这篇关于创建在Access中的“日历矩阵”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！