My Brain hurts - Help required Please !

[davidpalmer]

Hi I have produced a membership database in FM 6 Mac & PC and I am stumped on how to solve the following problem:

The database holds details of members using the facilities of a gym I need to anylayse the data and produce a report to see if any members usage declines from week to week - if so they will be contacted to see why

I have all the data summarised I need exported to another database as follows:

Membership Number (Number Field)

Week Number Attended (Number field)

Amt of Times attended for that week number (Qty)

Persons Name (Text Field)

I need to compare say this weeks attendance with last weeks attendance and if their is a decline of 2 or more visits then flag it.

The problem is that this is going to be based on a 4 or 5 week cycle.

and some members dont always attend sequentially.

The list of data looks like this:

Memb No------Week No---------Qty-----------------Name

876122------------44-----------------2---------------Mr Smith

876144------------48-----------------6 ------------Mr Jones

876555------------47-----------------1------------Mr Alan

876144------------45-----------------7------------Miss Jones

876122------------49-----------------2------------Mr Smith

and so on ... I can sort the data and select a member but how can I get a comparison between the found data - and If i do - how can I get to the next member using a script to automate this ?

Theinformation in need something like this :

Member 876122 ( Mr Smith) has declined his usage from 4 visits in week 48 to 2 visits in week 4

Its the comparing of different records for the same person that has me stumped !

Any help would be really appreciated

David

[Kurt Knippel]

I might suggest that you store this data in another DB. Then you can match by member # and compare the current results to historical results.

[Philland]

If I understand what you want to accomplish correctly, you might try something along these lines;

create a self-relationship based on member ID,

create a "Attendance" calculation field - If "current" - 2 is equal to or greater than "history" then True otherwise False.

create additional calculation fields to display within a portal based on the self-relationship each field would be If "Attendance" = True then "member name", create a field like this for each field you want in the portal and then sort the portal by week number so blank records fall to the bottom.

You should see all cases of fallen attendance by member sorted by week. You should then also be able to set up a view by all true by week.

Either that or I've got it all wrong.