12 Replies - 818 Views - Last Post: 08 October 2016 - 12:09 AM

#1 alobi  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 148
  • Joined: 21-January 13

Table structure question

Posted 06 October 2016 - 08:31 AM

How should I represent this table. I am designing a project that will store information on each student in school in db.
[b]ID	Subject	Percent Grade	Letter Grade	Class Position 	Term 	Year   Total[/b]
11	        Science	90	         A	          10th 	        Final    2016   400
	        English	90	         A			
	        Math 	78	         C			
	        History	80	         B			
	        Biology	90	         A			
	        				
						

I am a novice in database designing. all suggestion, lead or examples will be appreciated. thanks. I am using access database and DB is the plate form

Is This A Good Question/Topic? 0
  • +

Replies To: Table structure question

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13568
  • View blog
  • Posts: 54,141
  • Joined: 12-June 08

Re: Table structure question

Posted 06 October 2016 - 09:03 AM

Typically you would have a table for students.. a table for classes.. and then a relationship table matching student ids to class ids and a grade value. The rest are things you would calculate from that info.
Was This Post Helpful? 1
  • +
  • -

#3 alobi  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 148
  • Joined: 21-January 13

Re: Table structure question

Posted 06 October 2016 - 01:49 PM

Thanks, this will get me started in the right direction
Was This Post Helpful? 0
  • +
  • -

#4 alobi  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 148
  • Joined: 21-January 13

Re: Table structure question

Posted 07 October 2016 - 06:25 AM

I have two tables in the db and this is what the table looks like but my problem is that I still do not account for letter_grade. The other table tblStudent I used for recording Student Information. Do I need a third table for Letter_grade or how do set this up to account for that.
 StudentID   English     Maths	History	 Science	biology
                      90	90	78	   80	          90



thank you for your time

This post has been edited by alobi: 07 October 2016 - 06:28 AM

Was This Post Helpful? 0
  • +
  • -

#5 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,400
  • Joined: 03-December 12

Re: Table structure question

Posted 07 October 2016 - 06:29 AM

Use a case statement in the query, and it will give you a letter grade, without binding you to it.
Was This Post Helpful? 0
  • +
  • -

#6 alobi  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 148
  • Joined: 21-January 13

Re: Table structure question

Posted 07 October 2016 - 06:53 AM

I am using a select case now to get that but, how do I store it in a table. and be able to reproduce the same result should the students come back some few years down the road and want a copy of the result?
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13568
  • View blog
  • Posts: 54,141
  • Joined: 12-June 08

Re: Table structure question

Posted 07 October 2016 - 06:59 AM

Again, you wouldn't, right? The number grade is the key piece to store.. the query to retrieve the data would provide some sort of letter to it; no need to be stored.
Was This Post Helpful? 1
  • +
  • -

#8 alobi  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 148
  • Joined: 21-January 13

Re: Table structure question

Posted 07 October 2016 - 06:30 PM

Do you know of any article I can read to get a better Idea what I need to do. than

This post has been edited by alobi: 07 October 2016 - 06:30 PM

Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13568
  • View blog
  • Posts: 54,141
  • Joined: 12-June 08

Re: Table structure question

Posted 07 October 2016 - 06:40 PM

For what part? Queries, table making, etc are all broad topics - there's a ton of books on SQL to walk you through it all.
Was This Post Helpful? 0
  • +
  • -

#10 alobi  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 148
  • Joined: 21-January 13

Re: Table structure question

Posted 07 October 2016 - 07:26 PM

I have done the tables, queries that will allow me to pull letter grades into a report without storing them in the database
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13568
  • View blog
  • Posts: 54,141
  • Joined: 12-June 08

Re: Table structure question

Posted 07 October 2016 - 07:59 PM

Like it was suggested - a SELECT CASE would do.
Was This Post Helpful? 0
  • +
  • -

#12 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,400
  • Joined: 03-December 12

Re: Table structure question

Posted 07 October 2016 - 08:04 PM

You have one major thing against you, and that is doing this in Access. You need to investigate how to do a CASE statement, or fake it, in Access's bastard SQL.
Was This Post Helpful? 0
  • +
  • -

#13 alobi  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 148
  • Joined: 21-January 13

Re: Table structure question

Posted 08 October 2016 - 12:09 AM

For the benefits of those who maybe facing the same problem, the answer is to use the Switch function
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1