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
> Totaling, Access 2016    
 
   
mike60smart
post Jun 1 2020, 09:51 AM
Post#1


UtterAccess VIP
Posts: 14,027
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have a report which displays a summary of scores for a given Day

Attached File  Scores.JPG ( 22.55K )Number of downloads: 0


Is it possible to sum the Individual Shooters so that I have totals as follows:-

Brian Bowling: Score = 438 XScore = 13
Jeff Kaufman: Score = 441 XScore = 15

Any Help appreciated

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
theDBguy
post Jun 1 2020, 10:07 AM
Post#2


UA Moderator
Posts: 78,476
Joined: 19-June 07
From: SunnySandyEggo


You can if you take out the Day and Match numbers.

--------------------
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
 
mike60smart
post Jun 1 2020, 10:31 AM
Post#3


UtterAccess VIP
Posts: 14,027
Joined: 6-June 05
From: Dunbar,Scotland


Hi Guy

I have to keep the DayNumber as there is a need to the Total for a number of Days

The SQL for the query is as follows:-

CODE
SELECT DISTINCT tblCompetionYear.CompetionYear, tblCompetition.CompetitionName, tbluDayNumbers.DayNumber, tbluMatchList.MatchList, qryRegShooterList.Shooter, tblRegisteredMatches.RegisteredMatchesID, Sum(tblRegisteredMatches.Score) AS SumOfScore, Sum(tblRegisteredMatches.XCount) AS SumOfXCount, tblRegisteredMatches.GroupSize, tblRegisteredMatches.BerdenString
FROM tbluDayNumbers RIGHT JOIN (((tblCompetionYear INNER JOIN tblCompetition ON tblCompetionYear.CompYearID = tblCompetition.CompYearID) INNER JOIN (tblRegistration LEFT JOIN qryRegShooterList ON tblRegistration.RegID = qryRegShooterList.RegID) ON tblCompetition.CID = tblRegistration.CID) INNER JOIN (tblCompDays INNER JOIN (tbluMatchList RIGHT JOIN tblRegisteredMatches ON tbluMatchList.MatchListID = tblRegisteredMatches.MatchID) ON tblCompDays.CompDayID = tblRegisteredMatches.CompDayID) ON tblRegistration.RegID = tblCompDays.RegID) ON tbluDayNumbers.DayNumberID = tblCompDays.DayNumberID
GROUP BY tblCompetionYear.CompetionYear, tblCompetition.CompetitionName, tbluDayNumbers.DayNumber, tbluMatchList.MatchList, qryRegShooterList.Shooter, tblRegisteredMatches.RegisteredMatchesID, tblRegisteredMatches.GroupSize, tblRegisteredMatches.BerdenString
HAVING (((tblCompetionYear.CompetionYear)=[Forms]![frmMain]![txtYear]) AND ((tblCompetition.CompetitionName)=[Forms]![frmMain]![txtComp]) AND ((tbluDayNumbers.DayNumber)="1"))
ORDER BY tblRegisteredMatches.GroupSize;


Can you do anything with this SQL ?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
GroverParkGeorge
post Jun 1 2020, 10:33 AM
Post#4


UA Admin
Posts: 37,482
Joined: 20-June 02
From: Newcastle, WA


The pattern for all aggregate queries is that you can apply the various operators to certain fields -- in this case Sum([Score]) and Sum([XCount]) -- but those operators will be grouped on whatever other fields are present.

So, if you want to Sum two fields grouped on one other field -- in this case [ShootersName]-- only [ShootersName] can be present in the query in addition to the two other, aggregated fields. That means the GROUP BY clause will include that one field.

If you want to group by [ShootersName] and by [Day Nr], you can add that second field to the Group By clause to get your totals for each [ShootersName] on each [Day Nr].

Your sample data only includes 1 day, so in this case, the two would, coincidentally, be the same. However, if you include two days results, including [Day Nr] in the Group By would result in different output than Grouping only on [ShootersName].

The difficulty can be that sometimes we want to SHOW fields in the query results but not GROUP on those fields. That's when you need a subquery to get the proper Grouping which can be joined to the other fields in the main query. I realize that's probably more than you need at this point, but given the sample data provided, it's worth including the possibility, I think.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Jun 1 2020, 10:56 AM
Post#5


UtterAccess VIP
Posts: 12,387
Joined: 10-February 04
From: South Charleston, WV


For an Access report I suggest you make another report with a group on Shooters Name. Grouping by name won't work if you maintain the current listing. Also, if you're not printing this, you might want to consider a datasheet or a continuous form with a popup form or subform (datasheet only) that shows the shooters total.

--------------------
Robert Crouser
Go to the top of the page
 
mike60smart
post Jun 1 2020, 11:24 AM
Post#6


UtterAccess VIP
Posts: 14,027
Joined: 6-June 05
From: Dunbar,Scotland


Hi George & Robert

After your prompts in the right direction I finally have what I need

Attached File  FinalScores.JPG ( 28.67K )Number of downloads: 1


Many thanks everyone

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
projecttoday
post Jun 1 2020, 11:57 AM
Post#7


UtterAccess VIP
Posts: 12,387
Joined: 10-February 04
From: South Charleston, WV


You're welcome.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th July 2020 - 07:02 AM