Replace values of a column with another column by criterias in another table (1 Viewer)

S

saqassemi

Registered User.
Local time
Today, 01:31
Joined
Nov 11, 2017
Messages
37
Hello every one. I am developing a database for our schools and I have to change arrangement of subjects every year because of some reason from ministry of education.
So I have a table to enter scores and a table to arrange subjects titles for every year and grades.
There is a query that rearrange values but i dont know how to reference Year and Grade criterias to tblSubjects.
The file is attached. Is there any solution without dlookup for scores in form beacuse i want to enter or change scores.
 

Attachments

  • reArrange fields Values.accdb
    500 KB · Views: 82
Ranman256

Ranman256

Well-known member
Local time
Today, 04:31
Joined
Apr 9, 2015
Messages
4,337
I have a School Yr table. each year a new curriculum is built with the classes for that year.

school yr.jpg
 
S

saqassemi

Registered User.
Local time
Today, 01:31
Joined
Nov 11, 2017
Messages
37
Mr Ranman256, Thanks for your reply. Could you attach a sample access file.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:31
Joined
Feb 19, 2002
Messages
43,266
Your problem is that the tables are not normalized. Ranman's solution is also not normalized so it wouldn't help either. Both look more like spreadsheets than relational database solutions. When you are building forms, use combos to select the FK values to avoid typos.

You need more tables.
tblSubjects
SubjectID (autonumber pk)
SubjectName

tblSchoolYear
SchoolYearID (autonumber pk)
SchoolYear
Grade

tblClasses
ClassID (autonumber pk)
SchoolYearID (FK to tblSchoolYear)
SubjectID (FK to tblSubjects)

tblStudents
StudentID (autonumber pk)
FirstName
LastName

ClassScores
ScoreID (autonumber pk)
ClassID (FK to tblClasses)
StudentID (FK to tblStudents)
TestDate
Score

This is a start. It will give you as many grades and classes as you need. It also provides for more than one score for each class. You can average them to get a total for the class for the year.
 
S

saqassemi

Registered User.
Local time
Today, 01:31
Joined
Nov 11, 2017
Messages
37
Thanks all. I know a query can replace values of fields. For example there are 3 fields. I replace data of column 1 with data of column 2 ...
I want a query to look at tblSubjects for replacing. There is a picture attached to say what i want simpler. Thanks for your times.
 

Attachments

  • replace columns values.jpg
    replace columns values.jpg
    71.9 KB · Views: 85
Last edited:
S

saqassemi

Registered User.
Local time
Today, 01:31
Joined
Nov 11, 2017
Messages
37
Pat Hartman said:
Your problem is that the tables are not normalized. Ranman's solution is also not normalized so it wouldn't help either. Both look more like spreadsheets than relational database solutions. When you are building forms, use combos to select the FK values to avoid typos.

You need more tables.
tblSubjects
SubjectID (autonumber pk)
SubjectName

tblSchoolYear
SchoolYearID (autonumber pk)
SchoolYear
Grade

tblClasses
ClassID (autonumber pk)
SchoolYearID (FK to tblSchoolYear)
SubjectID (FK to tblSubjects)

tblStudents
StudentID (autonumber pk)
FirstName
LastName

ClassScores
ScoreID (autonumber pk)
ClassID (FK to tblClasses)
StudentID (FK to tblStudents)
TestDate
Score

This is a start. It will give you as many grades and classes as you need. It also provides for more than one score for each class. You can average them to get a total for the class for the year.
Click to expand...

Could you upload it as an access file.
 
You must log in or register to reply here.

Similar threads

M
Solved Change column order for temporary table
Replies
3
Views
225
Martynwheeler
M
D
want to save the D1 Column Header and place it in all rows of column G
Replies
12
Views
662
Pat Hartman
P
T
Exploring possibilities
2 3
Replies
40
Views
1,528
561414
5
B
Dropdown list based on a query?
Replies
4
Views
433
GaP42
G
N
Counting not null values in 3 columns, storing each row count in a 4th column.
Replies
10
Views
482
Netassess
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom