UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Top 5 Query In Report, Access 2016    
 
   
whitechair
post Jun 26 2019, 07:46 AM
Post#1



Posts: 491
Joined: 26-June 08



OK, I hope I can explain this well enough!

I have a report that I would like to show the top 5 VIN numbers contributing to a total score. Each VIN would have it's own page, so "Can Grow" = false option isn't really available. When I look at the recordset of the report, it shows the top five VIN numbers from any Company in the table. I would like to filter it for a specific company, so the end result is the top 5 VIN numbers with the highest scores for a particular company. If I manually enter the DOT number in the criteria section and run the query, I get the exact result I want. So I delete the DOT criteria in the query and save the report.

Then I use the following code to apply the same DOT number to the filter, (I did verify that the vDOT is correct) but the report is showing 0 results.
CODE
    vFileName2 = "C:\users\" & vUserName & "\Desktop\rptHighOffenders.pdf"
DoCmd.OpenReport "rptHighOffenders", acViewPreview, , "[DOT] =" & vDOT
DoCmd.OutputTo acReport, "rptHighOffenders", acFormatPDF, vFileName2, False
DoCmd.Close acReport, "rptHighOffenders"


I believe it's because the original query's top 5 doesn't include this DOT number since non of it's VIN numbers have scores higher than the other companies included. But I can't figure out why when I apply the criteria directly into the query it's sorts only for the Top 5 of that company, but when I apply the filter it only filters the existing records. How do I rewrite the filter or query in order to get the data filtered for the just DOT I need?

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
BruceM
post Jun 26 2019, 09:23 AM
Post#2


UtterAccess VIP
Posts: 7,921
Joined: 24-May 10
From: Downeast Maine


It would help to know the nature of the DOT number. Is it text? If so:

"[DOT] = ' " & vDOT & " ' " (spaces between quotes added for clarity, but need to be removed in practice).

You do not describe vDot. Is it declared somewhere, and a value assigned?

It may be helpful to assign the Where Condition to a variable, which can be tested with Debug.Print:
CODE
Dim strWhere As String

strWhere = "[DOT] =" & vDOT

Debug.Print strWhere

DoCmd.OpenReport "rptHighOffenders", acViewPreview, , strWhere

After running the code, press Ctrl + G to view the Debug.Print result (the value of the strWhere variable) in the Immediate code window.
Go to the top of the page
 
whitechair
post Jun 26 2019, 10:24 AM
Post#3



Posts: 491
Joined: 26-June 08



The vDOT is a number field. So I think the formatting is correct. The same variable is used in other parts of the code to filter reports and it works fine.

I think the issue is understanding how a filter works. It is only filtering the records that are currently there. The company I need to filter for doesn't exsit in the top 5 of ALL companies so the vDOT will not work. I think I need to apply a recordset to the report instead. So that the whole recordset is produced each time.

Something like here:
https://bytes.com/topic/access/answers/7250...t-access-report


--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
whitechair
post Jun 26 2019, 10:42 AM
Post#4



Posts: 491
Joined: 26-June 08



Somthing like this:
CODE
Private Sub Report_Open(Cancel As Integer)
Dim vDOT As Long
    vDOT = Forms!frmImportInspections.lstCompanyName.Column(6)
Dim vSQL As String

