Query For Getting ID Degree (1 Viewer)

[Jonny]

Having a table DegreeDate
Fields are : ID, DegreeDate, Degree.

Each ID may have more than one degree.

Wish to build a query that shows only those whose degree is 50.
If same ID has higher degree he will not be shown.

Will appreciate your help..

 

[theDBguy]

Hi. Did you try putting a criteria of 50 under the Degree column?

 

[Jonny]

Hi. Did you try putting a criteria of 50 under the Degree column?

Yes, but I want to get only those records where 50 is the higher degree.
If same ID has 60, 70, e.t.c not to show also 50.

 

[theDBguy]

Yes, but I want to get only those records where 50 is the higher degree.
If same ID has 60, 70, e.t.c not to show also 50.

I see. Maybe something like:

SELECT T1.* FROM TableName T1
INNER JOIN (SELECT T2.ID, Max(Degree) AS M
FROM TableName T2
GROUP BY T2.ID
HAVING Max(Degree)=50) SQ
ON T1.ID=SQ.ID
WHERE T1.Degree=50

 

[Gasman]

This method will do it. It shows me the highest amount for a client, with the lowest value being £500. If higher, that is shown instead.

SELECT Transactions.Description, Max(Transactions.Amount) AS MaxOfAmount
FROM Transactions
GROUP BY Transactions.Description
HAVING (((Max(Transactions.Amount))>=500));

 

[Jonny]

I'm a newbie in Access, may you add a screenshot how it looks in Access (not SQL).
Sorry for that..

 

[theDBguy]

I'm a newbie in Access, may you add a screenshot how it looks in Access (not SQL).
Sorry for that..

Instead, are you able to post a small copy of your db with test data?

 

[Gasman]

I'm a newbie in Access, may you add a screenshot how it looks in Access (not SQL).
Sorry for that..

Ok, here it is

 

[Jonny]

Instead, are you able to post a small copy of your db with test data?

Hereby please find attached,
Let's assume I'm putting "23" in Degree, then I don't want John to be shown as he has also 76.
Hoping it's clear..

 

[theDBguy]

Hereby please find attached,
Let's assume I'm putting "23" in Degree, then I don't want John to be shown as he has also 76.
Hoping it's clear..

Hi. Thanks, but I am not sure I understand. Here's a screenshot of your data.

So, if we use 23 for a Degree criteria, which records should show up in the result? Everybody but John? Or no one?

 

[Jonny]

Hi. Thanks, but I am not sure I understand. Here's a screenshot of your data.

So, if we use 23 for a Degree criteria, which records should show up in the result? Everybody but John? Or no one?

In this particular case nothing.

 

[theDBguy]

In this particular case nothing.

Okay, not sure it completely clarifies the ultimate intent, but I added a couple more records, since I want to make sure the query will return something acceptable other than nothing. Please let me know if this is anywhere close to what you want.


1. Open Table1 to review the two records I added
2. Open Query2 to see the result


If the result is correct for Degree of 23, then I think we can move on. If not, then please let us know why not and how else to move forward.


Thanks.

 

[Gasman]

Hereby please find attached,
Let's assume I'm putting "23" in Degree, then I don't want John to be shown as he has also 76.
Hoping it's clear..

Well my idea would not work,as it would show 76.

Sorry I misunderstood your initial post.

 

[June7]

Consider:

SELECT Table1.*
FROM Table1
WHERE Table1.Degree=[enter value] AND FirstName NOT IN(SELECT FirstName FROM Table1 WHERE Degree>[enter value]);

 

[Jonny]

Hi. Thanks, but I am not sure I understand. Here's a screenshot of your data.

So, if we use 23 for a Degree criteria, which records should show up in the result? Everybody but John? Or no one?

Okay, not sure it completely clarifies the ultimate intent, but I added a couple more records, since I want to make sure the query will return something acceptable other than nothing. Please let me know if this is anywhere close to what you want.


1. Open Table1 to review the two records I added
2. Open Query2 to see the result


If the result is correct for Degree of 23, then I think we can move on. If not, then please let us know why not and how else to move forward.


Thanks.

This one partially did the job, however when I put also DegreeDate column it shows me non-relevant record as well?!

 

[June7]

Did you see post 13?

 

[Jonny]

I've marked DegreeDate as Max and it solved the issue.

Thank you, guys, for your help!

 

[theDBguy]

Hi. Why are you modifying Query1? Has the intent changed? I asked you to open Query2 and verify its result. What are you trying to accomplish by adding the date column in Query1?

 

[Jonny]

Hi. Why are you modifying Query1? Has the intent changed? I asked you to open Query2 and verify its result. What are you trying to accomplish by adding the date column in Query1?

I took your solution , however when I was trying to add DegreeDate column the output was changed.
Apparently I was missing to add "Max" also to that column.