ACCESS FEED: Re: [MS_AccessPros] Re: BEST SIX SUBJECTS

ps.

I missed the part about math ... guess you would have some criteria

maybe something like:
MySort: iif(SUBJECT = "Maths",1,0)
and make that the primary sort field, then descending mark, so Maths would always be listed first and, therefore, included in the underlying query (the first one) since it gets the top 6.

On 3/23/18 4:26 PM, crystal 8 wrote:

adding on, based upon the SQL that Duane gave you (refer to his original post to see without nonsense characters that email has added), giving you a way to disregard data that would be present in a real data example such as student ...

Rather than one SQL statement, you could also make 2 queries.

1. create a query that shows the top 6 values
ï¿½ï¿½ - base the query on the table
ï¿½ï¿½ - put the field you want to evaluate on the design grid, which is MARKS in your example
ï¿½ï¿½ --ï¿½ sort in descending order
ï¿½ï¿½ - add criteria such as student, term, course, etc, but you don't appear to have any
ï¿½ï¿½ - Set Top Values to 6
ï¿½ï¿½ ---- turn on the Property Sheet (Ctrl-Enter), click in a blank area of the query design screen, and you will see properties that apply to the query as a whole. There, you will see 'Top Values', which translates to the TOP keyword that Duane used in his example.

2. look at the datasheet view, you should see the 6 records you want
ï¿½ï¿½ - Save As --> qMyTop6

3. make a Totals query using qMyTop6 as its source, and Sum the Marks field
ï¿½ï¿½ - there, you will see the bottom line -- just one record with the total that you want.

have an awesome day,
crystal

On 3/23/18 3:47 PM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

hi Adeboyejo

I think Duane is assuming this is real data, in which case, grades on exams would be related to records in another table with the students who got them. Is this a school assignment? If so, we are happy to guide you, but we won't do your assignments.

have an awesome day,
crystal

On 3/23/18 3:36 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] 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 NO SUBJECT MARKS

1 Maths 70

1 English 85

1 Science 81

1 History 90

1 Chemistry 64

1 Biology 91

1 Agric 72

1 Physics 50

1 Music 68

1 Greek 72

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 No Maths English Science History Chemistry Biology Agric Physics Music Greek

1 70 85 81 90 64 91 72 50 68 72

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/NO SUBJECT MARKS

1 Maths 70

2 English 85

3 Science 81

4 History 90

5 Chemistry 64

6 Biology 91

7 Agric 72

8 Physics 50

9 Music 68

10 Greek 59

Sum of Best Six Marks 489

ï¿½

__._,_.___

Posted by: crystal 8 <strive4peace2008@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (14)

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.

Visit Your Group