Solved

Get second MIN record

Posted on 1998-07-23
6
206 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now