473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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.........task7......name9

14.........task2......name3
............................name5

15.........task1.......name2
.............task2.......name3
.............task3

(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
Feb 12 '08 #1
3 1265
MikeTheBike
639 Expert 512MB
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.........task7......name9

14.........task2......name3
............................name5

15.........task1.......name2
.............task2.......name3
.............task3

(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
Feb 12 '08 #2
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............hobby
-------------------------------------------
1........2/12/08.....joe..............eating
.
2........2/13/08.....mary...........drinking
..........................paul
.
3........2/14/08.....peter...........skiing
............................................scuba
............................................caving
.
4........2/16/08.....megan.........horses
............................................climbi ng
............................................pets
.......................... judy...........horses
............................................climbi ng
............................................pets
.

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...........climbing
............................................pets

Is this even possible?

Peter
Feb 12 '08 #3
FishVal
2,653 Expert 2GB
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.
Mar 2 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: AstrA | last post by:
Hi All Wondered if you could help. Basically I have 2 tables that contain all the data I want for my report, but I need to put it in a particular way and I need to display it in an ASP page...
3
by: Grim Reaper | last post by:
I know this is probably an easy question, but I could not find/figure it out. Basically, I am printing mailing labels with a "Sorting/Grouping" section that groups the label types together....
8
by: Greg | last post by:
ok, i would personally expect this to be a given, but for some reason it doesnt seem to be. i have a report that is always 7 pages long and i want the user to be able to view it and/or print it....
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
1
by: Mike Cooper | last post by:
Hi everyone, This is a tough one. I have a database full of solicitations, identifying a customer and recording initial call, first followup, second followup, etc. My boss want to be able to...
1
by: Takayla via AccessMonster.com | last post by:
I am lost and would appreciate anyone's help!! I have to make my current access database do billing. In theory that would be a * b = c, but I have a lot of variables and no idea how to program...
1
by: Rodo | last post by:
Hi all, I'm trying to generate a simple crystal report without a database. Several people mention the use of a dataset. Someone mention in a msdn forum that I could use the SetParameterValue to...
176
by: nw | last post by:
Hi, I previously asked for suggestions on teaching testing in C++. Based on some of the replies I received I decided that best way to proceed would be to teach the students how they might write...
0
by: sara | last post by:
Hi - I have a table keeping track of employees' jobs in a location. So, Store #1 Manager #353, AsstMgr #556 Store #2 Manager #776, AsstMgr #132 etc. The table is Date StoreNum MgrNum ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.