Summary Reports

[Conran]

Hi there-

Our customer database is broken down that each product a customer requests is it's own separate record. So each customer has multiple records.

For this report, I want to be able to see each customer, what is the potential sum of all their requests, the actual sum of what has been purchased and the difference between the two. I can do that for each customer, and it now looks like this:

(list serial number)

(list serial number)

(list serial number)

(customers last name)(potential sum)(actual sum)(difference)

However I can only do this for one customer at a time because the summaries will summarize all the records in the find. And I want to get rid of the multiple 'list serial number' and just have a summarized version, so it looks like this:

(list serial number)(customers last name)(potential sum)(actual sum)(difference)

The next level of my summary problem is that I need to be able to find all open accounts, or all accounts for a particular year. But when I find multiple accounts the summary will summarize the whole group that has been found, and gives me outrageous numbers.

What I can't figure out is how to summarize the multiple records for each customer into one and then have each customer have it's own separate sum displayed indivdually. Then be able to list all open accounts together in a extended columnar report. So I want it to look like this:

1(date)(list serial number)(customers last name)(potential sum)(actual sum)(difference)

2(date)(list serial number)(customers last name)(potential sum)(actual sum)(difference)

3(date)(list serial number)(customers last name)(potential sum)(actual sum)(difference)

Does that make sense? I would be most grateful for any insite into this problem.

[Ugo DI LUCA]

Hi,

What makes sense to me is that you would need to use relationships and calculation to do all this.

But I'm so sure you'll need a Line Item for your business, and I'm so sure I cannot see it, that I'm confused about your file structure, that basically should be IMHO :

Products

Bridal Contact

Line Item

Requests

Purchase

(Invoices)

where your line item should be a Join File for your different relationships, as at least :

A Contact may have multiple product in his Request list

A product could be attached to many bridal Request list

A product could be attached to many bridal Purchases

.....

If you have one, your calcs should look like :

Sum (::LineItem - Total Purchased) where Line Item is a relationship from the Customer_Id to the Customer_Id in the Line Item.