Sub Summary for a Calculation

[Jon Crain]

Alright, I'll try to make this brief and to the point. Three tables. Royalty DB.

Table 1...Project

Table 2...Tracks (ProjectGroupSubtable)

Table 3...Group

I want a calculation which summarizes the number of tracks by a group on a certain project. (This will then be used in a seperate calculation to figure out royalties)

Right now whenever I try to use Count(field) it counts tracks by a Group over all the projects instead of just one.

I've read about using sub-summary parts, but I'm under the impression that these wouldn't fit my situation, am I wrong?

Thanks!

jon

[Genx]

Im not sure if im understanding the problem correctly seeing as i dont see where project comes in... but anyway.

If tracks is a subtable of group... i.e. 1-1

Relate Tracks to a second Tracks table occurance via the group identifier (foreign key). Then create an unstored calc in this table occurance to count over that relationship.

If thats not what your after, maybe im just not getting it, but if you could clarify a bit that'd be great.

~Genx

[Jon Crain]

Sorry about that, the Project would be like the album which has a number of tracks on it by different groups.

A group could be on a couple of projects and therefore the track count for a group always seems to add up all the total tracks for all the projects that they are in, which doesn't help me very much.

So again

Table 1

pk.projectID

Table 2

fk.projectID

fk.groupID

pk.trackID

Table 3

pk.groupID

Edited June 5, 2006 by Guest

[Jon Crain]

I believe I have figured it out. Basically used a self join table off of Table 2, and summarized values from the first occurence.

(I think I just confused myself with that statement...)

jon

[Genx]

Um righto, as long as the problem is solved!