3 Replies Latest reply on May 1, 2012 9:51 AM by philmodjunk

    Difficult Relationship Issue - summary and calculations

    cjl108

      Title

      Difficult Relationship Issue - summary and calculations

      Post

      So I have been trying to wrap my head around something and can't quite figure it out. I have a library/inventory database I have been building and so far, with all the info I find here in the forums, I have found the answers I need(thanks for that)... until now!

      My database is set up as so:

      SET---<BOOK---<SERIES_VOLUME>---SERIES

      SERIES_VOLUME::volumeNumber

      So I have a Set of 5 books in a series(ABC) that may have 3 sets and other single books. (not all sets are in a series and not all series have sets)

      In this particular set, the series volume numbers are 121-125, and from a Set layout, I wanted to use a summary and/or calculation field to say "Set of 5 books in the ABC Series, volumes 121-125"

      I was able to use a summary field in the BOOK table to get the number "5", but am unable to get the #s 121-126.

      Any ideas out there? 

        • 1. Re: Difficult Relationship Issue - summary and calculations
          philmodjunk

          I gather that you want the range of volume numbers for a set and not a book? Can you be sure that with a set, there will be no gaps in that range of volume numbers?

          A calculation field in Set might be defined as:

          SERIES_VOLUME::volumeNumber & " - " & Last ( SERIES_VOLUME::volumeNumber )

          You may need to specify a sorted relationship for Series_volume to get the correct values for that and this assumes you want the range for the set and that you do not have gaps in that range.

          • 2. Re: Difficult Relationship Issue - summary and calculations
            cjl108

            I wasn't able to get that to work, I am not sure if it is because there can be multiple series for each book. Through lots of trial and error, I was able to get the following to work:

            In a calculation field in SET:

            Min ( BOOK::calc_seriesVol ) & "-" & Max (BOOK::calc_seriesVol )

            In a calculation field in Book:

            SERIES_VOLUME::volumeNumber

             

            It seems like I should be able to do this more simply, but it seems to be working. I am worried it may slow things down as the DB gets bigger, but for now it will do. I simplified the set up a bit, because actually, the set and book are instances of the same table, so that throws another semi-confusing issue into things. I am still not sure that is the best way to go, but that is another question!

            Thanks everyone

            • 3. Re: Difficult Relationship Issue - summary and calculations
              philmodjunk

              Sounds like you did not sort the relationship as I indicted might be necessary. That said, if your approach works, there's no compelling reason to change it.