counting number of values and displaying in a tabl

[randy remark]

Hi I am struggling to get my head around this problem, let me first explain

I have 2 databases

1 is a database of activities (ie Football,Swimming etc)

and I have another database called bookings

in the booking database I have the following fields (just the relevant ones shown)

Name

MondayMorning

MondayAfternoon

TuesdayMorning

TuesdayAfternoon

(basically 2 fields for everyday of the week, morning and afternoon.)

The fields are ComboBoxes showing a valuelist of all the available activities from the activity field in the activity database)

I need to be able to produce a report showing in popularity order the activities, something like this

Football 1987

Swimming 1400

Cycling 1008

..

Walking 12

I assumed that this would be easy when i started this project but I am having trouble getting my head around it.

Any help would be hugely appreciated.

Cheers

mark

[Ugo DI LUCA]

Hi,

Without discussing your design and structure, you seem to have 14 relationship from File 1 (Activities) to File 2 (Booking).

Rel 1 - Activities:Activiity_Type::Booking:MondayMorning

Rel 2 - Activities:Activiity_Type::Booking:MondayAfternoon

Rel 3 - Activities:Activiity_Type::Booking:TuesdayMorning

....

So you could use the c_count1= Count (Rel1::MondayMorning) and c_count2 = Count (Rel2::MondayAfternoon)...

Then a bigger one with c_total = c_count1 + c_count2 +....c_count14 to get the total activities per week.

Then use a summary report, sorted by this calc to have it display the records from Activities by order.

[randy remark]

Hi, many thanks for replying but im still a little confused.

your method seems to be focused on the days and not the activity. Please note I don't want to sound rude, i just want to get my head around it.

Ok The structure of my database is very simple.

I simply have a 1 valuelist which is created using 1 relationship and every day(morn) and day(aft) use the same valuelist.

I can set up relationships for each day, and I can see how the count would work for a week etc.

However, i'm wanting a popularity report, based on the activity, time is irrelevant if you get what i mean. I may want to run the report on all the records, or apply a find on specific data, such as dates, or names, then run the report.

The other thing to consider is with the activities themselves, if a new activity is added to the activities database them it will be added to the valuelist.

Sorry to be a nuisance but i simply want to get my head around it.

Many Thanks

[Ugo DI LUCA]

Hi,

Sorry I didn't understood. I thought your day field holded a value-list of activities....

Im not very clear at the moment and I still don't understand your file structure

You have "activities" and a "day to day" db ?

How are records entered in the day to day db.

Is there a join file from fila 1 to file 2 ?

[randy remark]

Hi Let me explain

In the Activities Database the field we are working with is the Activity name field. The reason there is a seperate database for the activities is that their is other information such as location etc. A valuelist is then created from the field ActivityName. The database currently has 48 activities.

The daytoday database has a field for name, startdate, and then a 2 fields for everyday mon-sun. 1 for every morning and 1 for every afternoon.

To enter data into say field MonMorn,this is selected by means of a drop down combolist of activities.

This is the same for each of these fields they all use the valuelist created from another file.(activity)

Hope your with me so far.

It is a rolling week, so the actual dates don't apply (as in which monday in the year it is)

there is simply 1 record per person.

So lets say I have 87 people. 87 records in the daytoday database.

They each fill in which activities they want to to, and it only periodically changes, ie Paul Jones who does swimming on a thursday afternoon, now wants to do some painting.

The popularity report is to look at all the entrys in each field in the daytoday database and count each time each activity is requested.

I hope this now makes some sense.

I do appreciate your help on this.

Mark

[Ugo DI LUCA]

Hi,

Let start with what I understood so far...

The Activities are stored in a Activity file with 48 records.

The Members are stored in a Member file with 87 records.

Each of the 87 members does a selection of the unique activity they want and select the day (Mon to Sun) and period (morning-afternoon).

One member can only choose one activity, one day and one period.

So your DaytoDay file has 87 records in it also.

So it seems there is a easy One to One relationship.

Before going any further, are you sure you'll never have the case of multiple selection, that is that your file may need to be modified for a One to Many or a Many to Many relationship ?

It's easier to change your files from a Many to Many to a One to One, than the contrary.

Just a start.

BTW, what's your startdate ?

[randy remark]

No, sorry I only have 2 databases, the database that holds the member information ie name, startdate, also holds the activity selections for each person.

so record 1

name John Smith

startdate 12/01/02

monmorn Swimming

MonAfter Football

TuesMorn . No entry

TuesAfter Football

etc, etc

the startdate is just the date the person joined the club.

Hope this makes sense, All I need at this stage, is to be able to produce the popularity report.

Cheers

mark

[Ugo DI LUCA]

Hi,

So the first answer I provided will do it....

Rel 1 - Activities:Activiity_Type::Booking:MondayMorning

Rel 2 - Activities:Activiity_Type::Booking:MondayAfternoon

Rel 3 - Activities:Activiity_Type::Booking:TuesdayMorning

Rel4....Rel 14

You said

your method seems to be focused on the days and not the activity. Please note I don't want to sound rude, i just want to get my head around it.

I focused on the days because you day field is filled with the activities.

monmorn Swimming...

So summing each of the 14 Count(Relationship) will give you a total for each activities.

Now, for your table proble, just use a selfjoin and a portal using a new relationship with :

g_constant (=1) for the left side

n_constant (autoentered number field - indexed) = 1 for the right side.

The portal will hold the 48 lines. Just place in it the Total Activities...

Am I still missing something here ?