Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Report results different than query data

    I am working with ACCESS 2013 and Windows 10. I am working with basketball stats. I am working with Career Points scored - most found from totaling game by game stats. The query gives me good results. When I go to print a report, the points scored value is different. It seems that the report shows the career_points * #_years_varsity.Report_V_Car_Points.txt
    I only did the query part with the career years after seeing the problem with the points in the report.
    The SQL for the query:
    SELECT TOP 35 [Years_Var_#1_Query].Full_Name, Sum([53_V_v3_Seas_Pts_Query].Max_pts) AS SumOfMax_pts, [Years_Var_#4_Query].Career_Years, [Years_Var_#2_Query].MaxOfFall_Year, [Years_Var_#2_Query].MinOfFall_Year, [Years_Var_#2_Query].[MaxOfFall_Year]-[Years_Var_#2_Query].[MinOfFall_Year]+1 AS Years_played
    FROM [Years_Var_#4_Query] INNER JOIN ([Years_Var_#3_Query] INNER JOIN (((53_V_v3_Seas_Pts_Query INNER JOIN Players ON [53_V_v3_Seas_Pts_Query].Player_ID = Players.Player_ID) INNER JOIN [Years_Var_#1_Query] ON Players.Player_ID = [Years_Var_#1_Query].Players.Player_ID) INNER JOIN [Years_Var_#2_Query] ON [Years_Var_#1_Query].Players.Player_ID = [Years_Var_#2_Query].[Years_Var_#1_Query].Players.Player_ID) ON [Years_Var_#3_Query].[Years_Var_#2_Query].[Years_Var_#1_Query].Players.Player_ID = [Years_Var_#2_Query].[Years_Var_#1_Query].Players.Player_ID) ON [Years_Var_#4_Query].[Years_Var_#3_Query].[Years_Var_#2_Query].[Years_Var_#1_Query].Players.Player_ID = [Years_Var_#3_Query].[Years_Var_#2_Query].[Years_Var_#1_Query].Players.Player_ID
    GROUP BY [Years_Var_#1_Query].Full_Name, [Years_Var_#4_Query].Career_Years, [Years_Var_#2_Query].MaxOfFall_Year, [Years_Var_#2_Query].MinOfFall_Year, [Years_Var_#2_Query].[MaxOfFall_Year]-[Years_Var_#2_Query].[MinOfFall_Year]+1, Players.Grad_year
    ORDER BY Sum([53_V_v3_Seas_Pts_Query].Max_pts) DESC , Players.Grad_year;
    The other question - can a value be 'created' in the report? i.e. SumOfMaxPts/#_Career_Years
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I got it to work. I removed several of the queries that were linked, and only used 2 queries and the Players table. The first query was the points totaled, and the second query was with the career years. I had to link the Player_ID in the Players Table to both queries. I also created the full_name from FirstName and LastName in the Players Table.
    But this is not explaining why I am getting a good value in the query, but then the report seems to be multiplying the points scored for the career * the # of seasons played.
    When I tried using the query that I used for the original report, and made a 2nd query supposedly just using the fields in that first query, I again got the strange points total, and some other strange values. I thought if I simply Create using Query Wizard, and just 'pull' the fields from that query, I would be looking at the same data.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •