Solved

Get second MIN record

Posted on 1998-07-23
6
210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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)

 
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

Suggested Solutions

Title # Comments Views Activity
Delete table fields 3 48
access vba 5 57
Ranking Students in a normalised Ms Access Database (Ms Access 2010) 7 65
VBA Ref Table, use ID#, then ref column 2 3 23
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

751 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