Query Problem

[BBAirborne]

Have about 15000 records in a table [activity] and in the table a person can have many instances of one activity; for example, Routine Fitness, Dental Exam, etc.. It is a one-to-many where the user can have many activities; however, the activities have a value, and that activity is capped at a certain value. For Example, Walking activitity earns you 3 points and you can end up with 300 but the cap is 100.

My problem is not reporting ALL the values or Sum. I have that in a report. My problem is staying within the cap value. For example, If activity= walking is greater than 100 then value is 100. I need to count/sum the activity and then trim in back to the cap value if it exceeds it.

Hope this makes sense. it is kicking my tail. Thanks in advance.

 

[WayneRyan]

BB,

Use two queries:

Query1:

Select PersonID, Activity, Sum(Minutes) As TotalMinutes
From Activity
Group By PersonID, Activity

That'll give you a breakdown of the total minutes for each person's
activities.

Then join that query to your table containing the caps.

Query2:

Select A.PersonID, 
       A.Activity,
       IIf(A.TotalMinutes > B.ActivityCap, B.ActivityCap, A.TotalMinutes)
From   Query1 As A Inner Join tblCaps As B On
         A.Activity = B.Activity

Need more info on your table structure, but that should do it,
Wayne

 

[BBAirborne]

Thank you for the help. Will that look at ALL users and ALL activities and group as such?

I would be happy to email dB with table. It is about 3mb in size if you would like to look at it.

 

[BBAirborne]

Okay!!!! Query 1 is a success!! Thank you. So for Query 2 to work I will need a table for the CAP info correct? That way it can compare? Please advise.

Once again... thank you!

 

[WayneRyan]

BB,

Yes, the cap table should have an entry for each Activity.

To join on Activity, the value in each table should be the same.

If you need to post your DB, you can do a Compact/Repair and then ZIP it
and post it here.

Wayne