Selecting the most recent

[Pam]

I'm very new to ACCESS, so this is probably something very
simple.
table1 that has 'name's
table2, has log entries for each time that 'name' has
logged onto a system.
Not every 'name from table one has logged on

I want to run a query that joins the tables such that
every name from table1 has either a BLANK (it doesn't
exist in table2) or the "most recent logon date" from
table 2 for the 'name'. Right now, I've been able to get
the BLANKS, and ALL the matches (1 to many --- rather than
1 to most recent)

In Excel, Vlookup would return me ONLY the most recent
(depending on my sorting of table2)

My tables are too large to work within Excel, and I've had
to make a quick transition to Access. Please lead me in
the right direction. Thank you.

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[table1].[name],
Max([table2].[logon date]) AS [most recent logon date]
FROM
[table1]
LEFT JOIN
[table2]
ON
[table1].[name] = [table2].[name]
GROUP BY
[table1].[name]

This assumes that the "logon date" field in table2 has the Date/Time data
type.

 

[Dale Fye]

Pam,

This method uses a subquery to get the LastLogon for each [Name]. BTW
[Name] is a reserved word, so you should not use it as a field name.
If you are going to use it, make sure you wrap it in brackets when you
refer to it. The other thing this does is a left join, so it returns
all the values from Table 1, and only the matching values from T2.

Select T1.*, T2.LastLogon
FROM Table1 T1
LEFT JOIN
(SELECT [Name], MAX(LogOnDate) as LastLogon
FROM Table2
GROUP BY [Name]) as T2
ON T1.[Name] = T2.[Name]

--
HTH

Dale Fye


I'm very new to ACCESS, so this is probably something very
simple.
table1 that has 'name's
table2, has log entries for each time that 'name' has
logged onto a system.
Not every 'name from table one has logged on

I want to run a query that joins the tables such that
every name from table1 has either a BLANK (it doesn't
exist in table2) or the "most recent logon date" from
table 2 for the 'name'. Right now, I've been able to get
the BLANKS, and ALL the matches (1 to many --- rather than
1 to most recent)

In Excel, Vlookup would return me ONLY the most recent
(depending on my sorting of table2)

My tables are too large to work within Excel, and I've had
to make a quick transition to Access. Please lead me in
the right direction. Thank you.

 

[Pam]

I got this much to work. VERY MUCH appreciated. I'm
gettingthe concepts. It's been nearly 12 years since I've
written SQL code, and its not coming back to me as fast as
I'd like.
The problem I'm having now is -- I want to select more
than just the name -- I actually have a lot of fields that
I want to display -- (from both tables) -- but I only want
the information from table2 based on the row with the most
recent logon date.

When I try to add the additional fields, it gives me an
error. Going through the wizard - it adds to the Group By -
- which gives me incorrect results.

I'm thinking there's a set of () or additional grouping I
need to do.

Any direction to get be started would help. I've been
going through the online help - -but its not helping much.
Also went through the sample databases and got some ideas,
but I think I'm still missing just a very basic step.

THANKS IN ADVANCE

-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[table1].[name],
Max([table2].[logon date]) AS [most recent logon date]
FROM
[table1]
LEFT JOIN
[table2]
ON
[table1].[name] = [table2].[name]
GROUP BY
[table1].[name]

This assumes that the "logon date" field in table2 has the Date/Time data
type.

I'm very new to ACCESS, so this is probably something very
simple.
table1 that has 'name's
table2, has log entries for each time that 'name' has
logged onto a system.
Not every 'name from table one has logged on

I want to run a query that joins the tables such that
every name from table1 has either a BLANK (it doesn't
exist in table2) or the "most recent logon date" from
table 2 for the 'name'. Right now, I've been able to get
the BLANKS, and ALL the matches (1 to many --- rather than
1 to most recent)

In Excel, Vlookup would return me ONLY the most recent
(depending on my sorting of table2)

My tables are too large to work within Excel, and I've had
to make a quick transition to Access. Please lead me in
the right direction. Thank you.

.

 

[Brian Camire]

You might try something like this:

1. Create a query (say, named "Query1") that returns the most recent logon
date for each name from table2:

SELECT
[table2].[name],
Max([table2].[logon date]) AS [logon date]
FROM
[table2]
GROUP BY
[table2].[name]

2. Create a query (say, named "Query2") that joins Query1 to table2 to
return the record corresponding to the most recent logon date for each name
in table2:

SELECT
[table2].*
FROM
[table2]
INNER JOIN
[Query1]
ON
[table2].[name] = [Query1].[name]
AND
[table2].[logon date] = [Query1].[logon date]

3. Create a query that left joins table1 to Query2 to also return the all
records in table1 and the corresponding records in Query2:

SELECT
[table1].*,
[Query2].*
FROM
[table1]
LEFT JOIN
[Query2]
ON
[table1].[name] = [Query2].[name]

I got this much to work. VERY MUCH appreciated. I'm
gettingthe concepts. It's been nearly 12 years since I've
written SQL code, and its not coming back to me as fast as
I'd like.
The problem I'm having now is -- I want to select more
than just the name -- I actually have a lot of fields that
I want to display -- (from both tables) -- but I only want
the information from table2 based on the row with the most
recent logon date.

When I try to add the additional fields, it gives me an
error. Going through the wizard - it adds to the Group By -
- which gives me incorrect results.

I'm thinking there's a set of () or additional grouping I
need to do.

Any direction to get be started would help. I've been
going through the online help - -but its not helping much.
Also went through the sample databases and got some ideas,
but I think I'm still missing just a very basic step.

THANKS IN ADVANCE

-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[table1].[name],
Max([table2].[logon date]) AS [most recent logon date]
FROM
[table1]
LEFT JOIN
[table2]
ON
[table1].[name] = [table2].[name]
GROUP BY
[table1].[name]

This assumes that the "logon date" field in table2 has the Date/Time data
type.

I'm very new to ACCESS, so this is probably something very
simple.
table1 that has 'name's
table2, has log entries for each time that 'name' has
logged onto a system.
Not every 'name from table one has logged on

I want to run a query that joins the tables such that
every name from table1 has either a BLANK (it doesn't
exist in table2) or the "most recent logon date" from
table 2 for the 'name'. Right now, I've been able to get
the BLANKS, and ALL the matches (1 to many --- rather than
1 to most recent)

In Excel, Vlookup would return me ONLY the most recent
(depending on my sorting of table2)

My tables are too large to work within Excel, and I've had
to make a quick transition to Access. Please lead me in
the right direction. Thank you.

.