more than 1 related record in summary

[MJK.NSW]

Hi, I only program occasionally and feel as though I've overcome this before... but can't recall how

I've 2 tables A and B, both can have more than 1 related record with the other, when I do a summary in A where B has more than 1 related record, I get the multiple A values (and therefore they all sum() ) they but I only want that value once, and visa-versa.

I know I can create 2 layouts but Ideally I'll like to be able to do it on 1

hope if explained it properly

thanks

[comment]

hope if explained it properly

No, actually it's very confusing. Why don't you use meaningful names for your tables and explain how they are related. Abstracts like A and B are difficult to follow - and I suspect they got you confused as well, because if you "do a summary in A" there is no way you can get "multiple A values".

[MJK.NSW]

yep, ok, sorry

3 tables - Job, Employee::Hours & Video::Minutes

for each job an employee might take some video

Jobs and Employees relate via a JobID field. Employees and Video relate via EmployeeID and JobID

mostly there will be one Employee record per Job, but multiple Video records per employee per job (video taken on different days by the same employee for the same job)

However, sometimes an employee might have a few goes at the same job so there might be multiple records in Employee table where the JobID is the same

I wanting to total the Video::Minutes and the Employee::Hours and display the summaries on the same layout

If I make Video the table for the layout, the video::minutes total correctly but where there are multiple video:minutes records for the same same job-employee the employee:hours will get duplicated and added together

Similarly, when I use the Employee table for the layout, the employee::hours sum correctly but where there multiple records for the same employee in the Employee table, the video::minutes will be duplicated and added together

I've been able to work it out summeriesing to an employee level by doing a count() and dividing the duplicated totals by that amount, but it doesn't work for the grand total

maybe I should be pulling (importing) all the data into a separate table and doing all the calcs from there - although one other thing to consider is the database is hosted and things like summaries, sorting, replace field contents and importing take loooooooots longer than doing it locally

as I said previously, I only program spasmodically so maybe I'm forgetting something bleedingly obvious

cheers

[comment]

Well, it's getting clearer, but some spots still need clarifying:

Jobs and Employees relate via a JobID field.

That would make sense if each employee worked on one job only. I suspect that's not true. If I am correct, the next question will be: how many employees can work on the same job?

However, sometimes an employee might have a few goes at the same job so there might be multiple records in Employee table where the JobID is the same

What exactly is a "go"? If there are multiple "goes" (or anything that is many) between an employee and a job, then you need another table to track these. In the Employees table, there should be one unique record for each employee - and similarly, the Jobs table should have one unique record for each job. No more, and no less.

If I am guessing correctly, you want to track the hours an employee spent on a job; for this you should have a separate Hours table with fields for:

• EmployeeID

• JobID

• Date

• Hours

which would be related to both Jobs (by JobID) and Employees (by EmployeeID).

I am not sure I understand the video thing, but perhaps the same table could also contain the video minutes. So a record in this table would say that on date X, employee Y worked on job Z for N hours, and during this time M minutes of video were shot.

[MJK.NSW]

unlimited can work on the same job, but in reality usually no more than about 5

sorry, "goes" mean that an employee might do some work on the job then sign off. Then some time later do some more work and sign off. If this happens there are 2 records for that employee in the Employee table for that job (JobID)

a separate Hours table !! ... I think that might be the go ... using calc fields not lookup ??

[MJK.NSW]

how do I create each new record in Hours table for each relevant record in the Employee and Video tables ? if I use calc fields for the employee::hours and the video::minutes data then I'm in the same position... I have to use summary fields in Employee and Video tables to collect ALL the relevant records. I can't use script triggers to add a new record as the user doesn't need to update the 1 specific field (there's 3 fields for video time (Hrs, Mins & Secs !))

[comment]

how do I create each new record in Hours table for each relevant record in the Employee and Video tables ?

One easy way is to enter the records through a portal to Hours placed on a layout of Employee. I suggest you download the demo file here:

http://www.fmforums.com/forum/showpost.php?post/246136/

(rename People to Employees, Organizations to Jobs and Affiliations to Hours)

I am afraid I didn't understand the rest of your message; you are listing all the things you cannot do - but you do not say what do you want to accomplish. I am also still unclear about the role of the Video table.

[MJK.NSW]

OK, what I've done is create a Hours table. Then a script goes to the Employee table, does it's find (as per user input) then goes through a loop setting variables for JobID, EmployeeID and hours and then to the Hours layout and adds new records, setting the 3 fields for each. When finished it then goes to the Video table, does the same find criteria and repeats the adding new records to the Hours table with the JobID, EmployeeID and hours.

I go to the layout based on Hours, sorted by JobID and do the various summaries, though I found to get averages I could not use the Summary fields, I needed to have calculation fields using getsummary() and dot the averages on those

it's a bit slow over the hosted site, especially if the find criteria finds lots of records, more than 500 seems be getting to cup of tea time, but it's only going to used once or twice a month

[MJK.NSW]

also, the Hours is just a temp in order to display the summaries / averages, the script first deletes all the records

[comment]

I am afraid I have lost you at this point.

goes through a loop setting variables for JobID, EmployeeID and hours and then to the Hours layout and adds new records, setting the 3 fields for each.

How does the script know how many records to create and what to put in each one?

[MJK.NSW]

goto Video table Layout

perform find

goto first record

loop

set variable $JobID

set variable $EmployeeID

set variiable $video

goto layout Hours

new record

set field JobID, $JobID

set field EmployeeId, $EmployeeID

set field Video length, $video

next record, exit after last

endloop

then do the same for the Employee table, collecting the EmployeeID, JobID and Hours data for each record in the found set

In Hours table I can then sort either by JobID or EmployeeID (depending on how I want it to look) and have the summaries calculating properly, one totaling the employee::hours and the other video::hours

as mentioned, I've got the average calc working eg Employee::hours/video::hours, but am still trying to come up with a calculation that will give an integer ranking for each employee's average eg 4,3,6,1 - it doesn't have to be sorted 1,2,3,4 etc (I posted a query in the Calculation forum)

[MJK.NSW]

Hi Comment, after digging around I found you've already given advice on doing rankings, as below, so I'll give that a go, thanks

Usually this is done by defining a self-join relationship matching the event (and age group, etc.) and comparing the results, e.g.

Scores::EventID = Scores 2::EventID

AND

Scores::AgeGroup = Scores 2::AgeGroup

AND

Scores::Score < Scores 2::Score

The rank is then given by a calculation field =

Count ( Scores 2::ScoreID ) + 1

[MJK.NSW]

mmm... the only problem is the "scores::score" in mine (Hours::average) is based summary fields which I can't index and I get a 'missing index' error ... grrrr