relational database

[kumar]

i am new to access. i am trying to create a relational database. i have three
tables

#1 student
fields are student_ID (primary key), student_name

#2 exam
fields are exam_ID (primary Key), exam_name, full_marks

#3 marks
fields are exam_ID, student_ID (both are primary keys), marks_obtained

i have connected them with relation ships (one to many). now what do i do to
have this one up and running? i wish to have a form -- in such a way that i
enter one data only once. what do i do for that? kindly help. thank you.

 

[Ken Sheridan]

Just to clear up one point, its not quite true to say that 'both are primary
keys' in the case of the exam_ID and student_ID columns in the marks table.
The two columns actually make up a single composite primary key

As far as data entry is concerned you have two choices here; you can either
have a student form with a marks subform, so that you can enter marks for
multiple exams for the current student; or you can have an exams form with a
marks subform so that you can enter marks for multiple students for the
current exam.

For the first option base the main form, which will be in single form view,
on a query which returns all students in alphabetic order:

SELECT *
FROM student
ORDER BY student_name;

Base the subform on the marks table. The subform will be in continuous form
view and linked to the main form on student_ID as the LinkMasterFields and
LinkChildFields properties. Add a text box bound to the marks_obtained
column, and a combo box bound to the exam_ID column. So that the exam names
show in the combo box set it up as follows:

RowSource: SELECT exam_ID, exam_name FROM exam ORDER BY exam_name;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

You'll also need a form bound to the exam table (frmExam say) in which you
can enter rows in the table. If you are entering marks for an exam not yet
in the exam table you can do so by typing the name into the combo box
(cboExam say) on the subform and putting code like this in the combo box's
NotInList event procedure:

Private Sub cboExam_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmExam", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmExam closed
DoCmd.Close acForm, "frmExam"
' ensure exam has been added
If Not IsNull(DLookup("exam_ID", "exam", "exam_name = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Exam table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub


Then in frmExam's Open event procedure put:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.exam_name.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

For the second option of an exams main form with a marks subform the
approach would be very similar of course, but the subform would include a
combo box bound to student_id, not exam_id, and would open a student form via
its NotInList event procedure.

Ken Sheridan
Stafford, England

 

[kumar]

Thanks a lot Ken for your time and kind assistance. I request some more time
of yours. As I am new so lets go step by step.
Firstly.
Please guide me how to build this query
SELECT *
FROM student
ORDER BY student_name;
What I understand is I go to query design view there I find
Field:
Table:
Sort:
Show:
Criteria:
Or:
How do I fill those and do I have to select any field from any table
initially? I understand this SQL query (I have little bit of hands on
knowledge of query writing in SQL though I have never on SQL in computer). I
am not able to correlate SQL query statements with access interface. Sorry to
bother you. Your help would be appreciated. Thank you. Kumar.

 

[John W. Vinson]

Please guide me how to build this query
SELECT *
FROM student
ORDER BY student_name;
What I understand is I go to query design view there I find
Field:
Table:
Sort:
Show:
Criteria:
Or:
How do I fill those and do I have to select any field from any table
initially? I understand this SQL query (I have little bit of hands on
knowledge of query writing in SQL though I have never on SQL in computer). I
am not able to correlate SQL query statements with access interface. Sorry to
bother you. Your help would be appreciated. Thank you. Kumar.

pardon my jumping in but...

Create a new Query without selecting any tables.

Select View... SQL on the menu.

Copy and paste or type the SQL query into that window.


OR, alternatively


Create a new Query. Select the Student table. Select either the * pseudo-field
to see all fields in the table, or click and drag those fields that you want
to see from the table icon into the Field row.

Select "Ascending" under Student_Name on the Sort row.


These are two different ways to get to the same result. The SQL view is common
in messages on the newsgroup because it can be posted in a readable manner;
the query grid cannot, without great effort.