Solved

Get second MIN record

Posted on 1998-07-23
6
209 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 300 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
Independent Software Vendors: 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!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 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