vSQL = "SELECT TOP 5 qryHighOffenders4.CompanyName, qryHighOffenders4.DOT, qryHighOffenders4.VIN, qryHighOffenders4.[Insp Date], qryHighOffenders4.State, qryHighOffenders4.BASIC, qryHighOffenders4.Description, qryHighOffenders4.[Out of Service], qryHighOffenders4.[Violation Severity Weight], qryHighOffenders4.Time_Weight, qryHighOffenders4.Total_Points, IIf([Unit]=""1"",""Truck"",IIf([Unit]=""D"",""Driver"",""Trailer"")) AS UnitType, IIf([Out Of Service]=-1,""Yes"",""No"") AS OOS, tblImages.Image, tblTractorWAMI.TractorMake, tblVINYear.YearOfVIN " & _
    "FROM tblVINYear INNER JOIN (tblTractorWAMI INNER JOIN (tblImages INNER JOIN qryHighOffenders4 ON tblImages.ID = qryHighOffenders4.Image) ON tblTractorWAMI.WAMI = qryHighOffenders4.TractorWAMI) ON tblVINYear.Code = qryHighOffenders4.YearCode " & _
    "WHERE (((qryHighOffenders4.DOT) = " & vDOT & ")) " & _
    "ORDER BY qryHighOffenders4.Total_Points DESC;"

   Dim vConnection As ADODB.Connection
   Dim vRecSet As ADODB.Recordset

   'Use the ADO connection that Access uses
   Set vConnection = CurrentProject.AccessConnection

   'Create an instance of the ADO Recordset class and open it
   Set vRecSet = New ADODB.Recordset
   vRecSet.Open vSQL, vConnection

   'Set the report's Recordset property to the ADO recordset
   Set Reports!rptHighOffenders.Recordset = vRecSet
   Set vRecSet = Nothing
   Set vConnection = Nothing

End Sub

Although this is giving me an error that says: This feature only available in ADP. SO I think I need to switch it over but I'm not sure how to do that... yet! wink.gif

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
BruceM
post Jun 26 2019, 10:45 AM
Post#5


UtterAccess VIP
Posts: 7,921
Joined: 24-May 10
From: Downeast Maine


From your description I understood that the query returns the TOP 5 VINs for each company, and that the DOT value identifies the company. Apparently this is not so.

I'm not sure if you are explaining to me how a filter (actually, the OpenReport Where Condition) works, or if you are stating what you have learned. In any case, I don't understand what you mean by "apply a recordset to the report instead". A report is based on a recordset, and your recordset does not contain the value you are seeking, so I don't understand what you are thinking of doing differently.
Go to the top of the page
 
whitechair
post Jun 26 2019, 11:51 AM
Post#6



Posts: 491
Joined: 26-June 08



So, I knew this would be hard to explain, mostly because I understand so little of it and am still learning the right vocabulary. Thank you for your patience!

There is a key part of this that is hard to explain, but I'll try again.

The Query is sorted descending by the total points assigned to a VIN number. The higher the points, the higher on the list. This isn't sorted by DOT, it's just any VIN number in the system. Like this:
CODE
Company     VIN     Score
Comp A      4567     52
Comp B      9876     46
Comp A      6352     41
Comp D     1478     23
Comp D     6352     18
This keeps going for 200 records for all companies...


So if I want to filter for Comp H, it's so far down the list it doesn't show up in the Top 5, so I get 0 records. If I add Comp H to the criteria in the query, it shows this:
CODE
Company     VIN     Score
Comp H      6633     11
Comp H      3355     9
Comp H      2552     7
Comp H      1452      6
Comp H      2698      5


This is what I want it to look like. I'm struggling to get it to work that way.

This post has been edited by whitechair: Jun 26 2019, 11:55 AM

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
cheekybuddha
post Jun 26 2019, 11:59 AM
Post#7


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Have you tried a Top N per group query?

--------------------


Regards,

David Marten
Go to the top of the page
 
whitechair
post Jun 26 2019, 12:24 PM
Post#8



Posts: 491
Joined: 26-June 08



No, but that looks super cool. I would just have no idea on how to incorporate that into my current query.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
cheekybuddha
post Jun 27 2019, 06:39 AM
Post#9


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Can you also post the SQL of qryHighOffenders4

--------------------


Regards,

David Marten
Go to the top of the page
 
whitechair
post Jun 27 2019, 07:05 AM
Post#10



Posts: 491
Joined: 26-June 08



Here is the SQL for qryHighOffenders4
CODE
SELECT qryHighOffenders1.CompanyName, qryHighOffenders1.DOT, qryHighOffenders1.VIN, qryHighOffenders1.[Insp Date], qryHighOffenders1.State, qryHighOffenders1.BASIC, qryHighOffenders1.Description, qryHighOffenders1.[Out of Service], qryHighOffenders1.[Violation Severity Weight], qryHighOffenders1.Time_Weight, qryHighOffenders1.Total_Points, qryHighOffenders1.Unit, IIf(IsNull([Unit]),4,IIf([Out of Service]=-1,2,3)) AS [Image], Left([qryHighOffenders1].[VIN],3) AS TractorWAMI, Mid([qryHighOffenders1].[VIN],10,1) AS YearCode
FROM qryHighOffenders2 INNER JOIN qryHighOffenders1 ON qryHighOffenders2.VIN = qryHighOffenders1.VIN
ORDER BY qryHighOffenders1.VIN, qryHighOffenders1.BASIC;

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
cheekybuddha
post Jun 27 2019, 07:14 AM
Post#11


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


And also for qryHighOffenders1 and qryHighOffenders2 as well please!

(And also any queries that might be contained within those too!)

--------------------


Regards,

David Marten
Go to the top of the page
 
whitechair
post Jun 27 2019, 08:37 AM
Post#12



Posts: 491
Joined: 26-June 08



OK, I had to redo all of the queries because I messed them up so much last night. In doing so I realized there were some fields in there that I don't need. Same problem with the Filter for DOT and Top 5 issue though. Here are the progession of queries:

qryHighOffenders1
CODE
SELECT Company.CompanyName, Company.DOT, Table1.VIN, Table1.[Insp Date], Table1.State, Table1.BASIC, Table1.Description, Table1.[Out of Service], Table1.[Violation Severity Weight], IIf(Date()-180<[Insp Date],3,IIf(Date()-365<[Insp Date],2,1)) AS Time_Weight, Table1.Unit, [Violation Severity Weight]*[Time_Weight] AS Total_Points
FROM Company INNER JOIN Table1 ON Company.ID = Table1.CompanyID
WHERE (((Table1.[Insp Date])>Date()-730));



qryHighOffenders4
CODE
SELECT qryHighOffenders1.CompanyName, qryHighOffenders1.DOT, qryHighOffenders1.VIN, qryHighOffenders1.[Insp Date], qryHighOffenders1.State, qryHighOffenders1.BASIC, qryHighOffenders1.Description, qryHighOffenders1.[Out of Service], qryHighOffenders1.[Violation Severity Weight], qryHighOffenders1.Time_Weight, qryHighOffenders1.Total_Points, qryHighOffenders1.Unit, IIf(IsNull(qryHighOffenders1.[Unit]),4,IIf([Out of Service]=-1,2,3)) AS [Image], Left([qryHighOffenders1].[VIN],3) AS TractorWAMI, Mid([qryHighOffenders1].[VIN],10,1) AS YearCode
FROM qryHighOffenders1
ORDER BY qryHighOffenders1.VIN, qryHighOffenders1.BASIC;


I believe you do not need qryHighOffenders2 and qryHighOffenders3 because there were unnecessary references to those queries in previous versions of qryHighOffenders4. Let me know what you think.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
cheekybuddha
post Jun 27 2019, 12:01 PM
Post#13


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Can I clarify a couple of things:

The 'Score' you refer to in Post#6 is Table1.DOT ?

Or is it qryHighOffenders1.Total_Points ?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 27 2019, 12:08 PM
Post#14


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Also, is Table1.VIN a unique field?

And are the combinations of Table1.CompanyID and Table1.VIN unique?

Please specify the Primary key fields of all the tables involved:
tblVINYear
tblTractorWAMI
tblImages
Company
Table1

--------------------


Regards,

David Marten
Go to the top of the page
 
whitechair
post Jun 27 2019, 02:42 PM
Post#15



Posts: 491
Joined: 26-June 08



Table1.VIN is not unique

You could have Table1.CompaniID with multiple Table1.VIN

