Help in Querying data from 2 tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Antony - 02 Dec 2005 16:58 GMT

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

Reply to this Message

KARL DEWEY - 02 Dec 2005 19:46 GMT

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

> I have 2 tables the first called users has the fields:
> UserID, UserName, Comments
[quoted text clipped - 20 lines]
>
> Antony

Reply to this Message