Summarizing Records in a Portal - Looking for the Right Approach

[Tony H]

Greetings all...I am creating multiple records in a table (call it Techniques). Each record has a category field that ultimately appears many times. The category field actually represents the types of techniques that are used in sequences in the motion picture visual FX industry. Trying to figure out the best approach to setting up a (summary) portal that will show the category appearing just once.

Additionally, here's where it gets a little tricky. Once I have set up the portal showing each category once, I will then need to calculate a range of factors, including: how many times (a count) a category appears in the Technique table's records (the amount of times that the category appears in the records actually represents a single shot or sequence in which the category is used). I also need to assign values to the single category record showing in the portal. For example, each single category will require various values, including a period of days (a number) that each technique takes to complete. My logic is that once I have calculated the amount of times a category appears in a work cycle, I can then multiply that number by the number of days assigned to each category, thereby getting a time period that would be required to complete the category of work across all of the shots that it applies to. I then need to assign the single category to a department.

I am assuming that beyond the spectrum of building a summary portal that shows each category once, I would also have to create fields that allow me to calculate and assign values associated with the category records. My question, beyond looking for the right approach to building a portal (or even creating a summary on a layout if that is more feasible), is where I should create the fields that will be required for assigning values once the summary portal is operational. Should I create these fields in the Technique records? Or create them in a separate table that will be ultimately joined to the Technique records. Thoughts, advice and examples greatly appreciated.

[Søren Dyhr]

I am assuming that beyond the spectrum of building a summary portal that shows each category once, I would also have to create fields that allow me to calculate and assign values associated with the category records

No it isn't, it's what here (this forum) is called Ugo's method - take a look at the template ataached to this:

http://fmforums.com/forum/showpost.php?post/266487/

Alternatively could you study this, which isn't "portalized" but even so pretty flexible, geared toward a instant showing of "What if...?"'s

http://concise-design.com/downloads/invoice_cf.zip

--sd

[comment]

The best way to view information about categories would be in a Categories table, where each category has one unique record. With a relationship to Techniques, each Category record can easily summarize the techniques assigned to it.

I got a bit lost in the rest of the description. It's not clear whether your Techniques table is a table of techniques in general, or of specific shots for specific projects - if the latter, you would need a global ProjectID in Categories to filter shots by Project.

where I should create the fields that will be required for assigning values

A field should be ALWAYS defined in the table that represents the entity whose attribute is being described in the field. This may be a bit difficult to read at first, but I believe it's very relevant to what you're asking - because it looks like the real issue here is the missing Categories table.