AnsweredAssumed Answered

Summarizing related records data

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

Title

Summarizing related records data

Post

Hello, 

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.

 

Outcomes