A query that returns values that total less than a required amount?

[roadrage]

Hi

Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds.
1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of?
2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.

My knowledge of Access is Intermediate.

Kind regards

 

[Kinger43]

Add all of your fields to the query and in the criteria for the "length" field add <900. If you want the ones that equal 900 then use <=900.

 

[roadrage]

I don't want find the records where the length is less than 900, sorry, but what i need is for the query to calculate when any of the records from the length field equal or are less than 900 in total. I hope that makes sense?

So if three songs each with a song length of 300 seconds, once the query found these it would stop and list the tracks?

 

[WayneRyan]

RoadRage,

This can be very simple, but it depends on your table structure.

If the TrackTimes are all in ONE record (bad idea), you'll have to make
a new field in your query:

NewField: TrackTime1 + TrackTime2 + TrackTime3 ...

And in the criteria, put --> < 900

If you have multiple tables (good idea):

Select A.CDName, Sum(B.TrackTime)
From   tblCD As A Inner Join tblTracks As B On
         A.CDID = B.CDID
Having Sum(B.TrackTime) < 900

Wayne

 

[roadrage]

Hi wayne,

Thanks for your reply. The Track times are all in one table if thats what you meant, unfortunatley i was given the data like this. I'll give your 1st suggestion a try and get back to you. Thanks again.

 

[roadrage]

"And in the criteria, put --> < 900"

I get an error message when i put that into criteria:
The expression you entered contains invalid syntax
You may have entered a comma without a preceeding value or identifier

 

[boblarson]

He said to put

< 900

NOT < 900"

 

[roadrage]

He said to put

< 900

NOT < 900"

I was putting in >< 900

Now that i have changed it i get prompted by a dialogue box asking for values to enter into track1, track2, and track3 and then nothing is displayed in the query result

 

[WayneRyan]

RoadRage,

You have to substitute YOUR names, they're probably not track1, track2, and track3.

And to be safe surround each name in brackets like --> [Track 1 Time#]

Because my "sample" name had embedded spaces and the # symbol, I need to
put the brackets.

If all else fails, post your table structure here.

Wayne

 

[roadrage]

RoadRage,

You have to substitute YOUR names, they're probably not track1, track2, and track3.

And to be safe surround each name in brackets like --> [Track 1 Time#]

Because my "sample" name had embedded spaces and the # symbol, I need to
put the brackets.

If all else fails, post your table structure here.

Wayne

Hi wayne,

I was replacing Track with Length. I'll attach my table structure here if thats ok.

 

[Kinger43]

I guess I'm still confused on what you are trying to do. Are you wanting to start at the top of the table and add track lengths until you go over 900 then return the first x tracks before you went over?

 

[neileg]

Your Length field is set to text. Why? You can't do sums with text.

 

[roadrage]

I guess I'm still confused on what you are trying to do. Are you wanting to start at the top of the table and add track lengths until you go over 900 then return the first x tracks before you went over?

Yes thats what i want to do. Is it possible?
I can change the length field type to number, silly me!

 

[roadrage]

Hi i still have not managed to resolve this can anyone help?

 

[Jurjen]

SQL is not correct: GROUP BY clause is missing

RoadRage,

This can be very simple, but it depends on your table structure.

If the TrackTimes are all in ONE record (bad idea), you'll have to make
a new field in your query:

NewField: TrackTime1 + TrackTime2 + TrackTime3 ...

And in the criteria, put --> < 900

If you have multiple tables (good idea):

Select A.CDName, Sum(B.TrackTime)
From   tblCD As A Inner Join tblTracks As B On
         A.CDID = B.CDID
Having Sum(B.TrackTime) < 900

Wayne

This should of course be:

Select A.CDName, Sum(B.TrackTime)
From   tblCD As A Inner Join tblTracks As B On
         A.CDID = B.CDID
[B]GROUP BY A.CDName[/B]
Having Sum(B.TrackTime) < 900

 

[WayneRyan]

Jurgen,

Hey thanks.

I guess that every HAVING should have a Group By.

Nice catch.

Wayne