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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Sum Smallest Three Values, Access 2010    
 
   
bouncee
post Feb 12 2019, 09:14 AM
Post#21



Posts: 101
Joined: 5-August 05
From: Eastbourne, UK


I'm pretty busy, jmh, but you'd have to send me the database, you forgot to attach it to your last post
Go to the top of the page
 
jmh2008
post Feb 12 2019, 10:02 AM
Post#22



Posts: 67
Joined: 27-August 10
From: Windsor, Nova Scotia


If only I would remember to hit the upload button!

thanks

Attached File(s)
Attached File  Ski_Tracker_test_2019.zip ( 105.34K )Number of downloads: 1
 
Go to the top of the page
 
bouncee
post Feb 12 2019, 12:54 PM
Post#23



Posts: 101
Joined: 5-August 05
From: Eastbourne, UK


I had a look at your latest update.

I see you've changed female to girls and male to boys, good thing!

The 12qryPosition failure might be solved by using tblParameters; I see you're not using it, although the version I sent does use it, although it doesn't populate it.

To change from using school to team, you need to update the data so that all competitors have a team entry.

If you edit the data and post the new version I will take a look at it.
Go to the top of the page
 
jmh2008
post Feb 12 2019, 02:48 PM
Post#24



Posts: 67
Joined: 27-August 10
From: Windsor, Nova Scotia


Here it is again. Everything works but that position number, which then make for the top three sum to be wrong.
Attached File(s)
Attached File  Ski_Tracker_test_2019.zip ( 260.87K )Number of downloads: 6
 
Go to the top of the page
 
bouncee
post Feb 13 2019, 08:18 AM
Post#25



Posts: 101
Joined: 5-August 05
From: Eastbourne, UK


It looks like positions and total 3 times are actually correct, despite what you say.

All you need to do I think is tidy up the report so that date, age group and gender are shown in the title, and team name, rather than id, is shown in the team header.

You could also expand tblRacetimes subform to use it for data entry, with team name, rather than id, shown.
Go to the top of the page
 
jmh2008
post Feb 13 2019, 09:19 AM
Post#26



Posts: 67
Joined: 27-August 10
From: Windsor, Nova Scotia


Thanks for all your help. Ironically, I was testing the date of Feb 6, 2019, Junior, Girls and because there is only one team, it adds all scores, but if I select criteria with more than one team, it works correctly. Meaning, it adds the top three lowest scores. Still can't figure out why it does this with one team, so I guess I'll see if anyone else can debug this error.

Also it I run the 12qryposition query, it gives me errors but using the input form works, maybe just an Access thing.

Again, thanks so much.
Go to the top of the page
 
bouncee
post Feb 13 2019, 11:16 AM
Post#27



Posts: 101
Joined: 5-August 05
From: Eastbourne, UK


I had a look into the all times problem.
There is an error in calculating the position in 12qryPositions.
It checks position by subtracting 1 second for some reason, and, since the 4th fastest competitor's time is less than a second slower than the 3rd, her position is calculated as 3rd in error.
This is the correct calculation:
Position: IIf([10qrydata].[CombinedTimes]>0,DCount("*","[10qrydata]","[Team Name]= " & [10qrydata].[Team Name] & " And [CombinedTimes]<" & [CombinedTimes]),0)
Go to the top of the page
 
jmh2008
post Feb 13 2019, 12:05 PM
Post#28



Posts: 67
Joined: 27-August 10
From: Windsor, Nova Scotia


THANKS!

I'm glad I took that one as my test run, but then when I couldn't figure out why it was the only one with a wrong total, I was totally in a fog!

Thank you, Thank you!
Go to the top of the page
 
jmh2008
post Feb 18 2019, 11:10 AM
Post#29



Posts: 67
Joined: 27-August 10
From: Windsor, Nova Scotia


Is it possible to have a listbox on the form of Race Date, pick multiple dates, then run a report? I can't figure out how to pass multiple dates into the query from the form to create the report.

Thanks in advance for your help!

Attached File(s)
Attached File  multiple_dates.JPG ( 43.76K )Number of downloads: 2
 
Go to the top of the page
 
bouncee
post Feb 18 2019, 12:19 PM
Post#30



Posts: 101
Joined: 5-August 05
From: Eastbourne, UK


Looks like you have multiple reports now.

Not sure why you would want select multiple race dates, makes generating report more complicated.

If you really do, then when you open the main menu, you could populate a race dates table from the run times table.

The fields would be race date and a 'selected' yes/no field which you could tick on a race dates form (which would be a subform on the right, as per your screenshot).

You would then use the table in the queries and in the report you would sort and group by date.

Hope that helps.
Go to the top of the page
 
jmh2008
post Feb 18 2019, 02:57 PM
Post#31



Posts: 67
Joined: 27-August 10
From: Windsor, Nova Scotia


I actually got it working via VBA.

Downloaded a sample from http://www.databasedev.co.UK/downloads.html

Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2019 - 11:11 PM