Comparison Report help needed (1 Viewer)

Hi All
I have a bit of a problem creating a report which will show the comparison of scores for players between for example "Round 1" and "Round 2"

The table is populated with fields Round No,Name, Score
eg
Round Name Score
Round 1, Jim, 24,
Round 1,Nick, 25,
Round 1, John,26,
Round 2, Jim,15,
Round 2, Nick,16,
Round 2, John,17

Is it possible to create a report that will show Round 1 adjacent to Round 2 as in comparison to how the players improved or didnt
and just to complicate it more when round 3s results get submitted move round 2 to where round 1 was and round 3 to where round 2 was on the report and so on throughout the season

Hope its not too hard to understand my needs
Shakey

So you're always comparing the latest round with the previous round...
For the 3 examples given, the answer is -9

It would be easier to do if you change the Round field to a number field i.e 1, 2, 3 etc then you can easily get the latest round using Max

However, I'm fairly sure Round is a reserved word in Access so you will get issues using it. Change to something like EventRound.

Also what's the name of your table?

Hi Colin
sorry for the late reply have been too busy at work
I think that you have misunderstood the gist of what i am wanting to do
i have a similar application i produced in excel for a naps table leaderboard at the local which produced a ranking comparison table (please see Attachment)
I was hoping to do something similar in access
James View attachment Naps Competition.docx

I think that you have misunderstood the gist of what i am wanting to do

Click to expand...

It happens .... though I got part of it right, my explanation wasn't quite what I meant
Having the Word file would have helped in the first post!

Anyway after looking at your Word file, you need to do something like the following:

1. Create a main report with 3 subreports side by side - left / middle / right
2. Use the main report for the header line "Naps Competition (2016 Flat) Leaderboard" or whatever
3. Left subreport has the current week's scores; middle the previous week's scores ; right the strike rate data

Create separate aggregate queries for each of these subreports
a) Current week: Group by Name & points & use MAX to identify the latest week (see my comment in previous reply). Sort by points Descending
Getting Rank values is a little more tricky & you'll need to do some research
e.g. http://allenbrowne.com/ranking.html

b) Previous week - basically same as above but you need MAX week -1
The Up/down arrows are an extra complication - after these denoting changes in points or rank order? Either way, worry about that after you've got the rest sorted

c) I've no idea how your strike rate data is sorted.
Win% should be easy to calculate - Wins/Matches displayed as % with no d.p.
Place% would be similar using whatever criteria this is based on

Doing all of this may take you a while - have fun

NOTE: the above website will also assist with all aspects of aggregate queries

Hope that helps

Thanks for that
I have managed to get a query that just shows the most recent round and when i update the results table to the next weeks set of results the query updates as well and shows the most current weeks scores (Hallelujah)

Do you have any pointers to how i subtract 1 from the Max to get previous weeks scores :banghead:

I tried -1 in criterea
any help would be great
James

shakey said:

Thanks for that
I have managed to get a query that just shows the most recent round and when i update the results table to the next weeks set of results the query updates as well and shows the most current weeks scores (Hallelujah)

Do you have any pointers to how i subtract 1 from the Max to get previous weeks scores :banghead:

I tried -1 in criterea
any help would be great
James

Click to expand...

Save your first query in SQL view and post it here using code tags - the # button. I'll modify and post back

Top man
i think this is what you mean

James

one way is to do it with 2 more queries

Lets call your first query qryA
Now create a second query qryB as follows

CODE]SELECT TOP 2 Max(tblresults.[Round No]) AS [MaxOfRound No], tblresults.Names, tblresults.[Round Points]
FROM tblresults
GROUP BY tblresults.Names, tblresults.[Round Points]
ORDER BY Max(tblresults.[Round No]) DESC;[/CODE]

Now use the unmatched query wizard on those 2 queries to get the records in qryB that aren't in qryA

That should give you what you want

Thanks for your perseverance in helping me on this but qryB returns the same results as qryA

If you have time could you look at the thing I am completely Bamboozled by this
cheers James

Hi James

Scrapped the previous idea & have now done it a different way

I've deleted qryA & qryB & added 4 new queries
So you can find these easily, I've done a custom group in the navigation pane & called it 'ridders'



Once you've looked at these queries, you can easily change it back to standard view by clicking on the Custom header and selecting Object Types

You might want to delete any of your own test queries that are no longer needed before going on to the next step - ranking or the query for the third subreport

Have 'fun' doing the next step...

Thats Great Thanks
Many thanks for giving up your time on a Sunday
If I could there would be a pint waiting for you at your local

You're welcome but at 10.30 in the UK its a bit early. Maybe later ....