Automatically updating fields

[Ashley Horsley]

Hi,
Please can you help. I am in desperate need of answers.
I have a database that has got a field called CandidateID and several other
forms using this field as a relationship for data.
On one of the forms there is a field called Course1ID, and then fields
Course2ID up to Course4ID. Then on a different form, I want the value in a
field called Course1ID to automatically update to the one specific to a
Candidate ID, when the CandidateID is selected in a drop-down field on that
form.

Is this possible?

Please help.

Ashley Horsley
(e-mail address removed)

 

[Ashley Horsley]

Please can I have some suggestions.

Ashley

 

[Lynn Trapp]

Ashley,
It is not easy to know if it's possible without knowing more about your
database structure. Having fields named like "Course1ID", "Course2ID", etc.
suggests that your table isn't normalized. Could you supply us with more
information?

 

[Ashley Horsley]

Hi,
Sorry but could you explain what you mean by normalised?
The database is structured so that one form is for entering personal data
(candidateID and the CourseID's that the candidate is taking)
On a seperate form there is a drop down box for candidateID and a seperate
field for the courseID. Then another set of fields are for entering the
grades the candidate obtained. I want the courseID value to be automatically
selected when the candidate ID is selected. So it changes when crolling
through the CandidateID's.


Ashley

 

[Lynn Trapp]

Ashley,
You are creating a database for a class and don't know what normalized
tables are? If that's the case, then you need to go back to square one and
learn that. Would you please post the structure of your tables? That's
where you need to start to figure out this problem. List all of the tables
in your database.

 

[Ashley Horsley]

Sorry about the problems. Just some background info, I am studying ICT for
GCSE and A-Level by myself. I have no teacher or instructor. I am 15 years
old, and to be taking a large number of examinations this year. This is why
I need help.
Below is a documentor report (sorry about the length, and I hope this
helps):

C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: After SHFGS
Page: 1

Properties

DateCreated: 20/06/2004 13:23:36 DefaultView:
Datasheet

GUID: {guid {7F0A9B8F-52B6-4589- LastUpdated:
20/06/2004 13:44:28

B69D-A2F3F3A406D3}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 0

Updatable: True

Columns

Name
Type Size

CandidateID
Text 4

AllowZeroLength: False

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 4

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440;1440;1440;1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{F79A1929-B382-4594-A445-392963B7A6EF}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 5760twip

OrdinalPosition: 0

Required: False

RowSource: SELECT [Student
Details].CandidateID, [Student Details].FirstName,

[Student
Details].LastName, [Student Details].Form FROM [Student

Details] ORDER
BY [CandidateID], [FirstName], [LastName], [Form];

RowSourceType: Table/Query

SourceField: CandidateID

SourceTable: After SHFGS

UnicodeCompression: False

FinalDestination
Memo -

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: Neutral

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid
{338EA3D0-0A52-480E-83F4-6331CE39D9B7}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: After SHFGS
Page: 2

Required: False

SourceField: FinalDestination

SourceTable: After SHFGS

UnicodeCompression: False

Course
Memo -

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: Neutral

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid
{BC85CF51-7CE9-4F43-8A48-C0648557CF5C}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Course

SourceTable: After SHFGS

UnicodeCompression: False

Relationships

Student DetailsAfter SHFGS

Student Details
After SHFGS

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

PrimaryKey 1

Clustered: False

DistinctCount: 0

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 3

Properties

DateCreated: 20/06/2004 13:20:00 DefaultView:
Datasheet

GUID: {guid {DBCE0F31-A88D-4A08- LastUpdated:
20/06/2004 13:34:47

91E4-921B1C37A556}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 1

Updatable: True

Columns

Name
Type Size

CandidateID
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 4

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440;1440;1440;1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{EE9A2A69-2545-4E5A-983A-385FD0D20927}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 5760twip

OrdinalPosition: 0

Required: False

RowSource: SELECT [Student
Details].CandidateID, [Student Details].FirstName,

[Student
Details].LastName, [Student Details].Form FROM [Student

Details] ORDER
BY [CandidateID], [FirstName], [LastName], [Form];

RowSourceType: Table/Query

SourceField: CandidateID

SourceTable: Grades

UnicodeCompression: True

Module1Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{61D19ACF-D2B5-4641-88A1-B9E18445B5C2}}

IMEMode: 0

IMESentenceMode: 3



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 4

OrdinalPosition: 1

Required: False

SourceField: Module1Grade

SourceTable: Grades

UnicodeCompression: False

Module1ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{650C98FD-DFE4-4CF1-87F2-13EB9D8FFE26}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2

Required: False

SourceField: Module1ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module2Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{1412FE57-AFF3-4B8E-AA7A-3A65619E0051}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Module2Grade

SourceTable: Grades

UnicodeCompression: False

Module2ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{EADE4816-1B7F-4749-8B22-0C6C2D4724B4}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 4

Required: False

SourceField: Module2ResitGrade



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 5

SourceTable: Grades

UnicodeCompression: False

Module3Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{6745DC44-6D70-48A9-9F5E-2CB80C6D8746}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 5

Required: False

SourceField: Module3Grade

SourceTable: Grades

UnicodeCompression: False

Module3ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{CBCCE804-C84F-4AB4-A2A2-31C0BD1ACABF}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 6

Required: False

SourceField: Module3ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module4Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{51675417-ED32-472B-9A1A-197BE95161FF}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 7

Required: False

SourceField: Module4Grade

SourceTable: Grades

UnicodeCompression: False



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 6

Module4ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{59285E92-E7E1-4F5D-8276-CFE3754EE706}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 8

Required: False

SourceField: Module4ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module5Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{2A280B59-B410-4942-B5E3-B325B03CA09B}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 9

Required: False

SourceField: Module5Grade

SourceTable: Grades

UnicodeCompression: False

Module5ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{8D45BD46-42D6-4E5B-9E69-79FB1C34C147}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 10

Required: False

SourceField: Module5ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module6Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 7

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{5022565E-AB36-40D9-863D-D9FDF39E7CCE}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 11

Required: False

SourceField: Module6Grade

SourceTable: Grades

UnicodeCompression: False

Module6ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{49BF15DA-F242-4DB2-ABE7-9C0131AA89CB}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 12

Required: False

SourceField: Module6ResitGrade

SourceTable: Grades

UnicodeCompression: False

OverallASGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{74FE82A8-6A54-4137-9869-726763309193}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 13

Required: False

SourceField: OverallASGrade

SourceTable: Grades

UnicodeCompression: False

OverallA2Grade
Text 50

AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 8

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{B9C98040-8670-4933-BA8E-57EEF2C7B661}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 14

Required: False

SourceField: OverallA2Grade

SourceTable: Grades

UnicodeCompression: True

Relationships

Student DetailsGrades

Student Details
Grades

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

PrimaryKey 1

Clustered: False

DistinctCount: 1

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 9

Properties

DateCreated: 20/06/2004 13:22:29 DefaultView:
Datasheet

GUID: {guid {53C088F3-218C-4E59- LastUpdated:
19/08/2004 17:19:51

BD84-40D4C3063B41}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 2

Updatable: True

Columns

Name
Type Size

CandidateID
Long Integer 4

AllowZeroLength: False

Attributes: Fixed Size

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DecimalPlaces: Auto

DisplayControl: Text Box

GUID: {guid
{0F7DA9DD-E578-4B7B-AC6F-CBD5BBA09BBD}}

OrdinalPosition: 0

Required: True

SourceField: CandidateID

SourceTable: Student Details

FirstName
Text 50

AllowZeroLength: False

Attributes: Variable Length

Caption: First Name

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{1684724A-172D-4E3A-9925-2DDDFA12A3DB}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 1

Required: True

SourceField: FirstName

SourceTable: Student Details

UnicodeCompression: False

LastName
Text 50

AllowZeroLength: False

Attributes: Variable Length

Caption: Last Name

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 10

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{CCD0DB93-3083-46F1-B07A-5F430DC4695B}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2

Required: True

SourceField: LastName

SourceTable: Student Details

UnicodeCompression: False

Ethnicity
Text 50

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{E08F204E-A425-45E4-BF0F-FBE1FBCEBA8E}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Ethnicity

SourceTable: Student Details

UnicodeCompression: False

Form
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{E7765996-39FE-41E6-A219-1FCFDF9C5B2D}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 1440twip

OrdinalPosition: 4

Required: True

RowSource:
12.1;12.2;12.3;12.4;12.5;13.1;13.2;13.3;13.4;13.5

RowSourceType: Value List

SourceField: Form

SourceTable: Student Details

UnicodeCompression: False



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 11

Course 1
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{D3FA4340-A2FE-4EAA-9323-55A0976621B1}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 1440twip

OrdinalPosition: 5

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 1

SourceTable: Student Details

UnicodeCompression: True

Course 2
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{EF65A086-43A7-4BF2-9E74-03A05B372426}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip

OrdinalPosition: 6

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 12

Education"

RowSourceType: Value List

SourceField: Course 2

SourceTable: Student Details

UnicodeCompression: True

Course 3
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{1107DEC0-1CCF-43F7-AA86-D1E51A9CA07A}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip

OrdinalPosition: 7

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 3

SourceTable: Student Details

UnicodeCompression: True

Course 4
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{C7431EDF-0D30-4C90-AE51-B44CD2108F89}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 13

OrdinalPosition: 8

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 4

SourceTable: Student Details

UnicodeCompression: True

Relationships

Student DetailsAfter SHFGS

Student Details
After SHFGS

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Student DetailsGrades

Student Details
Grades

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

Form 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: Form

Primary: False

Required: False

Unique: False

Fields:

Form Ascending

LastName 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: LastName



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 14

Primary: False

Required: False

Unique: False

Fields:

LastName Ascending

PrimaryKey 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending





Thank you very much. I apprechiate your help.





Ashley

 

[Lynn Trapp]

Ashley,
Bravo to you for trying to take on something like a relational database
without the aid of an instructor. It's a pretty daunting task for someone
with twice your age and experience. It's a little bit tough to read the
report here in my newsreader, but I do see some of the things I suspected.
Particularly your StudentDetails table. That table has the repeating fields
that I thought it might -- and that's a big no-no in relational database
design. Here are some basics of Relational design -- very simple -- with an
example of how you might proceed.

1. A table should represent data of one kind and one kind only (Students,
Courses, CoursesTakenByStudents, etc.)
2. Any repeating fields should be put in another table.
3. Every table must have a field or fields that uniquely identify that
record throughout the database.
4. Fields in a table should store one and only one example of the
information intended for that field.

With that in mind, you might restructure your tables something like this...
and this is only some of them.

tblStudents
StudentID
FirstName
LastName
Address
City
State
PostalCode
--any other fields you need

tblCourses
CourseID
CourseDescription
--any other fields you need

tblCoursesTakenByStudents
CourseID
StudentID

You can change the field names to suit your purpose.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Sorry about the problems. Just some background info, I am studying ICT for
GCSE and A-Level by myself. I have no teacher or instructor. I am 15 years
old, and to be taking a large number of examinations this year. This is why
I need help.
Below is a documentor report (sorry about the length, and I hope this
helps):

C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: After SHFGS
Page: 1

Properties

DateCreated: 20/06/2004 13:23:36 DefaultView:
Datasheet

GUID: {guid {7F0A9B8F-52B6-4589- LastUpdated:
20/06/2004 13:44:28

B69D-A2F3F3A406D3}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 0

Updatable: True

Columns

Name
Type Size

CandidateID
Text 4

AllowZeroLength: False

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 4

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440;1440;1440;1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{F79A1929-B382-4594-A445-392963B7A6EF}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 5760twip

OrdinalPosition: 0

Required: False

RowSource: SELECT [Student
Details].CandidateID, [Student Details].FirstName,

[Student
Details].LastName, [Student Details].Form FROM [Student

Details] ORDER
BY [CandidateID], [FirstName], [LastName], [Form];

RowSourceType: Table/Query

SourceField: CandidateID

SourceTable: After SHFGS

UnicodeCompression: False

FinalDestination
Memo -

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: Neutral

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid
{338EA3D0-0A52-480E-83F4-6331CE39D9B7}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: After SHFGS
Page: 2

Required: False

SourceField: FinalDestination

SourceTable: After SHFGS

UnicodeCompression: False

Course
Memo -

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: Neutral

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid
{BC85CF51-7CE9-4F43-8A48-C0648557CF5C}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Course

SourceTable: After SHFGS

UnicodeCompression: False

Relationships

Student DetailsAfter SHFGS

Student Details
After SHFGS

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

PrimaryKey 1

Clustered: False

DistinctCount: 0

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 3

Properties

DateCreated: 20/06/2004 13:20:00 DefaultView:
Datasheet

GUID: {guid {DBCE0F31-A88D-4A08- LastUpdated:
20/06/2004 13:34:47

91E4-921B1C37A556}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 1

Updatable: True

Columns

Name
Type Size

CandidateID
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 4

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440;1440;1440;1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{EE9A2A69-2545-4E5A-983A-385FD0D20927}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 5760twip

OrdinalPosition: 0

Required: False

RowSource: SELECT [Student
Details].CandidateID, [Student Details].FirstName,

[Student
Details].LastName, [Student Details].Form FROM [Student

Details] ORDER
BY [CandidateID], [FirstName], [LastName], [Form];

RowSourceType: Table/Query

SourceField: CandidateID

SourceTable: Grades

UnicodeCompression: True

Module1Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{61D19ACF-D2B5-4641-88A1-B9E18445B5C2}}

IMEMode: 0

IMESentenceMode: 3



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 4

OrdinalPosition: 1

Required: False

SourceField: Module1Grade

SourceTable: Grades

UnicodeCompression: False

Module1ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{650C98FD-DFE4-4CF1-87F2-13EB9D8FFE26}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2

Required: False

SourceField: Module1ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module2Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{1412FE57-AFF3-4B8E-AA7A-3A65619E0051}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Module2Grade

SourceTable: Grades

UnicodeCompression: False

Module2ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{EADE4816-1B7F-4749-8B22-0C6C2D4724B4}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 4

Required: False

SourceField: Module2ResitGrade



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 5

SourceTable: Grades

UnicodeCompression: False

Module3Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{6745DC44-6D70-48A9-9F5E-2CB80C6D8746}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 5

Required: False

SourceField: Module3Grade

SourceTable: Grades

UnicodeCompression: False

Module3ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{CBCCE804-C84F-4AB4-A2A2-31C0BD1ACABF}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 6

Required: False

SourceField: Module3ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module4Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{51675417-ED32-472B-9A1A-197BE95161FF}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 7

Required: False

SourceField: Module4Grade

SourceTable: Grades

UnicodeCompression: False



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 6

Module4ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{59285E92-E7E1-4F5D-8276-CFE3754EE706}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 8

Required: False

SourceField: Module4ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module5Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{2A280B59-B410-4942-B5E3-B325B03CA09B}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 9

Required: False

SourceField: Module5Grade

SourceTable: Grades

UnicodeCompression: False

Module5ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{8D45BD46-42D6-4E5B-9E69-79FB1C34C147}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 10

Required: False

SourceField: Module5ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module6Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 7

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{5022565E-AB36-40D9-863D-D9FDF39E7CCE}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 11

Required: False

SourceField: Module6Grade

SourceTable: Grades

UnicodeCompression: False

Module6ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{49BF15DA-F242-4DB2-ABE7-9C0131AA89CB}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 12

Required: False

SourceField: Module6ResitGrade

SourceTable: Grades

UnicodeCompression: False

OverallASGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{74FE82A8-6A54-4137-9869-726763309193}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 13

Required: False

SourceField: OverallASGrade

SourceTable: Grades

UnicodeCompression: False

OverallA2Grade
Text 50

AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 8

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{B9C98040-8670-4933-BA8E-57EEF2C7B661}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 14

Required: False

SourceField: OverallA2Grade

SourceTable: Grades

UnicodeCompression: True

Relationships

Student DetailsGrades

Student Details
Grades

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

PrimaryKey 1

Clustered: False

DistinctCount: 1

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 9

Properties

DateCreated: 20/06/2004 13:22:29 DefaultView:
Datasheet

GUID: {guid {53C088F3-218C-4E59- LastUpdated:
19/08/2004 17:19:51

BD84-40D4C3063B41}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 2

Updatable: True

Columns

Name
Type Size

CandidateID
Long Integer 4

AllowZeroLength: False

Attributes: Fixed Size

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DecimalPlaces: Auto

DisplayControl: Text Box

GUID: {guid
{0F7DA9DD-E578-4B7B-AC6F-CBD5BBA09BBD}}

OrdinalPosition: 0

Required: True

SourceField: CandidateID

SourceTable: Student Details

FirstName
Text 50

AllowZeroLength: False

Attributes: Variable Length

Caption: First Name

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{1684724A-172D-4E3A-9925-2DDDFA12A3DB}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 1

Required: True

SourceField: FirstName

SourceTable: Student Details

UnicodeCompression: False

LastName
Text 50

AllowZeroLength: False

Attributes: Variable Length

Caption: Last Name

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 10

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{CCD0DB93-3083-46F1-B07A-5F430DC4695B}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2

Required: True

SourceField: LastName

SourceTable: Student Details

UnicodeCompression: False

Ethnicity
Text 50

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{E08F204E-A425-45E4-BF0F-FBE1FBCEBA8E}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Ethnicity

SourceTable: Student Details

UnicodeCompression: False

Form
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{E7765996-39FE-41E6-A219-1FCFDF9C5B2D}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 1440twip

OrdinalPosition: 4

Required: True

RowSource:
12.1;12.2;12.3;12.4;12.5;13.1;13.2;13.3;13.4;13.5

RowSourceType: Value List

SourceField: Form

SourceTable: Student Details

UnicodeCompression: False



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 11

Course 1
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{D3FA4340-A2FE-4EAA-9323-55A0976621B1}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 1440twip

OrdinalPosition: 5

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 1

SourceTable: Student Details

UnicodeCompression: True

Course 2
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{EF65A086-43A7-4BF2-9E74-03A05B372426}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip

OrdinalPosition: 6

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 12

Education"

RowSourceType: Value List

SourceField: Course 2

SourceTable: Student Details

UnicodeCompression: True

Course 3
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{1107DEC0-1CCF-43F7-AA86-D1E51A9CA07A}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip

OrdinalPosition: 7

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 3

SourceTable: Student Details

UnicodeCompression: True

Course 4
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{C7431EDF-0D30-4C90-AE51-B44CD2108F89}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 13

OrdinalPosition: 8

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 4

SourceTable: Student Details

UnicodeCompression: True

Relationships

Student DetailsAfter SHFGS

Student Details
After SHFGS

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Student DetailsGrades

Student Details
Grades

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

Form 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: Form

Primary: False

Required: False

Unique: False

Fields:

Form Ascending

LastName 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: LastName



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 14

Primary: False

Required: False

Unique: False

Fields:

LastName Ascending

PrimaryKey 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending





Thank you very much. I apprechiate your help.





Ashley

Ashley,
You are creating a database for a class and don't know what normalized
tables are? If that's the case, then you need to go back to square one and
learn that. Would you please post the structure of your tables? That's
where you need to start to figure out this problem. List all of the tables
in your database.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


value to

field

on

 

[Ashley Horsley]

Thank You. This is a massive help.
If you have any more suggestions, please keep them coming.

Ashley

Ashley,
Bravo to you for trying to take on something like a relational database
without the aid of an instructor. It's a pretty daunting task for someone
with twice your age and experience. It's a little bit tough to read the
report here in my newsreader, but I do see some of the things I suspected.
Particularly your StudentDetails table. That table has the repeating fields
that I thought it might -- and that's a big no-no in relational database
design. Here are some basics of Relational design -- very simple -- with an
example of how you might proceed.

1. A table should represent data of one kind and one kind only (Students,
Courses, CoursesTakenByStudents, etc.)
2. Any repeating fields should be put in another table.
3. Every table must have a field or fields that uniquely identify that
record throughout the database.
4. Fields in a table should store one and only one example of the
information intended for that field.

With that in mind, you might restructure your tables something like this...
and this is only some of them.

tblStudents
StudentID
FirstName
LastName
Address
City
State
PostalCode
--any other fields you need

tblCourses
CourseID
CourseDescription
--any other fields you need

tblCoursesTakenByStudents
CourseID
StudentID

You can change the field names to suit your purpose.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Sorry about the problems. Just some background info, I am studying ICT for
GCSE and A-Level by myself. I have no teacher or instructor. I am 15 years
old, and to be taking a large number of examinations this year. This is why
I need help.
Below is a documentor report (sorry about the length, and I hope this
helps):

C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: After SHFGS
Page: 1

Properties

DateCreated: 20/06/2004 13:23:36 DefaultView:
Datasheet

GUID: {guid {7F0A9B8F-52B6-4589- LastUpdated:
20/06/2004 13:44:28

B69D-A2F3F3A406D3}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 0

Updatable: True

Columns

Name
Type Size

CandidateID
Text 4

AllowZeroLength: False

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 4

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440;1440;1440;1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{F79A1929-B382-4594-A445-392963B7A6EF}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 5760twip

OrdinalPosition: 0

Required: False

RowSource: SELECT [Student
Details].CandidateID, [Student Details].FirstName,

[Student
Details].LastName, [Student Details].Form FROM [Student

Details] ORDER
BY [CandidateID], [FirstName], [LastName], [Form];

RowSourceType: Table/Query

SourceField: CandidateID

SourceTable: After SHFGS

UnicodeCompression: False

FinalDestination
Memo -

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: Neutral

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid
{338EA3D0-0A52-480E-83F4-6331CE39D9B7}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: After SHFGS
Page: 2

Required: False

SourceField: FinalDestination

SourceTable: After SHFGS

UnicodeCompression: False

Course
Memo -

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: Neutral

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid
{BC85CF51-7CE9-4F43-8A48-C0648557CF5C}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Course

SourceTable: After SHFGS

UnicodeCompression: False

Relationships

Student DetailsAfter SHFGS

Student Details
After SHFGS

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

PrimaryKey 1

Clustered: False

DistinctCount: 0

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 3

Properties

DateCreated: 20/06/2004 13:20:00 DefaultView:
Datasheet

GUID: {guid {DBCE0F31-A88D-4A08- LastUpdated:
20/06/2004 13:34:47

91E4-921B1C37A556}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 1

Updatable: True

Columns

Name
Type Size

CandidateID
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 4

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440;1440;1440;1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{EE9A2A69-2545-4E5A-983A-385FD0D20927}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 5760twip

OrdinalPosition: 0

Required: False

RowSource: SELECT [Student
Details].CandidateID, [Student Details].FirstName,

[Student
Details].LastName, [Student Details].Form FROM [Student

Details] ORDER
BY [CandidateID], [FirstName], [LastName], [Form];

RowSourceType: Table/Query

SourceField: CandidateID

SourceTable: Grades

UnicodeCompression: True

Module1Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{61D19ACF-D2B5-4641-88A1-B9E18445B5C2}}

IMEMode: 0

IMESentenceMode: 3



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 4

OrdinalPosition: 1

Required: False

SourceField: Module1Grade

SourceTable: Grades

UnicodeCompression: False

Module1ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{650C98FD-DFE4-4CF1-87F2-13EB9D8FFE26}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2

Required: False

SourceField: Module1ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module2Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{1412FE57-AFF3-4B8E-AA7A-3A65619E0051}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Module2Grade

SourceTable: Grades

UnicodeCompression: False

Module2ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{EADE4816-1B7F-4749-8B22-0C6C2D4724B4}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 4

Required: False

SourceField: Module2ResitGrade



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 5

SourceTable: Grades

UnicodeCompression: False

Module3Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{6745DC44-6D70-48A9-9F5E-2CB80C6D8746}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 5

Required: False

SourceField: Module3Grade

SourceTable: Grades

UnicodeCompression: False

Module3ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{CBCCE804-C84F-4AB4-A2A2-31C0BD1ACABF}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 6

Required: False

SourceField: Module3ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module4Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{51675417-ED32-472B-9A1A-197BE95161FF}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 7

Required: False

SourceField: Module4Grade

SourceTable: Grades

UnicodeCompression: False



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 6

Module4ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{59285E92-E7E1-4F5D-8276-CFE3754EE706}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 8

Required: False

SourceField: Module4ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module5Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{2A280B59-B410-4942-B5E3-B325B03CA09B}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 9

Required: False

SourceField: Module5Grade

SourceTable: Grades

UnicodeCompression: False

Module5ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{8D45BD46-42D6-4E5B-9E69-79FB1C34C147}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 10

Required: False

SourceField: Module5ResitGrade

SourceTable: Grades

UnicodeCompression: False

Module6Grade
Text 30

AllowZeroLength: False

Attributes: Variable Length



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 7

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{5022565E-AB36-40D9-863D-D9FDF39E7CCE}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 11

Required: False

SourceField: Module6Grade

SourceTable: Grades

UnicodeCompression: False

Module6ResitGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{49BF15DA-F242-4DB2-ABE7-9C0131AA89CB}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 12

Required: False

SourceField: Module6ResitGrade

SourceTable: Grades

UnicodeCompression: False

OverallASGrade
Text 30

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{74FE82A8-6A54-4137-9869-726763309193}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 13

Required: False

SourceField: OverallASGrade

SourceTable: Grades

UnicodeCompression: False

OverallA2Grade
Text 50

AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Grades
Page: 8

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{B9C98040-8670-4933-BA8E-57EEF2C7B661}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 14

Required: False

SourceField: OverallA2Grade

SourceTable: Grades

UnicodeCompression: True

Relationships

Student DetailsGrades

Student Details
Grades

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

PrimaryKey 1

Clustered: False

DistinctCount: 1

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 9

Properties

DateCreated: 20/06/2004 13:22:29 DefaultView:
Datasheet

GUID: {guid {53C088F3-218C-4E59- LastUpdated:
19/08/2004 17:19:51

BD84-40D4C3063B41}}

NameMap: Long binary data OrderByOn:
False

Orientation: Left-to-Right
RecordCount: 2

Updatable: True

Columns

Name
Type Size

CandidateID
Long Integer 4

AllowZeroLength: False

Attributes: Fixed Size

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DecimalPlaces: Auto

DisplayControl: Text Box

GUID: {guid
{0F7DA9DD-E578-4B7B-AC6F-CBD5BBA09BBD}}

OrdinalPosition: 0

Required: True

SourceField: CandidateID

SourceTable: Student Details

FirstName
Text 50

AllowZeroLength: False

Attributes: Variable Length

Caption: First Name

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{1684724A-172D-4E3A-9925-2DDDFA12A3DB}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 1

Required: True

SourceField: FirstName

SourceTable: Student Details

UnicodeCompression: False

LastName
Text 50

AllowZeroLength: False

Attributes: Variable Length

Caption: Last Name

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 10

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{CCD0DB93-3083-46F1-B07A-5F430DC4695B}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2

Required: True

SourceField: LastName

SourceTable: Student Details

UnicodeCompression: False

Ethnicity
Text 50

AllowZeroLength: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{E08F204E-A425-45E4-BF0F-FBE1FBCEBA8E}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: Ethnicity

SourceTable: Student Details

UnicodeCompression: False

Form
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{E7765996-39FE-41E6-A219-1FCFDF9C5B2D}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 1440twip

OrdinalPosition: 4

Required: True

RowSource:
12.1;12.2;12.3;12.4;12.5;13.1;13.2;13.3;13.4;13.5

RowSourceType: Value List

SourceField: Form

SourceTable: Student Details

UnicodeCompression: False



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 11

Course 1
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

ColumnWidths: 1440

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{D3FA4340-A2FE-4EAA-9323-55A0976621B1}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 1440twip

OrdinalPosition: 5

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 1

SourceTable: Student Details

UnicodeCompression: True

Course 2
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{EF65A086-43A7-4BF2-9E74-03A05B372426}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip

OrdinalPosition: 6

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 12

Education"

RowSourceType: Value List

SourceField: Course 2

SourceTable: Student Details

UnicodeCompression: True

Course 3
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{1107DEC0-1CCF-43F7-AA86-D1E51A9CA07A}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip

OrdinalPosition: 7

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 3

SourceTable: Student Details

UnicodeCompression: True

Course 4
Text 50

AllowZeroLength: True

Attributes: Variable Length

BoundColumn: 1

CollatingOrder: General

ColumnCount: 1

ColumnHeads: False

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Combo Box

GUID: {guid
{C7431EDF-0D30-4C90-AE51-B44CD2108F89}}

IMEMode: 0

IMESentenceMode: 3

LimitToList: False

ListRows: 8

ListWidth: 0twip



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 13

OrdinalPosition: 8

Required: False

RowSource: "Business
Studies";"Chemistry";"Biology";"Design and Technology

(Resistant
Materials";"Design and Technology (Textiles)";"Design and

Technology
(Graphics)";"Design and Technology


(Food)";"Physics";"Maths";"English Literature";"ICT";"Physical

Education"

RowSourceType: Value List

SourceField: Course 4

SourceTable: Student Details

UnicodeCompression: True

Relationships

Student DetailsAfter SHFGS

Student Details
After SHFGS

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Student DetailsGrades

Student Details
Grades

CandidateID
CandidateID

Attributes: Unique, Not
Enforced

RelationshipType: One-To-One

Table Indexes

Name Number of
Fields

Form 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: Form

Primary: False

Required: False

Unique: False

Fields:

Form Ascending

LastName 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: LastName



C:\Documents and Settings\Ashley\My Documents\YEAR 11\ICT\Copy (3) of A-
19 August 2004

level.mdb

Table: Student Details
Page: 14

Primary: False

Required: False

Unique: False

Fields:

LastName Ascending

PrimaryKey 1

Clustered: False

DistinctCount: 2

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

CandidateID Ascending





Thank you very much. I apprechiate your help.





Ashley

Ashley,
You are creating a database for a class and don't know what normalized
tables are? If that's the case, then you need to go back to square one and
learn that. Would you please post the structure of your tables? That's
where you need to start to figure out this problem. List all of the tables
in your database.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Hi,
Sorry but could you explain what you mean by normalised?
The database is structured so that one form is for entering personal data
(candidateID and the CourseID's that the candidate is taking)
On a seperate form there is a drop down box for candidateID and a seperate
field for the courseID. Then another set of fields are for entering the
grades the candidate obtained. I want the courseID value to be
automatically
selected when the candidate ID is selected. So it changes when crolling
through the CandidateID's.


Ashley
Ashley,
It is not easy to know if it's possible without knowing more about your
database structure. Having fields named like "Course1ID", "Course2ID",
etc.
suggests that your table isn't normalized. Could you supply us with
more
information?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Hi,
Please can you help. I am in desperate need of answers.
I have a database that has got a field called CandidateID and several
other
forms using this field as a relationship for data.
On one of the forms there is a field called Course1ID, and then fields
Course2ID up to Course4ID. Then on a different form, I want the value
in
a
field called Course1ID to automatically update to the one

specific
to

a
Candidate ID, when the CandidateID is selected in a drop-down

field

on
that
form.

Is this possible?

Please help.

Ashley Horsley
(e-mail address removed)

 

[Lynn Trapp]

Thank You. This is a massive help.

If you have any more suggestions, please keep them coming.

You're welcome. See what you can do with that first and then post back if
you have any problems.

 

[Ashley Horsley]

OK I will reply whatever happens.

Thanks
Ashley Horsley

Check out the my school's website, that I have designed and maintained, at
www.sirhenryfloyd.bucks.sch.uk (I am a lot better at webdesign then database
design.

Before this project I had only ever used a database once.

 

[Ashley Horsley]

Ok I have it set up how you say, and now back to the original question.
When I select the candidate, I want the course that they have for Course1ID
to come up in a field so that the teacher knows which course they are
filling in for.

Ashley

 

[Ashley Horsley]

I would like to say a big thank you to Lynn, for all the help that he has
given. It has been excellent.
Keep up the good work.

Ashley