Limiting the time in a Query

The exam site here has a Query in it which is mostly simple
http://www1.edexcel.org.uk/spbs2007/...MusicULike.htm
It also wants to restrict the total play time for record tracks displayed,
to 15 minutes [900 sec]. Track times are in the table as seconds.
I have asked various people and they generaly think it is not possible
[particularly at student level 2 standard [year 11 exams]
Has anyone any idea if it can be done at the level indicated, or if it is
simply not achievable? Please - thanks for looking.

Julian wrote:
The exam site here has a Query in it which is mostly simple
http://www1.edexcel.org.uk/spbs2007/...MusicULike.htm
It also wants to restrict the total play time for record tracks
displayed, to 15 minutes [900 sec]. Track times are in the table as
seconds.
I have asked various people and they generaly think it is not possible
[particularly at student level 2 standard [year 11 exams]
Has anyone any idea if it can be done at the level indicated, or if
it is simply not achievable? Please - thanks for looking.

This isn't a lure to get us to look at your site, and perhaps allow it to
install malware on our computers, is it? :-)

You should post the sql for your query here, as well as telling us something
about your table structure(s). That way you will get more of us to look at
your problem.

--
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"

Thanks for the reply. I will try to improve the question. The site is an
english exam board, so not a nasty one - well not in the sense you mean! [If
some nice person lets me know what an sql is, I will try to do that too]
The tables are 1. Details of the tracks, titles, artist, track length,
genre, H,M & L energy level. 2. Dancer name, music preferences, age and
energy preference etc
I also have alinking table for booking the slots the dancers should go in.
[All pretty standard stuff] The idea is to query for suitable tracks for
individual dancers and make an individual playlist for them, according to
their preferences etc [again pretty standard] [Before getting more about a
Report being the nicer format for the playlist, the intention is to do that
from the query.
NB The tracks seem to be from genuine CDs and the times are much varied from
a couple of minutes to around 5 or 6 [in seconds thankfully]
So far OK then - BUT the examiner then says "make sure they have a total
play time of no more than 15 minutes"????? ....... TILT!!

"Bob Barrows [MVP]" wrote:

Julian wrote:
The exam site here has a Query in it which is mostly simple
http://www1.edexcel.org.uk/spbs2007/...MusicULike.htm
It also wants to restrict the total play time for record tracks
displayed, to 15 minutes [900 sec]. Track times are in the table as
seconds.
I have asked various people and they generaly think it is not possible
[particularly at student level 2 standard [year 11 exams]
Has anyone any idea if it can be done at the level indicated, or if
it is simply not achievable? Please - thanks for looking.

This isn't a lure to get us to look at your site, and perhaps allow it to
install malware on our computers, is it? :-)

You should post the sql for your query here, as well as telling us something
about your table structure(s). That way you will get more of us to look at
your problem.

--
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"

Julian wrote:
Thanks for the reply. I will try to improve the question. The site
is an english exam board, so not a nasty one - well not in the sense
you mean! [If some nice person lets me know what an sql is, I will
try to do that too]

Open your query in Design View. Then do one of the following:

View | SQL View
Click the appropriate toolbar button
Right-click in the query window and select SQL View

This will display the SQL being generated by your actions in the Design
View.

The tables are 1. Details of the tracks, titles, artist, track length,
genre, H,M & L energy level. 2. Dancer name, music preferences, age
and energy preference etc

Data types are always helpful. Some of those are obviously Text fields. What
are the others? track length, for instance. Is that a Number field?

Also, sample data and desired results when using that sample data always
helps. Show us a few rows of data (only the relevant fields, please) from
each of the relevant tables, then show us what the data would look like from
a successful query. A picture is worth a thousand words (this is not to say
that you should attach screen shots ...).

So far OK then - BUT the examiner then says "make sure they have a
total play time of no more than 15 minutes"????? ....... TILT!!

Oh! we're being asked to do your homework ... ? :-)
Post the details and we'll decide what to help you with. :-)

--
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"

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?

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"

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"

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"

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"