calculations from information calculated in the report

[MonkeyMaster]

calculations in the report

alright, I have a table. It has the ClientID field, the AmountOffered field and the AmountClaimed field. for each ClientID there is one AmountClaimed amount and several different AmountClaimed amounts. To make this possible in the table (which I did not design) there are duplicate rows containing ClientID and AmountOffered for each AmountClaimed entry.

I need to find out the total amount for AmountClaimed, which is easy...but I also need to find out the total for AmountOffered, which I don't know how to get access to count since there are multiple for each ClientID.

I need to be able to get the sum of AmountClaimed, and the sum of ONLY ONE for each ClientID of AmountOffered.

I tried to group a report, and I hid the duplicates, but since all the calculations are done from the underlying table it gives me the total of them all. Is there some way I can set it so it only calculates from non hidden entries? Alternatively, is there a way I can calculate the grand total as the sum of the group footer total?

if anyone can help me I would really appreciate it.

 

[Rich]

It would make more sense to sort out the table structure first, is that option available to you?

 

[MonkeyMaster]

unfortunatly I cannot alter the tables or their relationships. I work in an arm of the Canadian Government and it's their baby.

they just tell us to pull stats...but it's the financial arm that wants them, so they have no idea how easy or difficult and given task is.

 

[Rich]

Is that several Amounts claimed or several Amounts offered?

 

[MonkeyMaster]

the underlying table looks something like this

ClientID AmountOffered AmountClaimed

2031 $1200. $800
2032 $1500. $230
2032 $1500. $270
2032 $1500. $875
2033 $1385. $650
2033 $1385. $690

the number in the AmountOffered field is duplicated along with the client ID, but it is actually only offered once per client ID. I need to get the sum of the amount offered with no duplication for extra ClientID's.
I also need on the same report the total for Amount claimed, but that was easy enough.
Currently I have the report grouped by clientID, but while I can hid the duplicate values in AmountOffered, I cannot sum them without the hidden ones.
I hope that clairified what I was asking a little.

 

[Rich]

Why not use a query to select the Unique Claim ID and First claim offered base the report on that and use a subreport for amounts claimed, link by ClaimID

 

[MonkeyMaster]

sorry I have not replied to your post Rich, I am west of you, so I went home from work around 6:00pm (my time)

Perhaps I am misinterpreting what you are saying. but as you can see from my example, there are duplicates in the ClientID as well. I don't know how to make a query to take only the first selection of any given ID.

 

[Rich]

Would it have been better if I'd moved to the Can. Rockies
I don't have a structure like this to work with, if you can send me some sample data in 97 I'll take a look for you.

 

[MonkeyMaster]

Thank you for the offer...though there are two barriers.

I don't have '97 and when try to convert it tells me that I am missing at least one object library that will have to be fixed in 97 before it will work. I can convert anyway (I don't have much experience in convertions, so maybe missing object libraries is not as big a deal to fix as it sounds) or send in 2000 and let you mess with it...your call.

I also have no way (that I know of) to send it to you (your email was blocked in your profile). If I can send it through the forum in a way I don't know about that would be great, otherwise, if you don't want to post your email you can send it to me at directorjlancaster@yahoo.ca and I will reply.

Thank you very much for being willing to help me out, I think that is really fantastic, and I appreciate it a lot.

 

[Rich]

Unfortunately, or fortunately as the case may be I don't have A2k so this example is in 97, it should be roughly what you looking for

 

[MonkeyMaster]

Thank you so much for your help.
I will let you know how it works.

 

[MonkeyMaster]

I just wanted you to know that the file oyu sent me was exactly what I was looking for. I really appreciate your help, it would have taken forever (perhaps literally) to find that myself. What I was missing was the simple thing...I did not know about the First sort by feature in queries. Anyway, now I know and thanks again!