Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Get second MIN record

Posted on 1998-07-23
6
Medium Priority
?
216 Views
Last Modified: 2006-11-17
I'm using Access 97.  I have a database of requests for materials.  Each person has an ID number.  When a request is made, a record gets put into a request table.  The record contains the date of the request.  For one report, I need to get the first request for each person.  This is easy, I group on the ID number and take the MIN of the date field.

Now I need to determine the second request for each of the people who have requested more than once.  They may have requested up to 10 times, so I can't just use the MAX of the date.  Also, my report needs to show the number of people who submitted a second request listed by date.

I have over 50,000 requests from about 35,000 separate IDs.

How can I do this?
0
Comment
Question by:rstone
  • 2
  • 2
  • 2
6 Comments
 
LVL 1

Accepted Solution

by:
avli earned 1200 total points
ID: 1957099
For the report's data source, could you create a recordset based on the information needed (i.e. ID number, date of the request).
Sort the request in ascending order by the date.  For each ID number, go to the record following the MIN and that should contain the date for the second request.  [Note: To move from one record to the next, use the MoveFirst and MoveNext methods.]

For the report that shows the number of people who submitted a second request listed by date, you can use the same recordset as described above and count the request dates for each ID number.  This will then equal to the number of people who submitted a second request by date.  You'll also have the date of the request already.
 
0
 

Author Comment

by:rstone
ID: 1957100
So I can create a recordset with the ID and date, then sort it by ID, then within ID, sort by date.  That I can do with a query.  If I open the query with DAO, I can walk through the records and get the information.  How do I then create a report based on this information?

In other words, how do create the recordset that I want?  Counting the request dates isn't exactly what I want because some IDs may have requested 10 times.  For that ID, I want to know the date of the second request.

0
 
LVL 5

Expert Comment

by:tuvi
ID: 1957101
I don't really know your table structures. Anyway, here is my example. Assuming there is a separate table for Persons who is making request. Now, I have this Request table and call it "tblRequest".

tblRequest:

Field Name     Data Type
----------
RequestID      AutoNumber
PersonID       Long
RequestDate    Date

That's easy enough. PersonID links to your Person table.

Now, to solve your problems, I use 5 queries. First query is obvious: looking for the first request. I call it "qryFirstRequest"

qryFirstRequest:
---------------
Field:  PersonID     FirstRequestDate:RequestDate
Table:  tblRequest   tblRequest
Total:  GroupBy      Min
Sort:                Ascending
Show:   checked      checked

Next, based on this query, I create "qryFirstRequestID" to look for the RequestID. (This is necessary because what if a person request more than once in a single day. So the earliest must be the smaller RequestID). Here is the SQL statement in SQL window (easier):

SELECT Min(tblRequest.RequestID) AS FirstRequestID, tblRequest.PersonID, tblRequest.RequestDate
FROM tblRequest INNER JOIN qryFirstRequest ON (tblRequest.RequestDate = qryFirstRequest.FirstRequestDate) AND (tblRequest.PersonID = qryFirstRequest.PersonID)
GROUP BY tblRequest.PersonID, tblRequest.RequestDate;

Next step, I create "qryWithoutFirstRequest" (substract all those first request). Again here is the SQL statement:

SELECT tblRequest.RequestID, tblRequest.PersonID, tblRequest.RequestDate
FROM tblRequest
WHERE (((tblRequest.RequestID) Not In (Select FirstRequestID from qryFirstRequestID)))
ORDER BY tblRequest.RequestID;

Now we can have those second request date:

qrySecondRequest:
----------------
Field:  PersonID                 SecondRequestDate:RequestDate
Table:  qryWithoutFirstRequest   qryWithoutFirstRequest
Total:  GroupBy                  Min
Sort:                            Ascending
Show:   Checked                  Checked

Last touch, to have the RequestID, we need another query: "qrySecondRequestID"

SELECT Min(qryWithoutFirstRequest.RequestID) AS SecondRequestID, qryWithoutFirstRequest.PersonID, qryWithoutFirstRequest.RequestDate
FROM qryWithoutFirstRequest INNER JOIN qrySecondRequest ON (qryWithoutFirstRequest.RequestDate = qrySecondRequest.SecondRequestDate) AND (qryWithoutFirstRequest.PersonID = qrySecondRequest.PersonID)
GROUP BY qryWithoutFirstRequest.PersonID, qryWithoutFirstRequest.RequestDate
ORDER BY qryWithoutFirstRequest.RequestDate;
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 5

Expert Comment

by:tuvi
ID: 1957102
So all you need for your recordsets are two primary queries:

qryFirstRequestID and qrySecondRequestID
0
 

Author Comment

by:rstone
ID: 1957103
Thank you very much!

0
 
LVL 1

Expert Comment

by:avli
ID: 1957104
If you use a query, you could create a query based on the second query similar to tuvi's comment.  You could then use the set the recordsource for the report to the final query (or you could build a query internal to the report itself).  

On the other hand, if you decided to use a recordset instead, you could generate the recordsets on the Form_Open event procedure and assign the results of "walking through the recordset" to the textboxes in whichever report section you choose.

What I meant when I said to count the request dates was that this way you would know which IDs requested more than once based on the count.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question