Audit Sample Select Top X (Variable) 1

[MKH2001]

I have been asked to create a list of sample data for auditing on a weekly basis.

Random 5 Roads per district per week... Fine and done.
Pulled from tbl_Insp based on input date criteria input on a form and a "Select Top 5" query run in individual make table and append tables to give me a resultant tbl_Audits_stp1 table.

With a number of assets on those roads to be dependant on what has been attended with some pretty annoying calculations

I have therefore pulled this into another table with a numeric field of "ActualAttended" (pulled from the tbl_Insp) and a numeric field of "AuditQuant" based on calculation of count (each record in tbl_insp refers to an individual asset).
This is step is actually 2 queries as the number of iif statements in query expression is so large I have to do 2 and then do a final combination of the 2 make tables into a final
tbl_Audits_stp2.

So what I get is:

Roadname District ActualAttended AuditQuant
Liam Lane West 8 5
Francis Corner West 20 5.1
Margery Close West 200 20
Fransisco Drive West 10 5
Bartholomew Ave West 1 1
Trident Way South 7 5
Comet Close South 1020 102
Furbies Rock South 25 12.5
Flibble Fore South 33 11.33
Flintstone Road South 22 10

What I need to do now is get a Select Top query to accept a variable from a table but understand that can't be done in normal query design sql.

Instead have to some how create a means of calling the process in a form function and somehow opening the table
and looping through the enteries and somehow selecting the number of asset to be audited(again randomly from all those attended in the tbl_Insp)and input that in to a table.
Obviously the RoadName and District need to match (Can be same roadnames in other district)and the attended gullies need to be between the same date criteria as used for the same pull of "road samples" and total attended counts.

Another issue the above table example demonstrates is that of decimal places in the AuditQuant field. This must always be rounded UP so that, for example, Flibble Fore should have 12 assets selected from attended for audit.

Any assistance would be greatly appreciated, maybe I can get this done before the Xmas hols that I can enjoy them =)

 

[MajP]

you may want to look at thread701-1631889. See if that helps for randomly selecting TopX from a table and importing into another table.

 

[MKH2001]

Random selection isnt the issue so much as a dynamic Select Top variable.

IE A field on a form is not going to be sufficient.
The selection for the Select top needs to be able to be gleaned from a table for each record in the tbl_audits_stp2 and value for each records "AuditQuant" entered as the Select top for each and every row before appending the result to a table.

Its how to loop through each record and select Select top based on that AuditQuant for each road.

 

[MajP]

1)Create a table to store the recordIDs being audited.
tblAuditRecords
2)I assume you have some table with data and each record has some kind of primary key
I called this tbldata for demo purposes. I assume the Top x records by date are the ones to audit. If not just change the query

Basically it reads through the table that has the amount to audit field. Figures out how many to audit and then runs append queries. The loop produces this

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 5 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 5 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Liam Lane' And tblData.district = 'West' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 5 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 5 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Francis Corner' And tblData.district = 'West' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 20 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 20 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Margery Close' And tblData.district = 'West' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 5 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 5 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Fransisco Drive' And tblData.district = 'West' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 1 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 1 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Bartholomew Ave' And tblData.district = 'West' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 5 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 5 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Trident Way' And tblData.district = 'South' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 102 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 102 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Comet Close' And tblData.district = 'South' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 12 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 12 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Furbies Rock' And tblData.district = 'South' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 11 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 11 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Flibble Fore' And tblData.district = 'South' ORDER BY tbldata.dtmDate

INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits )  SELECT TOP 10 tblData.PrimaryKey, #12/23/2010# AS dtmAuditDate, 10 AS AuditQuan FROM tblData WHERE tblData.RoadName = 'Flintstone Road' And tblData.district = 'South' ORDER BY tbldata.dtmDate

