Report results different than query data

Topic

New Reply

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.44799-Report_V_Car_Points
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

Reply | Quote

Replies

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.

Reply | Quote