(Table) Table1 Primary Key = ID
(Table) Company Primary Key = ID ==> CompanyID in Table1

I hope this is what you are looking for.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
cheekybuddha
post Jun 27 2019, 03:09 PM
Post#16


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Can more than one company have the same VIN?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 27 2019, 03:10 PM
Post#17


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Also, what about the score (see my question in Post#13)

--------------------


Regards,

David Marten
Go to the top of the page
 
whitechair
post Jun 28 2019, 03:12 AM
Post#18



Posts: 491
Joined: 26-June 08



The total is based on qryHighOffenders1.Total_Points

And yes it is possible that a Company could have the same VIN as another. It would only happen if they physically sold the truck to another company and that company got an inspection on that truck and it happens to be a company we insure. So it doesn’t happen often, but it does happen.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
BruceM
post Jun 28 2019, 07:51 AM
Post#19


UtterAccess VIP
Posts: 7,921
Joined: 24-May 10
From: Downeast Maine


Regarding the VIN, I expect you could exclude policies that have been discontinued, by way of an EndDate or Active field. Just a general point.
Go to the top of the page
 
cheekybuddha
post Jun 28 2019, 09:14 AM
Post#20


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


I think you need to turn this inside out!

First, replicate the Company, VIN, Score for the last 2 years as per Post#6:
CODE
SELECT
  t.CompanyID,
  t.VIN,
  IIf(
    Date() - 180 < t.[Insp Date],
    3,
    IIf(
      Date() - 365 < t.[Insp Date],
      2,
      1
    )
  ) AS Time_Weight,
  t.[Violation Severity Weight] * Time_Weight AS Total_Points
FROM Table1 t
WHERE t.[Insp Date] > Date() - 730
;

Perhaps, for ease, save this query as qryCompanyVINPoints

Then, use that to get the Top 5 per Company as per Allen Browne. Since uniqueness is determined by the combination of CompnayID and VIN, concatenate the 2 together to make the subquery easier:
CODE
SELECT
  q1.CompanyID,
  q1.VIN,
  q1.Time_Weight,
  q1.Total_Points
FROM qryCompanyVINPoints q1
WHERE q1.CompanyID & "|" & q.VIN IN (
  SELECT TOP 5
    q2.CompanyID & "|" & q2.VIN
  FROM qryCompanyVINPoints q2                              
  WHERE q2.CompanyID = q1.CompanyID
    AND q2.VIN = q1.VIN
  ORDER BY q2.Total_Points DESC
)
ORDER BY
  q1.CompanyID,
  q1.Total_Points
;

Save as qryTopFiveVINPoints

Once you have that, you can join the other details on CompanyID and VIN:
CODE
SELECT
  c.CompanyName,
  c.DOT,
  t1.VIN,
  t1.[Insp Date],
  t1.State,
  t1.BASIC,
  t1.Description,
  t1.[Out of Service],
  t1.[Violation Severity Weight],
  q3.Time_Weight,
  t1.Unit,
  q3.Total_Points,
  IIf(
    IsNull(t1.[Unit]),
    4,
    IIf(
      t1.[Out of Service] = -1,
      2,
      3
    )
  ) AS [Image],
  Left(t1.VIN,3) AS TractorWAMI,
  Mid(t1.VIN,10,1) AS YearCode,
  IIf(
    t1.Unit = '1',
    'Truck',
    IIf(
      t1.Unit = 'D',
      'Driver',
      'Trailer'
    )
  ) AS UnitType,
  IIf(
    t1.[Out Of Service] = -1,
    'Yes',
    'No'
  ) AS OOS
FROM Table1 t1
INNER JOIN (
  qryTopFiveVINPoints q3
  INNER JOIN (Company c
          ON t1.CompanyID = c.CompanyID
  )
        ON t1.CompanyID = q3.CompanyID
       AND t1.VIN = q3.VIN
);


Then join the other tables: tblVINYear, tblTractorWAMI, tblImages.

Of course this is thoroughly untested!!!

hth,,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 12:15 PM