Информатика и выч. техника \ Базы данных

Проектирование реляционной базы данных методом «Сущность – связь».Освоение метода «сущность – связь», страница 5

SELECT MonthName(Month(Objects!ComingInDate)) AS Месяц, Count(patient.PasNum) AS [Количество обратившихся]

FROM patient INNER JOIN ((Specialist INNER JOIN Illness ON Specialist.SpecID=Illness.SpecID) INNER JOIN Objects ON Illness.illnessID=Objects.ilnessID) ON patient.PatientID=Objects.PatientID

GROUP BY MonthName(Month(Objects!ComingInDate)), Specialist.SpecName

HAVING (((Specialist.SpecName)="терапевт"));

Запрос 5. Определить количество больных, направленных терапевтом к разным специалистам за прошедший год.

SELECT Count(Objects.PatientID) AS [Количество обратившихся ], Specialist.SpecName, Year(Objects!GoAwayDate) AS Год

FROM patient INNER JOIN ((Specialist INNER JOIN Illness ON Specialist.SpecID=Illness.SpecID) INNER JOIN Objects ON Illness.illnessID=Objects.ilnessID) ON patient.PatientID=Objects.PatientID

GROUP BY Specialist.SpecName, Year(Objects!GoAwayDate)

HAVING (((Year(Objects!GoAwayDate))=Year(Date())-1));

4. По построенным таблицам создадим формы.

Форма 1. Добавление данных о новом пациенте.

Рис. 8.

Проверка на пустоту полей осуществляется следующим кодом.

Option Compare Database

Dim flag1 As Integer

Dim flag2 As Integer

Dim flag3 As Integer

Dim flag4 As Integer

Dim flag5 As Integer

Private Sub Form_Load()

Me.Поле1 = Null

Me.Поле 4 = Null

Me.Поле 7 = Null

Me.Поле 10 = Null

Me.ПолесщСоСписком 12 = Null

flag1 = 1

flag2 = 1

flag3 = 1

flag4 = 1

flag5 = 1

Set db = CurrentDb

Set zap = db.OpenRecordset("TableForm1", dbOpenDynaset)

Do Until zap.EOF

zap.Edit

zap.[Birthdate] = Null

zap.[BloodDate] = Null

zap.[XRayDate] = Null

zap.Update

zap.MoveNext

Loop

zap.Close

Set num = db.OpenRecordset("tabTel", dbOpenDynaset)

Do Until num.EOF

num.Delete

'num.[Tel1] = Null

'num.Update

num.MoveNext

Loop

num.Close

db.Close

End Sub

Private Sub Кнопка18 _Click()

If (flag1 = 1 Or flag2 = 1 Or flag3 = 1 Or flag4 = 1 Or flag5 = 1) Then

Me.Êíîïêà18.Enabled = False

MsgBox ("не вес поля заполнены")

Else

Me.Кнопка18.Enabled = True

End If

Set db = CurrentDb

FIO = Me.Поле1.Value

Pass = Me.Поле 4.Value

Pol = Me.ПолеСоСписком12.Value

Addr = Me.Поле7.Value

Job = Me.Поле10.Value

M2:

Set REC = db.OpenRecordset("patient", dbOpenDynaset)

Set zap = db.OpenRecordset("TableForm1", dbOpenDynaset)

Set tel = db.OpenRecordset("patTel", dbOpenDynaset)

Set num = db.OpenRecordset("tabTel", dbOpenDynaset)

zap.MoveFirst

num.MoveFirst

REC.AddNew

REC.[FIO] = FIO

REC.[PasNum] = Pass

REC.[Address] = Addr

REC.[WorkPlace] = Job

REC.[Sex] = Pol

REC.[Birthdate] = zap.[Birthdate]

REC.[BloodDate] = zap.[BloodDate]

REC.[XRayDate] = zap.[XRayDate]

REC.Update

Do Until num.EOF

tel.AddNew

REC.MoveLast

tel.[PatientID] = REC.[PatientID]

tel.[Tel1] = num.[Tel1]

tel.Update

num.MoveNext

Loop

num.Close

REC.Close

zap.Close

tel.Close

db.Close

DoCmd.Close acForm, "new1"

Set db = CurrentDb

Set zap = db.OpenRecordset("TableForm1", dbOpenDynaset)

Do Until zap.EOF

zap.Edit

zap.[Birthdate] = Null

zap.[BloodDate] = Null

zap.[XRayDate] = Null

zap.Update

zap.MoveNext

Loop

zap.Close

Set num = db.OpenRecordset("tabTel", dbOpenDynaset)

Do Until num.EOF

num.Delete

num.MoveNext

Loop

num.Close

db.Close

End Sub

Private Sub Кнопка19_Click()

DoCmd.Close acForm, "new1"

Set db = CurrentDb

Set zap = db.OpenRecordset("TableForm1", dbOpenDynaset)

Do Until zap.EOF

zap.Edit

zap.[Birthdate] = Null

zap.[BloodDate] = Null

zap.[XRayDate] = Null

zap.Update

zap.MoveNext

Loop

zap.Close

Set num = db.OpenRecordset("tabTel", dbOpenDynaset)

Do Until num.EOF

num.Delete

num.MoveNext

Loop

num.Close

db.Close

End Sub

Private Sub Поле1_LostFocus()

If (Me.ПОле.text = "") Then

MsgBox ("Введите ФИО")

Me.Кнопка18.Enabled = False

Me.Поле1.SetFocus

flag1 = 1

Else

flag1 = 0

Me.Кнопка18.Enabled = True

End If

End Sub

Private Sub Поле10_LostFocus()

If (Me.Поле10.text = "") Then

MsgBox ("Введите место работы пациента")

Me.Кнопка18.Enabled = False

flag2 = 1

Else

flag2 = 0

Me.Кнопка18.Enabled = True

Скачать файл