Public Sub AppendAuditIDs()
  Const dataTable = "tblData"
  Const dataPK = "PrimaryKey"
  Const auditAmountTable = "tblAuditAmounts"
  Const auditRecordsTable = "tblAuditRecords"
  
  Dim rsAuditQuant As DAO.Recordset
  Dim strSql As String
  Dim RoadName As String
  Dim auditQuant As Integer
  Dim district As String
  
  strSql = "Select RoadName, District, auditQuant FROM " & auditAmountTable
  Set rsAuditQuant = CurrentDb.OpenRecordset(strSql)
  Do While Not rsAuditQuant.EOF
    RoadName = Nz(rsAuditQuant!RoadName, "")
    district = Nz(rsAuditQuant!district, "")
    auditQuant = Nz(rsAuditQuant!auditQuant, 0)
    auditQuant = roundUp(auditQuant)
    strSql = getInsertSql(RoadName, district, auditQuant)
    Debug.Print strSql
    CurrentDb.Execute (strSql)
    rsAuditQuant.MoveNext
  Loop
  rsAuditQuant.Close
End Sub

Public Function getInsertSql(RoadName As String, district As String, auditQuant As Integer) As String
 Dim strSql As String
 If RoadName <> "" And district <> "" And auditQuant <> 0 Then
   strSql = "INSERT INTO tblAuditRecords ( dataID, dtmAuditDate, requiredAudits ) "
   strSql = strSql & " SELECT TOP " & auditQuant & " tblData.PrimaryKey, #" & Date & "# AS dtmAuditDate, "
   strSql = strSql & auditQuant & " AS AuditQuan FROM tblData "
   strSql = strSql & "WHERE tblData.RoadName = " & sqlTxt(RoadName) & " And tblData.district = " & sqlTxt(district)
   strSql = strSql & " ORDER BY tbldata.dtmDate"
 End If
 getInsertSql = strSql
End Function

Public Sub test()
  Dim strSql As String
  strSql = getInsertSql("Bartholomew Ave", "West", 5)
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub
Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function

Public Function roundUp(ByVal dblQuant As Double) As Integer
  If dblQuant = Fix(dblQuant) Then
    roundUp = CInt(dblQuant)
  Else
    roundUp = Fix(dblQuant) + 1
  End If
End Function

The result is a table with the records that need auditing.

 

[dhookom]

You may be able to create a ranking field in your query that numbers the records by group (RoadName?). Then filter the query based on the Rank value < or > than a number/field within the query.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane's suggestion may be simpler (depending on your table design). You probably still need the round up function, but you can use that in the query
select ... roundUp(auditQuant) as rndUpAuditQuant ...
If you do not know how to do a ranking query you can google that.
If you cannot figure out the query, the above code did give the proper results.

 

[MajP]

I tried Duane's suggestion and it is pretty easy.
qryRank

SELECT tblData.PrimaryKey, tblData.RoadName, tblData.District, tblData.dtmDate, (select count(*) from tblData as A where tblData.RoadName = A.roadName and tblData.District = A.district and TblData.dtmdate > A.dtmdate) AS Rank
FROM tblData
ORDER BY tblData.RoadName, tblData.District, tblData.dtmDate;

and then the output (which you may use for an insert to store the data)

SELECT qryRank.PrimaryKey, qryRank.Rank
FROM tblAuditAmounts INNER JOIN qryRank ON (tblAuditAmounts.District = qryRank.District) AND (tblAuditAmounts.Roadname = qryRank.RoadName)
WHERE (((qryRank.Rank)<roundUP([auditQuant])));

 

[MKH2001]

That is awesome.

MajP suggestion is more along the lines I was originally envisaging, but will look into both to see what seems more robust method.

Many thanks. And Merry Xmas.
May the new year bring many more many to many relationships =)

 

[MKH2001]

Nothing wrong with the original code using majp's format.

However took alot of struggling with tweaking code syntax mainly to exactly to suit my table structure and source of variable feeds and again getting enough data extracted to give me the exact results required but finally got it working )

Many thanks MajP