Help in Querying data from 2 tables

[Antony]

I have 2 tables the first called users has the fields:
UserID, UserName, Comments

The 2nd table called SecurityLogins has the fields:
SecurityID, SecUserID, SecEffDate, SecUserLogon, SecUserPassword

There is a left join between Users.UserID and SecurityLogins.SecUserID.
Users.UserID and SecurityLogins.SecurityID are unique in their
respective tables.

SecurityLogins contains a history of the logons and password
combinations for the users.

I want to show all the fields from USERS and only the latest
SecUserLogon and SecUserPassword for each user from SECURITYLOGINS.

I have been trying using subqueries but keep getting error saying my
subquery is invalid.

Can someone please help?

TIA

Antony

 

[KARL DEWEY]

This is untested --
Change it to a Totals query with the following fields --
UserID, UserName, SecEffDate, SecUserLogon, SecUserPassword
Set the Totals row to the following --
Group By, Group By, Max, Group By, First