Undoubtedly, the Examiner was envisioning using DSum to get the running
totals ... are the domain functions (DSum, DCount, etc.) part of the course
criteria?
Personally, I would use a subquery (mainly because it's been years since I
wrote a call to DSum), but ... create a new query, switch to SQL View, and
paste in this sql statement:
Select TrackID, Title, [Effort Rating], Genre, Length,
DSum("Length","Tracklist","TrackID<" & t.TrackID &
" AND Genre = '" & t.Genre & "' AND [Effort Rating] ='" &
t.[Effort Rating] & "'") As RunningTotal
From Tracklist As t
Run it to make sure it works (again, I'm out of practice with DSum), then
save it with the name TracklistsWithRunningTotals.
Then, in your existing query, change Tracklist to
TracklistsWithRunningTotals and add to the WHERE clause:
And RunningTotal <= 900
Wait, perhaps the examiner was envisioning the student using the RunningSum
property of a report field ... is that part of the course criteria?
Hi again and thank you.
You are right about the cross join as that was the only way I could
set criterea to get the dancer, with genre and energy levels. I will
eventually do that with Parameters, so that each dancer can get a
playlist suited to them and then make the Report.
Yes the tracks have an autonumber TrackID.
Thanks for the time, much appreciated.
Bob Barrows said:
Julian wrote:
You are very kind, thank you but you may be glad to know it isn't my
homework, my headache is because I'm the teacher :-( [? - well at
least that's the theory]
Here is the sql thing
SELECT TblBooking.SlotI1, TblBooking.Slot2, TblDancers.Surname,
TblSlots.[Slot Times], Tracklist.Title, Tracklist.[Effort Rating],
Tracklist.Genre, Tracklist.Length
FROM Tracklist, TblSlots INNER JOIN (TblDancers INNER JOIN
TblBooking
ON TblDancers.DancerID = TblBooking.DancerID) ON TblSlots.SlotID =
TblBooking.SlotI1
WHERE (((TblDancers.Surname)="farmery") AND ((Tracklist.[Effort
Rating])="m") AND ((Tracklist.Genre)="jb"));
I have managed to get the suitable dance tracks for the dancer's
playlist and the dance times. It goes quite nicely into a Report,
using levels to have lots of tracks and only one name, and slots at
the top.
The problem is that the examiners seem to think there is a way [for
year 11 kids remember] Quote "to make sure the dancers have a total
play time of not more than 15 minutes"
I can Sum / Total and a few other things but I can't, for the life
of
me, see how Access can select dance tracks up to a limit of 15 mins
and then stop there, let alone decide which ones would make the best
selection. I'm guessing it is probably not a practical thing for the
examiner to ask or is there a relatively easy solution I just can't
see?
You're doing a cross-join between tracklist and the rest of the
tables? Is that giving you the results you want? I guess if you want
each dancer to get the same tracklist, it would make sense ...
Again, sample data and desired results would really help.
This does seem like an advanced query. You are going to need to do a
running sum and then select the records whose running sum is less
than 15 minutes. Is there a track ID that can be used to order the
tracks when doing the running sum?
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"