Group total Based on last 80 Records ?

[Dyer]

I have an Access table based on team defect counts.
I need to sum the number of defects by team for the last or
most recent 80 records. It would be acceptable to sum every 80 records starting at the most recent 80 records.

Can anyone help ?

Thank You,

 

[cmmrfrds]

What do you have to work with? Unique ID (auto number)? timestamp? Date? Other break down?

 

[zerosandones]

The only assumptions I'll make here are that, when you say "last 80 records", you want the last 80 records irrespective of the Team. Secondly, I'll assume that the basis for determining which records are the most recent is date and time ... I'll use a field named "DefectDate".

I've called the table "tblDefects" with fields as follows:

Id (unique record identifier)
Team
DefectDate
DefectCount (the number of defects recorded for that team on that defect date)

To sum the total number of defects for each team, you first need to write a query to select the last 80 records ... lets call it "qryLast80".

SELECT TOP 80 tblDefects.Team, tblDefects.DefectCount
FROM tblDefects
ORDER BY tblDefects.DefectDate DESC;

The final step sums the total defects for each team:

SELECT qryLast80.Team, Sum(qryLast80.DefectCount) AS TeamDefects
FROM qryLast80
GROUP BY qryLast80.Team;

Hope this helps.

00001111s

 

[Dyer]

Thank you very much for your help !

I would like to select the most recent 80 records by date and time for each team number in my table. I was not specific enough in my original post.

I will run the query on a daily basis to determine which teams have
over X amount of defects in the last 80 samples so we can increase the amount
of inspections for the problem teams.

 

[zerosandones]

Sorry I took so long to reply ... I was away for a few days.

Okay, I understand your new dilemna ... the way to solve this one is a little bit more complicated since the "TOP n" SQL qualifier cannot be "sub-assigned" to a GROUP ... it's assigned to the entire recordset.

So, one method of solving this is to create an exact copy of your tblDefects table, let's call it tblDefectReport. This table will be used to add the "TOP n" records for each team. Your final report will be based upon tblDefectsReport rather than tblDefects.

So, with the additional table now in place, all you need is one procedure to do it all:

Public Function gfnLastnRecords()
' Process most recent n records for each team

On Error GoTo Err_gfnLastnRecords

Dim dbs As Database, rst As Recordset, rst2 As Recordset, rst3 As Recordset
Dim strSQL As String
Dim strTeam As String

' Initialize target table
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblDefectsReport"

' Return reference to current database
Set dbs = CurrentDb
strSQL = "SELECT DISTINCT Team FROM tblDefects"
' Get distinct list of teams
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
rst.MoveFirst

