Solved

Get second MIN record

Posted on 1998-07-23
6
205 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
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.

762 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