I'm back trying to essentially cross-tab some data.
I have a solution with multiple related tables.
A songbook table which includes a song title, duration, etc.
A gig table which includes information like performance date, venue, etc.
A Singers table which contains performers names and info.
A Performance table (join table) contains the following fields:
SongbookID (the song)
Set (in which the song is performed)
Slot (the place in a set where the song is performed)
SingerID (to identify the singer)
GigID (which Gig is being performed)
I have a SetSummary table which is related to the Performance table for the purpose of trying to summarize data in the Performance table. For example, there's a Set1Count field which looks at the GigID in Performance, then any songs with Set=1 and counts those records. This works fine!
The challenge comes when trying to calculate the length of the set. There is a duration field in the Songbook table which I'd like to sum based on Set number. I've tried both case and nested ifs (see below). The result in the field is for the entire gig - not just set 1.
If ( gigs_PERFORMANCES::GigID=GigID;
If ( gigs_PERFORMANCES::Set=1;
sum ( gigs_performances_SONGBOOK::Duration)
I can share the whole database and relationships, but thought I'd through this out there.
Many of you have helped in the past with this Gigs solution.