' Open target table for defect reporting
Set rst3 = dbs.OpenRecordset("tblDefectsReport&quot

Do Until rst.EOF ' Loop once for each team
With rst
strTeam = rst!Team
strSQL = "SELECT TOP 80 * FROM tblDefects WHERE Team = '" & strTeam & "'"
' Get last n records for current team
Set rst2 = dbs.OpenRecordset(strSQL)
rst2.MoveLast
rst2.MoveFirst

' Loop once for each top 80 record per team
Do Until rst2.EOF
With rst2
With rst3
.AddNew ' Add new record
!Team = rst2!Team ' Add data
!DefectDate = rst2!DefectDate
!DefectCount = rst2!DefectCount
.Update ' Save changes.
End With
End With
rst2.MoveNext
Loop
End With
rst.MoveNext
Loop

Exit_gfnLastnRecords:
DoCmd.SetWarnings True
rst.Close
rst2.Close
rst3.Close
Set dbs = Nothing
Exit Function

Err_gfnLastnRecords:
MsgBox Err.Description
Resume Exit_gfnLastnRecords

End Function


Hope this helps ...

00001111s

 

[r937]

>> the "TOP n" SQL qualifier cannot
>> be "sub-assigned" to a GROUP

actually, yes, it can

select teamname
     , defectdate
     , otherstuff
  from defectstable xx
 where defectdate in
       ( select top 80
                defectdate
           from defectstable
          where teamname = xx.teamname 
       order by defectdate descending )

this is called a correlated subquery

each defectdate is tested to see if it's in the top 80 dates for its group, where its group is all the rows that have the same teamname -- change this to test on primary key for better performance

rudy

 

[zerosandones]

Good idea Rudy ... I had tried this too, but, unfortunately it does not correctly apply the TOP n value to the recordset results.

Given tblDefects records:

Team DefectDate DefectCount
A 1/1/02 1
A 1/1/02 1
A 1/2/02 1
B 1/1/02 1
B 1/2/02 1
B 1/3/02 1

In running the coorelated subquery, using a TOP 2 qualifier, Team B correctly returns 1/3/02 and 1/2/02. However, since Team A has two records each having a valid 1/1/02 date, the outer query passes all three records.

Nice try ... that's why I settled on a procedure.

00001111s

 

[r937]

dear 00111s (however many there are of each)

your example is interesting, but i think somewhat simplistic

in every example i've worked on over the years, the "top" concept always includes ties

in dyer's case, the objective is to get a count of defects in the last X days and here you are throwing some of them away because there are two rows from the Xth day???

no, i would say that my sql does apply the top concept properly, and your interpretation unfortunately misunderstands the requirement

no offence, though


rudy

 

[r937]

much clearer example: get the students who have the top 3 grades in each class

suppose some class has 4 students with an A+

which one you gonna exclude?

count all the defects in the last 80 days does not mean get just 80 records, it means get all records where the date is one of the last 80 dates

and if it did mean get just 80 records, then this would imply a unique constraint on date within group, which your example violates...

rudy

 

[zerosandones]

Hi Rudy,

Your reasoning is sound ... I do find it interesting though that your experience has typically allowed you the ability to JOIN via a UNIQUE indentifier.

During the many years I've worked with SQL in real-world business environments, "date" fields are typically NOT part of any primary key or unique index and this is what I believe our author has to deal with ... maybe he'll clarify his environment for us.

Date-qualified records, when TOP is applied and the date field is used to sort the recordset, will always present this type of problem unless, as you stated, you're fortunate enought to have the date field plus the grouped field (in this case "Team&quot form a unique index.

I nonetheless like your reasoning and logic Rudy ... good talking to you.

00001111s

 

[Dyer]

All,

I tried the TOP 80 on a date field today and as you said I got different numbers
of records due to ties on the last day etc. I used the unique ID field to sort the entire
table and ran an additonal query to pick out the most recent 80 records for
a team. The unique ID worked very well.

The only issue is I hate to run a query for all 80 teams. The team numbers
can also change at times which causes some additional maintenance.
My final query grouped by team added all the defect types field for the 80 samples up
and shows me the teams with > X defects in the last 80 or<
80 samples. I just hate to copy all those query's. Will your approach still work
00001111s based on this info ? We will run the query each day after we input the prev. days data and the query will show us which teams need 100% inspection.

Thanks for the great input !
Dyer

 

[zerosandones]

Hi Dyer,

If I understand your question correctly, I have two suggestions, one being to go with Rudy's solution, the other with the subroutine I provided.

We know that your tblDefects table currently contains Team, DefectDate and DefectCount.

Next, can we confirm that you can or cannot have multiple records for a particular team on a given date e.g. more than one record for Team A on 1/1/02 ... or, is the combination of Team and DefectDate a primary key (unique index). I think you said in your last post that duplicates can exist, but, I need your confirmation.

If Team and DefectDate form a unique index, then Rudy's SQL works perfectly i.e.

SELECT Team, DefectDate, DefectCount
FROM tblDefect AS d
WHERE DefectDate In
(SELECT TOP 80 DefectDate
FROM tblDefect
WHERE Team = d.Team
ORDER BY DefectDate DESC);

If duplicates can exist, then you can use an additional unique identifier (I suggest an autonumber field ... let's call it &quot;Id&quot to arbitrate. The one catch is this ... it is imperative that the value of &quot;Id&quot; be sequenced in the same order as their associated DefectDate values i.e. sorting tblDefect records in ascending order by Id needs to also result in the records being in chronological order by DefectDate. If Id order matches DefectDate order, the following SQL will work nicely:

SELECT Team, DefectDate, DefectCount
FROM tblDefect AS d
WHERE Id In
(SELECT TOP 80 Id
FROM tblDefect
WHERE Team = d.Team
ORDER BY Id DESC);

If, however, more than one record can exist for the same Team on a given day AND you cannot guarantee that the value for Id will sequentially match that of the DefectDate value, you should use the procedure I previously supplied.

The good news is that, irrespective of which way you go, all three solutions are dynamic in that they will accomodate changes in Team members etc.

Finally, if either of the two SQL examples work well for you, you can further enhance them to dynamically filter by DefectDate and &quot;team&quot; DefectCount as follows ... first a modified version of the first SQL:

SELECT d.Team, Sum(d.DefectCount) AS [Team Defects]
FROM tblDefect AS d
WHERE (((d.DefectDate) In (SELECT TOP 80 DefectDate
FROM tblDefect
WHERE Team = d.Team
AND DefectDate > [Defect date later than]
ORDER BY DefectDate DESC)))
GROUP BY d.Team
HAVING (((Sum(d.DefectCount))>[Team defects greater than]));

Next, a modified version of the second SQL:

SELECT d.Team, Sum(d.DefectCount) AS [Team Defects]
FROM tblDefect AS d
WHERE (((d.Id) In (SELECT TOP 80 id
FROM tblDefect
WHERE Team = d.Team
AND DefectDate > [Defect date later than]
ORDER BY id DESC)))
GROUP BY d.Team
HAVING (((Sum(d.DefectCount))>[Team defects greater than]));

And lest we forget, if you need to resort to the procedure I supplied you, in which the results are appended into a target table named tblDefectReport, modify the second &quot;strSQL =&quot; statement to read:

strSQL = &quot;SELECT TOP 80 * FROM tblDefects WHERE Team = '&quot; & strTeam & &quot;' ORDER BY DefectDate DESC&quot;

When you run this function, the most recent 80 records for each team will be appended into tblDefectReport. Now you simply need to run one final SQL to summarize and filter your data e.g.

SELECT tblDefectReport.Team, Sum(tblDefectReport.DefectCount) AS SumOfDefectCount
FROM tblDefectReport
WHERE (((tblDefectReport.DefectDate)>[Defect date greater than]))
GROUP BY tblDefectReport.Team
HAVING (((Sum(tblDefectReport.DefectCount))>[Team defects greater than]));

Hope this does it ... good luck !

00001111s

 

[Dyer]

Looks Great !

Thanks,