Find second to last date

[skizzly]

Hi all,

I am trying to find the second to last date (max Startdate - 1) from a group of student attendances.

Example:

Id Name SchName Startdate Endate
1 Jim Sch1 01/09/2010 20/10/2010
1 Jim Sch2 01/11/2010
1 Jim Sch3 09/10/2011
2 Kim Sch1 09/10/2011 20/12/2011
2 Kim Sch2 01/10/2012 09/10/2013
3 Pat Sch1 01/09/2009 20/07/2010
3 Pat SCh2 01/09/2010 20/10/2010
3 Pat Sch3 01/11/2010
4 Mic Sch1 09/09/2013

Desired output:

1 Jim sch2 01/11/2010
2 Kim sch1 09/10/2011
3 Pat sch2 01/09/2010
4 Mic sch1 09/09/2013

I would like to do this in one query and would like to know how this is done.

The attendance information comes from a separate query and the student information from another query. Is this possible? :banghead:

Thanks for any advice

 

[plog]

A few things:

1. 'Name' is a bad field name because it is a reserved word in access and can cause issues when writing code--which I suggest you do to accomplish this task.

2. Are Id and Name always the same pairings? If so, is the data you are showing from a table or query? If its a table and the Id/Name pairings are the same then your table isn't structured properly.

3. What is the name of the table/query that your data is in?

4. Why is the last record in your desired output? That person has no second to last attendance date.

 

[skizzly]

Hi plog, thanks for getting back to me...

1. name is just for the example. The actual field is forename

2. The data comes from a query that builds the attendances. I have no access to the physical table.

3. The query is called vwAttendances.

4. The second to last is required so that attendance can be contacted as part of data checking exercise

thanks in advance....

 

[plog]

1. What did you save by not giving actual field names the first time? Repost your data and provide accurate names.

2a. If you can run a query in Access, you have access to the physical table.

2b. So what do you want to find the second to last attendence date of--Id or Name/forename.

4. My question didn't have to do with second to last, it had to do with the last record of your expected results. Mic only had one attendance date, which makes it impossible for him to have a second to last attendence date. Why is he showing up in your expected data?

 

[mdlueck]

I am trying to find the second to last date (max Startdate - 1) from a group of student attendances.

Perhaps a nested/double query.

Inner query does a:

SELECT TOP 2
ORDER BY [xyz] DESC

And then from there:

SELECT TOP 1
ORDER BY [xyz]

The inner query will execute first, selecting the bottom most two records.
The outer query runs against the results of the inner query and selects the top record, which of the overall total records was the second to last - as you requested.

 

[skizzly]

thanks plog,

2a. i do not have access to the source tables used in the query just the query
2b. yes
4. you are correct, even there is not a second to last date return whatever is there

 

[skizzly]

hi mdluek,

thanks for the idea....

so is it two separate queries, in one? how would the nested query look like? very green with all this...

thanks in advance.

 

[mdlueck]

Here is one link describing such:

SELECT within SELECT Tutorial
http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

 

[skizzly]

Hi mdluek,

my query following you advice:

SELECT TOP 1 vwAttendances.ID, vwAttendances.SchName, vwAttendances.STARTDATE
FROM tbl_05 INNER JOIN vwAttendances ON tbl_05.ID = vwAttendances.ID
WHERE (((vwAttendances.STARTDATE) In (SELECT TOP 2 T.STARTDATE FROM vwAttendances T WHERE T.ID = vwAttendances.ID ORDER BY T.STARTDATE Desc)))
ORDER BY vwAttendances.STARTDATE;

however i only get one row returned...

tbl_05 has 26 rows...

any advice?

 

[mdlueck]

however i only get one row returned...

tbl_05 has 26 rows...

any advice?

That is what you asked for...

I am trying to find the second to last date (max Startdate - 1) from a group of student attendances.

Did you not state what you actually wanted?

 

[mdlueck]

Oh, and you did not quite understand what I meant about a query within a query. The outer query needs to select from the inner query, not try to grab also from the table itself.

Here is an elaborate query in Access which does what I meant:

Need to add static entry to a SQL populated Combo Box - UNION Query Example
http://www.access-programmers.co.uk/forums/showthread.php?t=219692&page=3#post1178814

 

[skizzly]

hi mdluck,

i need the second to last date for all in tbl_05...

apologies for my mistake...

 

[skizzly]

hi mdluek,

sorry do not understand how to "The outer query needs to select from the inner query, not try to grab also from the table itself."... based on the query i put up, how do i do this... thanks for your patience....

 

[mdlueck]

i need the second to last date for all in tbl_05...

So you are expecting a GROUP BY operation, then SELECT the second to last from each unique GROUP... from a table with 5 entries each for George / Mary / Bert / Ernie, resulting in 4 records being selected?

 

[skizzly]

hi mdluek,

yes thats it,

just to note, each group will have variable number of entries, but yes this is what i am trying to achieve....

 

[mdlueck]

sorry do not understand how to "The outer query needs to select from the inner query, not try to grab also from the table itself."

A sub-query runs first, then the outer query may further refine the final result set.

Pseudo-code example:

SELECT TOP 1 [tr].[x], [tr].[y], [tr].[z]
FROM (SELECT TOP 2 [t].[x], [t].[y], [t].[z], [t].[datecol]
      FROM [table] AS [t]
      ORDER BY [t].[datecol] DESC
      ) AS [tr]
ORDER BY [tr].[datecol]

 

[jzwp22]

I would remove the TOP 1 from your query. This query should give you no more than two records per person (the two most recent per person or one in the case of Mic)

SELECT TOP 1 vwAttendances.ID, vwAttendances.SchName, vwAttendances.STARTDATE
FROM tbl_05 INNER JOIN vwAttendances ON tbl_05.ID = vwAttendances.ID
WHERE (((vwAttendances.STARTDATE) In (SELECT TOP 2 T.STARTDATE FROM vwAttendances T WHERE T.ID = vwAttendances.ID ORDER BY T.STARTDATE Desc)))
ORDER BY vwAttendances.STARTDATE

Now create an aggregate query (using the query above) that finds the minimum date for each person which should be the second to last date.

 

[mdlueck]

yes thats it,

just to note, each group will have variable number of entries, but yes this is what i am trying to achieve....

Each group may have a different number of items / that will not break the query.

I have done this sort of thing with SQL Server and its SQL dialect. I am not sure if Access has strong enough SQL to be able to handle this sort of thing.

GROUP BY will be your tool to use to have the query separate all of the records into each bucket.

GROUP BY Clause in Access
http://office.microsoft.com/en-us/access-help/group-by-clause-HA001231482.aspx

 

[skizzly]

hi jzwp22

thanks for your interest... so to solve my problem I have to create two queries? I will try as you suggest...

mdluek... your psuedo code looks like what i have done without joining the table.... am i wrong

 

[mdlueck]

your psuedo code looks like what i have done without joining the table.... am i wrong

It looked like your query, both the inner and outer, were both referencing the actual table.

Here the inner query is named [tr] (table reference) and is used by the outer query.