Sabtu, 24 Maret 2018

Re: [MS_AccessPros] Re: BEST SIX SUBJECTS

 

Thanks Duane. I got it . Used subject as primary key as it is obvious a student cannot do a subject twice. The tie was eliminated with the code below.


Thank you also Crystal for your suggestion.

Ade

On Saturday, 24 March 2018, 13:25:25 GMT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

I'm on my phone all day but you can try

SELECT [EXAMINATION RECORDS].STUDENTID, [EXAMINATION RECORDS].[STUDENT EXAM NO], [EXAMINATION RECORDS].SUBJECT, [EXAMINATION RECORDS].MARKS
FROM [EXAMINATION RECORDS]
WHERE ((([EXAMINATION RECORDS].[primary key field]) In (SELECT TOP 5 [primary key field]
FROM [EXAMINATION RECORDS] ER
WHERE ER.[STUDENT EXAM NO] = [EXAMINATION RECORDS].[STUDENT EXAM NO]
AND SUBJECT <> "MATH"
ORDER BY MARKS DESC, SUBJECT))) OR ((([EXAMINATION RECORDS].SUBJECT)="MATH"))


Get Outlook for iOS
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, March 24, 2018 5:42:51 AM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Re: BEST SIX SUBJECTS
 


Thanks Duane,

the EXAMINATION RECORDS table is a transaction file and examination number is as unique as student id. Though I am able to see where seven subjects are retuned instead of six, that is not my goal. I created a table called STUDENTID where i can create a primary key. Could you please help modify the code which i adjusted a bit  in respect of MATH/MATHS  and shown below so that the tie can be broken and six subjects returned.

Thanks.

Ade

SELECT [EXAMINATION RECORDS].STUDENTID, [EXAMINATION RECORDS].[STUDENT EXAM NO], [EXAMINATION RECORDS].SUBJECT, [EXAMINATION RECORDS].MARKS
FROM [EXAMINATION RECORDS]
WHERE ((([EXAMINATION RECORDS].MARKS) In (SELECT TOP 5 MARKS 
 FROM [EXAMINATION RECORDS] ER
 WHERE ER.[STUDENT EXAM NO] = [EXAMINATION RECORDS].[STUDENT EXAM NO]
 AND SUBJECT <> "MATH"
 ORDER BY MARKS DESC, SUBJECT))) OR ((([EXAMINATION RECORDS].SUBJECT)="MATH"))


On Saturday, 24 March 2018, 01:47:55 GMT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Ade,

I hinted in my previous reply about a primary key. You can use it to break ties.  Primary key fields aren't required unless you need them. You almost always need them and then don't cost anything.


Duane



From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, March 23, 2018 6:06 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Re: BEST SIX SUBJECTS
 


Thanks Duanne,

largely ok except for the tie . Agric and Greek have 72 marks so one of them should drop as we already have five top marks but the query returns six because of this.

Ade

On Friday, 23 March 2018, 20:36:20 GMT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Ade,


Something like this might work but I don't see either a primary key or something resembling a student ID number.


SELECT [STUDENT EXAM NO], SUBJECT, MARKS
FROM [EXAMINATION RECORDS]
WHERE MARKS IN 
(SELECT TOP 5 MARKS 
 FROM [EXAMINATION RECORDS] ER
 WHERE ER.[STUDENT EXAM NO] = [EXAMINATION RECORDS].[STUDENT EXAM NO]
 AND SUBJECT <> "MATHS"
 ORDER BY MARKS DESC, SUBJECT)
OR SUBJECT = "MATH";

Again, please type []s around object names that contain spaces or symbols.

Thanks,
Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, March 23, 2018 2:15 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Re: BEST SIX SUBJECTS
 


I put the data in the form you prefer. Table name is EXAMINATION RECORDS and the three fields shown previously are the most relevant to the issue at hand.

Is there more clarity now ?

Ade



On Friday, 23 March 2018, 19:02:26 GMT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Ade,

I'm confused by your previous remarks "i know you do not like the excel format of tabulation' which I assume is my normal soap box regarding normalizing table structures while your current email suggests the table seems normalized.


So you have a table named [pls see below] with only three fields: [STUDENT EXAM NO], SUBJECT, and MARKS? (sorry, it is Friday afternoon)


Notice how I have taken the time to wrap any field name with spaces in []s for clarity. 


Regards,

Duane



From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, March 23, 2018 1:31 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Re: BEST SIX SUBJECTS
 


OK.

pls see below

STUDENT EXAM NOSUBJECTMARKS
1Maths70
1English85
1Science81
1History90
1Chemistry64
1Biology91
1Agric72
1Physics50
1Music68
1Greek72



On Friday, 23 March 2018, 18:20:54 GMT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Ade,

Again "Can you provide actual table and field names?"


Duane



From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, March 23, 2018 11:00 AM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Re: BEST SIX SUBJECTS
 


Thanks Duane,

i know you do not like the excel format of tabulation but its easy for me and so I am showing the data here. I have changed i field to make a tie... Really a tie does not make a difference in this case as it leaves the calculation unchanged at 489marks. If there is a tie at higher marks, this only displaces the lower marks that would have been included.

Ade

Exam NoMathsEnglishScienceHistoryChemistryBiologyAgricPhysicsMusicGreek
170858190649172506872
Please note also that a student can offer anything from 6 to 12 subjects but the best six are counted.


On Friday, 23 March 2018, 15:33:17 GMT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Hi Ade,


Is there a StudentID in your table/query? I assume you would like a query that returns for each student their math mark as well as their top five subjects by mark excluding math. 


How would you handle ties?


Can you provide actual table and field names?


Here is a query from the Northwind Orders table that displays the top 3 orders for each customer by Freight:


SELECT Orders.CustomerID, Orders.OrderDate, Orders.Freight
FROM Orders
WHERE (((Orders.OrderDate) In (SELECT TOP 3 O.OrderDate FROM Orders O where O.CustomerID = Orders.CustomerID ORDER BY O.OrderDate DESC )))
ORDER BY Orders.CustomerID, Orders.OrderDate DESC;

Regards,

Duane



From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, March 23, 2018 10:08 AM
To: wrmosca@comcast.net [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Re: BEST SIX SUBJECTS
 


Thanks Bill. Scores in Maths must be in regardless of the marks obtained. The others are the highest marks in five subjects among the remaining subjects.

Ade

On Friday, 23 March 2018, 14:54:35 GMT, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Ade - what determines the 6 "best" subjects?


Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com



---In MS_Access_Professionals@yahoogroups.com, <aoye_99@yahoo.co.uk> wrote :

Hi all,

the data below is hypothetical results of a student for simplicity in a situation where there are thousands of students who study varying number of subjects, with Maths being compulsory.

Would you know of a function that will help calculate the total marks in six best subjects ?

Thanks.

Ade

S/NOSUBJECTMARKS
1Maths70
2English85
3Science81
4History90
5Chemistry64
6Biology91
7Agric72
8Physics50
9Music68
10Greek59
Sum of Best Six Marks489

 












__._,_.___
Posted by: Adeboyejo Oyenuga <aoye_99@yahoo.co.uk>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (22)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)