Getting totals from a subform into a field on the parent.

[Morogth]

This is something that's been bugging me for a while now, and I'm still no closer to finding a solution. Hopefully I'll make sense in what I'm saying, but please bear with me. Here goes.

I have a music database. It's somewhat over the top in terms of the information stored in it, but I like it. My issue is with getting the total number of tracks, as well as the total duration, for a given album (and for each disc, but if I can solve one that should also solve the other).

I have a lot of tables and forms, but for this problem I only need to deal with two of each. They are the tables for albums, the table for tracks, and their associated forms.

In my album table I have information such as album title, artist, release date, and album length. The tracks table contains information on each song - track title, track number, parent album, and track length.

My main form is for the album itself, and contains a subform for the tracks on said album. They correspond to the relevant tables.

What I want to do is obtain the value of the album length by calculating the sum of the track lengths for that album, and the number of tracks. Easy enough - Sum(TrackLength) and Count(TrackID) respectively, both of which can be stored in the footer of the subform.

My problem is that, rather than simply copying those values into text boxes on the main form, I want to copy them into actual fields in the album table. Furthermore, I want them to update as I add or edit the track lengths in the subform.

I'm at a loss at the moment, having tried for a few years to solve this. I'm convinced I'm missing something obvious, but I still can't find out what. Any help would be a massive help.

Al

 

[CJ_London]

the obvious thing you are missing is that you shouldn't store calculated values in a table - just calculate as you go

 

[Morogth]

I see your point. I should point out that I'd simplified the problem because it actually occurs over several forms. For example, I have a number of albums with multiple discs. Some of these discs have different sections, for example the album proper, and a CD-ROM section.

What I want to do is get the total duration of the tracks per section, then the total duration of the sections per disc, then the total duration of the discs per album. I've already set my forms up so that the subforms display the requisite totals, so I can manually type them into the parent forms. I'm just wondering if there is a way of doing this automatically.

Did I mention I'd gone a bit overboard with it?

 

[CJ_London]

Did I mention I'd gone a bit overboard with it?

Whatever floats your boat

What I want to do is get the total duration of the tracks per section, then the total duration of the sections per disc, then the total duration of the discs per album

this can still be easily calculated as you go - the formula will be the same as for updating.

But this

Furthermore, I want them to update as I add or edit the track lengths in the subform.

Makes updating horrendous

Easy enough - Sum(TrackLength) and Count(TrackID)

it is. Although I am not a fan of domain functions, they have their uses and would be appropriate in this case.

I'm not clear of your table and form structure but would assume it is along the lines of

tblAlbums
AlbumID autonumber
ArtistID long
AlbumName text

tblTracks
ID autonumber
AlbumID long
DiscNo integer
TrackNo integer
TrackTitle text
TrackLength double

so in your control on the form where you are displaying the tracklengths per disc the controlsource would be something like

=dsum("TrackLength","tblTracks","[AlbumID]=" & me.albumID & " AND [Discno]=" & me.discno)

and for the album
=dsum("TrackLength","tblTracks","[AlbumID]=" & me.albumID)

 

[CJ_London]

or having selected an album, you would have a listbox to display the discs with a rowsource of

 SELECT Discno, sum(tracklength) as Tracklengths, count(*) as NoofTracks
 FROM tblTracks
 WHERE tblTracks.AlbumID=[COLOR=red][AlbumID][/COLOR]

Note, this is the code in the rowsource, not a separate query. Where the bit in red is the name of the appropriate control on the form