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.
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?