Using a Relationship to show unique values

[par72]

Hi there,

I have DB with 400,000 records in it. To keep it simple lets say there are 4 fields in it

(a)=unique value

(=every record has one(there are about 150 different values)

©=every record has one (there are about 5,000 different values)

(d)=number (can be null)

I want to be able to select a (b)and then show all of the (c')'s related to the selected (: but only 1 of each of the ©'s, no duplicates. Then i would like to show the total of (d) for each of the unique ©'s. Then it gets more complicated but for now that would be a great start.

I have done this in the past with exports, scripts and lookups but i think there has to be a better way. I have searched the form but have failed in finding an answer.

Thaanks for any help.

[par72]

Hey again

I will try to be a little more specific to see if someone can help me out.

Table 1

ID Name Price Year

1 Joe 10 2000

2 Mike 5 2000

3 Mike 1 2001

4 Joe 20 2000

5 Joe 25 2003

6 Garry 1 2000

7 Garry 3 2002

8 Mike 30 2000

If a search for year 2000 was done i would like to display this in a layout in list view.

Name Count total_Price

Joe 2 30

Mike 2 35

Garry 1 1

I would like to be able to sort those results and have the opition for some scripts associated with each name. I have done it with exports but still think there is a better way.

Thanks for any help. I may be asking this in the wrong place or it may be a silly question.

[Fitch]

Assuming you have a table for People, then view your list in that table. Make a global field in People, gYear, and put it on the list layout so you can choose the year. Make a relationship to the Sales table or whatever that is that you listed above, based on People ID and on gYear. Make calculated fields for Count(SalesByYear::ID) and Sum(SalesByYear::Price). Put those fields on the layout. Hope that helps.

[par72]

Thanks Fitch, yes that relationship works fine if I search on only one field. Unfortunately there are several other fields in the main table that may be searched on at the same time. Then the relationship will not work. I was hopping to be able to search multiple fields (not always the same ones) then attain a unique name within the found set and all of the summed data based on name.

Sorry that my example was so clunky, my data is a little sensitive so I was trying to come up with an example that would amount to the same thing.