Unter form with Max (1 Viewer)

G

georg7

Registered User.

Local time

Yesterday, 21:28

Joined

Sep 3, 2019

Messages

84

• Dec 6, 2019

• #1

Hi there, I've build a form where I can pick an Identity number in the Dropdown menu to get all events of that person. It works perfect, but I've realized that it shows really every event.
Every Event is categorized.
My Problem is that is just want the latest event for each category for that user.
So i tried it with the max clause but then Nothing in that form worked.

How do I get just the Max Dates per category from the choosen user?

:banghead:

Thank you guys.
Have a nice one

Georg

 

Attachments

• 
  qry.PNG

  27.8 KB · Views: 93
• 
  frm.PNG

  34.4 KB · Views: 69
• 
  frm1.PNG

  23.6 KB · Views: 69
• 
  Inkedfrm2_LI.jpg

  75 KB · Views: 72

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 14:28

Joined

Jan 20, 2009

Messages

12,851

• Dec 6, 2019

• #2

You need to subquery to identify the event with the latest event grouped by Category then join that back to your main query on Category

 

G

georg7

Registered User.

Local time

Yesterday, 21:28

Joined

Sep 3, 2019

Messages

84

• Dec 6, 2019

• #3

like that:
SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_MitSchul.eDatum, tbl_Schulungstyp.SchulName
FROM tbl_Schulungstyp INNER JOIN (tbl_Mitarbeiter INNER JOIN tbl_MitSchul ON tbl_Mitarbeiter.ID = tbl_MitSchul.Mit_ID) ON tbl_Schulungstyp.SchulungsID = tbl_MitSchul.Schul_ID
WHERE eDatum in (Max eDatum FROM tbl_MitSchul);


?
I've never done this before xd

 

June7

AWF VIP

Local time

Yesterday, 20:28

Joined

Mar 9, 2014

Messages

5,466

• Dec 6, 2019

• #4

If you join to an aggregate query then the dataset will not be editable.

Syntax of that WHERE clause is not valid.

I can't follow your table relationships so see if this example gives you any guidance.
SELECT * FROM Purchases WHERE PONum IN (SELECT Max(PONum) AS MaxPO FROM Purchases GROUP BY AccountNum);

 

G

georg7

Registered User.

Local time

Yesterday, 21:28

Joined

Sep 3, 2019

Messages

84

• Dec 6, 2019

• #5

Okay I don't get it to work.
Is there any other possibility to get just the ID with the MaxEventdate that I've choosen?
Now my query selects the ID and the MaxEventDate. I want to create a Dropdown where i can choose the ID to get all MaxEventDates for that ID.
Maybe someone know other options.

Im to bad for this

 

G

georg7

Registered User.

Local time

Yesterday, 21:28

Joined

Sep 3, 2019

Messages

84

• Dec 6, 2019

• #6

EDIT:
Guys I found a way. Thank you guys!