Re: help please: 'contains' relationship / counting usage

On 2005-12-30 21:17:43 -0800, Helpful Harry <helpful_harry@xxxxxxxxxxxxxxxx> said:

In article <2005123016082616807%bob@nowherecom>, grasshopper
<bob@xxxxxxxxxxx> wrote:

I have 2 tables, Books and Subjects.

The field Subjects:subject contains things like:

chemistry
bio chemistry
geology
geography
human geography

The field Books:title contains entries such as:

chemistry 101: basic principles
elective bio chemistry 203: cellular structures

I'm trying to create a calculation field in Subjects that shows how many Books:title fields contain the Subjects:Subject

The tricky thing here is that Books:title is a free form field, so I can't just create a relationship that equates the two... I need to work out a relationship that indicates inclusion (i.e. contains Subjects:subject.

In our limited example, we would get:

chemistry: 2
bio chemistry: 1
geology: 0
geography: 0
human geography: 0

Does this make sense? I was thinking maybe a custom function rather than a basic Count of related records?

thanks in advance for the help, and happy gnu year folks!
g.


With only that very brief description of your database it's difficult
to understand exactly what you're doing, but it's could be better and
easier to have a field in the Books table which is used to store the
subject of the book, probably a checkbox field obtaining it's
categories from the Subject table.
Then when you enter a book you can mark off what subject(s) it covers.
That way you simply need a relationship set-up in the Subjects table as
Subjects::Subject = Books::Subject and the count in the Subjects table
of the number of related books for each subject simply becomes
Count(Books::Subject).


Helpful Harry Hopefully helping harassed humans happily handle handiwork hardships ;o)

No, it's not a one-to-one relationship that can be set by hand, the field is by requirement a free form field.


.

Relevant Pages