simple report idea - but I can't figure it out!

[peterkennett]

I have three tables, all linked.
Table 1 "job" has an autonumber ID field.
Table 2 "tasks" has a [job] field that is linked to [job:ID]
Table 3 "staff" has a [job] field that also links to [job:ID]

The purpose is to track multiple tasks and multiple staff who are working on a single job. There can be a random number of tasks per JOB, and a random number of staff per [ID] - so I have separate TASK and STAFF tables but they are linked to [ID]. The tasks and the staff are not linked. It all works great when entering and tracking data.

But now I need a report that lists all jobs, and also displays all tasks and staff that worked on that job.

Job#.....Tasks. .....Staff
-----------------------------------------------
13.........task 7......name9

14.........task 2......name3
............... .............na me5

15.........task 1.......name2
.............ta sk2.......name3
.............ta sk3

(dots added since spaces do not work here)

The problem I have is that even with grouping and playing with the "hide duplicates" option, I can't duplicate the above example. It either multiples the staff names when there are more than one task, or it multiplies the tasks when there are more than one staff!

So all I am trying to do is simply show all jobs, with lists of all tasks and all staff assigned to that job - with no duplicates.

Any help would be appreciated!

Peter

[MikeTheBike]

I have three tables, all linked.
Table 1 "job" has an autonumber ID field.
Table 2 "tasks" has a [job] field that is linked to [job:ID]
Table 3 "staff" has a [job] field that also links to [job:ID]

The purpose is to track multiple tasks and multiple staff who are working on a single job. There can be a random number of tasks per JOB, and a random number of staff per [ID] - so I have separate TASK and STAFF tables but they are linked to [ID]. The tasks and the staff are not linked. It all works great when entering and tracking data.

But now I need a report that lists all jobs, and also displays all tasks and staff that worked on that job.

Job#.....Tasks. .....Staff
-----------------------------------------------
13.........task 7......name9

14.........task 2......name3
............... .............na me5

15.........task 1.......name2
.............ta sk2.......name3
.............ta sk3

(dots added since spaces do not work here)

The problem I have is that even with grouping and playing with the "hide duplicates" option, I can't duplicate the above example. It either multiples the staff names when there are more than one task, or it multiplies the tasks when there are more than one staff!

So all I am trying to do is simply show all jobs, with lists of all tasks and all staff assigned to that job - with no duplicates.

Any help would be appreciated!

Peter

Hi

One though, have you tried using OUTER JOINS ??

That may provide a solution.


MTB

[peterkennett]

Yup, I just tried it, in every combo I could think of. I even created a separate query that joined two of the tables and then used it to link the third - no luck.

I still get duplicate entrees when one of the two linked tables has more than one entry. Hiding duplicates does nothing.

Example 2: three tables:
Maintable has two fields, ID and date
stafftable has two fields, ID and staff
hobbytable has two fields, ID and hobby

stafftable.ID is linked to Maintable.ID
hobytable.ID is linked to Maintable.ID

All I can get is:


ID.........date .........staff. ...........hobb y
-------------------------------------------
1........2/12/08.....joe..... .........eating
.
2........2/13/08.....mary.... .......drinking
............... ...........paul
.
3........2/14/08.....peter... ........skiing
............... ............... ..............s cuba
............... ............... ..............c aving
.
4........2/16/08.....megan... ......horses
............... ............... ..............c limbing
............... ............... ..............p ets
............... ........... judy........... horses
............... ............... ..............c limbing
............... ............... ..............p ets
.

Record 1, 2 and 3 print out perfect, because they have only one record in either the staff or task table or both.

But record 4 dupicates the task table since there are two or more records in the staff table. And hiding duplicates does not stop this.

Here's what I want record 4 to look like:

4........2/16/08.....megan... ......horses
............... ...........Judy ...........clim bing
............... ............... ..............p ets

Is this even possible?

Peter

[FishVal]

Hi, Peter.

What you are getting is actually how sql joins work.
When you join tables, database engine just give you all possible combinations of records satisfying criteria given in ON clause.
So if you have 1 record in MainTable, 2 records in stafftable and 3 records in hobbytable, then you'll receive 6 records, not 3.

Actually I don't see how it may be implemented but concatenating field values from stafftable and hobbytable. Read Producing a List from Multiple Records to get acquainted with technical aspects of this.

Regards,
Fish.