Hi, I´m with the same problem I

[Fursten]

Hi,

I´m with the same problem I said in other thread, however I had other ideias on how to solve them, I think.

Consider I have 3 tables.

Table1: CodNumber, Nºemployee, CodActivity, Quant, Cost
Table2: CodActivity, Descrition
Table3: CodNumber, NºEmployee, Action, NºHours

In my main report I have Table1 linked with Table2 and I print the list of all activities made by all the employees that had made some activity. I have a group by (CodActivity), like this:

CodActivity1
NºEmployee, Quant, Cost
"" "" ""
Sum(Quant*Cost)
CodActivity2
Nºemployee, Quant, Cost
"" "" ""
Sum("&quot
...etc....

Now, what I would like to have it was the Sum(hours) by CodActivity in the same main report, like this:

CodActivity1
NºEmployee, Quant, Cost
"""""""""
Sum(hours) Sum(Quant*Cost)

Problem: I can´t link Table1 with Table3 because becaue the relationship between thwm is many-to-many. This is a Analisys problem, however I nedd to solve it without changing the table structures.

How:

1) In a subreport I could get the sum(hours) for each CodActivity and use shared variables, but the problem is that I don´t even know how many CodActivities my report will have :| So I would need something like this:

For each CodActivity Sum(hours)

And then said in the main report I would need to said that to Group field (codActivity1) Sum(hours) = something ...etc...

I don´t see how :
2) Forget the subreport. Had the 3 tables to the report, link two of them (Table1 and Table2), and then calculate the sum(hours) like a running total, linkinh it with CodActivity, is this possible?How?because if I don´t link the three of them I will not be able to get table3´s fields :
Thank you

 

[malk]

What do you mean by many to many? What is the data like and how are you trying to link them?

 

[Fursten]

Hi

Many to many:

It means that a same employee could have a lot of records in table1 with the same CodNumber, the same Nºemployee and the same CodActivity.

At the same time, the same employee can have in Table3 a lot of records with the same CodNumber and the same NºEmployee, so if I link them I will get repeated recordsets... this si because the tables are not normalized However my work is not to change them but to make that report!

Thank you