AnsweredAssumed Answered

Summarizing related records data

Question asked by hokiemike64 on Aug 23, 2012
Latest reply on Aug 23, 2012 by philmodjunk


Summarizing related records data



I have a client that has an assortment table and also has a related items table. I have a few fields in the item table that calculate a number based on whether the item is New, Revised or Repeated.


What I am trying to do is show in a report from the assortment table data a summary (total) of all of the related items. Since the total of the items is always 1 (if the parameter evaluated is true), I just want to aggregate these items as total count columns in my report.


To illustrate...


The assortment table has several headers...

ID Name DesignsNew DesignsRevised, DesignsRepeated


The Item Table has 

ITem ID   AsstID_fk (foreign key) IsNew IsRevised IsRepeat.


IsNew, IsRevise, IsRepeat are 1 if the item type is New, Revise or Repeat respectively.


What I want my report to look like (and also export to excel as a table) is


AsstID AstName DesignsNew DesignsRevised DesignsRepeated Where


DesignsNew, Revised and Repeated are summarizations (total) of the IsRevise, isrepeat and isnew fields from the related item table.


Any thoughts or cheezy examples that can get me going?


Thanks much.