Limiting the total time for dancing to 15 mins [900 sec]
in [Queries]
|
From: Julian on 29 Jun 2008 13:33 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?
From: Bob Barrows [MVP] on 29 Jun 2008 13:48 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"
From: Julian on 29 Jun 2008 14:04 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 [MVP]" wrote: > 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" > > >
From: Bob Barrows [MVP] on 29 Jun 2008 15:08 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? Julian wrote: > 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 [MVP]" wrote: > >> 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"
From: Julian on 29 Jun 2008 16:24 Hi, Thanks for the reply. I am not sure whether the [DSum, DCount] are part of the course criterea. They are quite "cosy" about the level of ICT capability they expect the pupils to have. I am not even sure they would expect the Sub Query level either. I can check both these with the board / local advisors now I have something to work on, thanks to you. It is nowafter 21.00 hrs here and will have to wait until tomorrow to send to the board. I will, in the meantime try the sql you so kindly sent. I will let you know how it goes. Many many thanks. sincerely yours Julian "Bob Barrows [MVP]" wrote: > 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? > > Julian wrote: > > 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 [MVP]" wrote: > > > >> 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" > > >
|
Pages: 1 Prev: Duplicating values to empty field Next: Ungrouping in DSUM |