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 28 2019, 01:51 PM
Post#21



Posts: 493
Joined: 26-June 08



Wow, what a cool learning experience. Eventually I'll even understand it! wink.gif

I got the first 2 queries fine, qryCompanyVINPoints and qryTopFiveVINPoints.

The last query gave me an error that said: Syntax error in JOIN Expression. then it highlights the word "ON" in the line: ON t1.CompanyID = c.CompanyID

This is so far over my head, I'm not sure how to fix it.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
whitechair
post Jun 28 2019, 02:01 PM
Post#22



Posts: 493
Joined: 26-June 08



Also, qryTopFiveVINPoints doesn't show the top 5. It shows this in the criteria section of this field: [q1].[CompanyID] & "|" & [q1].[VIN] shows this as a criteria:

CODE
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
)

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
cheekybuddha
post Jun 28 2019, 02:26 PM
Post#23


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


I had a feeling that the joins wouldn't be correct. Access insists on nesting each join within brackets and even after all these years I have never been able to get it right!! pullhair.gif

>> Also, qryTopFiveVINPoints doesn't show the top 5. It shows this in the criteria section of this field: [q1].[CompanyID] & "|" & [q1].[VIN] shows this as a criteria: <<
First we had better deal with this part!

Access represents SQL in the query builder very strangely, since it's not straightforward to represent subqueries visually.

What results do you get (if any)?

Are you able to post a sample db with just the tables involved with sample data (dummy data if necessary)?

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


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 28 2019, 02:29 PM
Post#24


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


I just spotted a typo:
CODE
WHERE q1.CompanyID & "|" & q.VIN IN (

should be:
CODE
WHERE q1.CompanyID & "|" & q1.VIN IN (
                            ^
                            |


qryTopFiveVINPoints:
CODE
SELECT
  q1.CompanyID,
  q1.VIN,
  q1.Time_Weight,
  q1.Total_Points
FROM qryCompanyVINPoints q1
WHERE q1.CompanyID & "|" & q1.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
;

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


Regards,

David Marten
Go to the top of the page
 
whitechair
post Jul 1 2019, 04:22 PM
Post#25



Posts: 493
Joined: 26-June 08



Thanks so much for the time you are putting to help me. I can't thank you enough.

I caught the typo, but I think I fixed it incorrectly. I had changed it to:
CODE
WHERE q2.CompanyID & "|" & q1.VIN IN (


Also, I need the Total_Points to be sorted Descending so that when we take the Top 5, it takes the highest score from Total_Points. So I adjusted the final SQL to look like this:
CODE
SELECT q1.CompanyID, q1.VIN, q1.Time_Weight, q1.Total_Points
FROM qryCompanyVINPoints AS q1
WHERE ((([q1].[CompanyID] & "|" & [q1].[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 DESC;

So I think that the seecond query is giving us the info we want, but it is not limiting it to the Top 5 Highest Total_Points. It shows 334 records. Here are some screen shots that hopefully will help.

Attached File(s)
Attached File  Query_qryTop5VINPoints_image2.PNG ( 11.8K )Number of downloads: 2
Attached File  Query_qryTop5VINPoints_image1.PNG ( 45.88K )Number of downloads: 4
 

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
cheekybuddha
post Jul 1 2019, 04:30 PM
Post#26


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


Are you able to post a db with the relevant tables containing [non-identifiable or dummy] data?

(Let me know if you need help with that.)

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


Regards,

David Marten
Go to the top of the page
 
whitechair
post Jul 3 2019, 08:29 AM
Post#27



Posts: 493
Joined: 26-June 08



I will attempt to do this today. It will take me a little while... thanks so much for the help. The entire database is ready to launch except for this one issue.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
cheekybuddha
post Jul 3 2019, 08:37 AM
Post#28


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


No problem, Jeff! I'm actually on a train to Amsterdam right now and won't be back home till Friday, so may not be able to help much before then. Trying to use Access via my phone is a very slow process! Others here may will look in in the meanwhile.

thumbup.gif

d

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


Regards,

David Marten
Go to the top of the page
 
whitechair
post Jul 3 2019, 09:08 AM
Post#29



Posts: 493
Joined: 26-June 08



I think this will work. I tried to only strip what wasn't necessary.

The form frmImportInspections is the main form. The button that says Send Email is the trigger for running the report.

I just need to be able to limit the rptHighContributors to the top 5 Total_point VINs so that it doesn't turn into a 50 page report.

Let me know what you think.
Attached File(s)
Attached File  Inspection_Analysis_2.zip ( 980.19K )Number of downloads: 8
 

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
whitechair
post Jul 9 2019, 10:27 AM
Post#30



Posts: 493
Joined: 26-June 08



OK, I ended up solving it by starting from scratch. That always seems to help, but it's a hard decision to make.

I found a way to use VBA to update the SQL of a saved query. That way I could assign the correct DOT number to the query that my Top 5 query was based on before I ran it.

Here is the code for reference:
CODE
Dim strSQL As String
strSQL = "SELECT TOP 5 qryHighOffenders1.CompanyName, qryHighOffenders1.DOT, qryHighOffenders1.VIN, Sum(qryHighOffenders1.Total_Points) AS SumOfTotal_Points " & _
"From qryHighOffenders1 " & _
"GROUP BY qryHighOffenders1.CompanyName, qryHighOffenders1.DOT, qryHighOffenders1.VIN " & _
"HAVING (((qryHighOffenders1.DOT) = " & vDOT & ") And ((Sum(qryHighOffenders1.Total_Points)) Is Not Null)) " & _
"ORDER BY Sum(qryHighOffenders1.Total_Points) DESC;"


Works like a charm. Thanks for everyone's help on this.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 02:09 PM