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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using A Top N Query As The Basis For Another Group Query Then Run A Report, Access 2013    
 
   
GoodyGoody
post Jan 18 2020, 07:20 PM
Post#1



Posts: 1
Joined: 18-January 20



Hi,

I have been using Access Eorld Forums but for some reason am getting constant 'Database Error' page when I try and post.

Both the SQL statements below work on their own BUT what I need to do is have the second query use the results of the first as input and then have the report run off the results of the second query which is summarising and sorting the data. The code is below and what is basically happening is that the report is just running the second query and ignoring the first. Can someone tell me what I am doing wrong?

CODE
DoCmd.SetWarnings False  'This avoids confusing messages to the user that 'n' records will be being updated (or not!)

    Set db = CurrentDb()
    strSQL = _
         "SELECT * FROM qrySeriesPositionOverallIndividual AS X " _
       & "WHERE (((X.RaceEventRunnerID) In (SELECT TOP " _
       & N _
       & " RaceEventRunnerID FROM qrySeriesPositionOverallIndividual AS Y WHERE " _
       & "Y.RaceRunner = X.RaceRunner " _
       & "ORDER by Y.RaceCategoryPosition ASC, Y.RaceEvent))) " _
       & "ORDER BY X.RaceRunner, X.RaceCategoryPosition ASC;"
      
    Set rs1 = fDAOGenericRst(strSQL)
    rs1.MoveLast  'this will "populate the recordset"
    
    'Now run the Summary query to aggregate the results based on Sum(RaceCategoryPosition) for a Racerunner
     strReportName = "rptSeriesResultsByTotalCategoryPosnByAgeCategory"
     strSQL = _
          "SELECT RaceRunner, RaceRunnerNumber, RaceCategory, RaceGender, " _
          & " RaceRunnerFullName, [Club Name], HowManyRaces, RaceRunnerSurnameFirstName, Sum(RaceCategoryPosition) AS SumOfRaceCategoryPosition " _
        & "FROM qrySeriesPositionOverallIndividual " _
        & "GROUP BY RaceRunner, RaceRunnerNumber, RaceCategory, RaceGender, RaceRunnerFullName, [Club Name], HowManyRaces, RaceRunnerSurnameFirstName " _
        & "ORDER BY Racegender ASC, RaceCategory ASC, Sum(RaceCategoryPosition) ASC; "
  'open the report in design mode
  DoCmd.OpenReport strReportName, acViewDesign
  ' Set up the recordsource for the fields in the query
  Reports(strReportName).RecordSource = strSQL
  DoCmd.Close acReport, strReportName
  DoCmd.SetWarnings True
  DoCmd.OpenReport strReportName, acViewPreview


Go to the top of the page
 
jleach
post Jan 18 2020, 07:25 PM
Post#2


UtterAccess Administrator
Posts: 10,459
Joined: 7-December 09
From: St. Augustine, FL


Hi - I think Jon at AWF was having some hosting issues which are in process of being worked out. In the meantime, welcome to UA.

I haven't studied your question in depth (just a quick passing-by here), but I think if it were me I'd try one query (or two saved queries) utilizing a subquery and see if I can get the end results that way, then base the report off that. Usually quite a bit easier than doing it in code with recordsets. Have you looked at that as an option?

hth

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
theDBguy
post Jan 18 2020, 07:55 PM
Post#3


UA Moderator
Posts: 77,568
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

I agree with Jack. Just save both queries as regular queries, edit the second one to use the first query, and then use the second query for your report.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Jan 18 2020, 08:21 PM
Post#4



Posts: 1,269
Joined: 25-January 16
From: The Great Land


To reiterate from other thread:

Options:

1. Use QueryDefs code to modify saved query object

2. Pass SQL statement to report and have report set its own RecordSource property with statement

For option 2, the two queries will have to be one nested SQL statement.

This post has been edited by June7: Jan 18 2020, 08:22 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th February 2020 - 11:22 PM