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
3 1279
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
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 2,653
Recognized Expert Specialist
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 so my queries got to be manual rather than an MS Excel/Query 'munge'.
To be honest, the report itself is very basic, so hopefully my ramble will
make sense. I need to report that shows 6 columns at best, 4 columns if
|
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. Also, I am using a "Report Header" to
show a count of how many total labels that are being printed.
Now, my problem is that without the "Report Header", the spacing is perfect.
But, when I add the Report Header, it gets shifted downwards and the...
|
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. not a problem,
one button to print preview, one to just print. the problem is just
that in the print preview, the user wants to be able to use the scroll
wheel on the mouse to scroll through the pages. i cant seem to figure
out how to enable that....
|
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 the moment the printed output is usually going to Word. It's turning into
an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.
|
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 generate a report showing a list of
customer who were call between two different dates that he types into
a form. That, I have done. My boss types in his two dates, the SQL
statement searches through the follow-up call fields for a date
| |
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 this
to make it work.
Table 1 - Rate Table
Customer Rate Commodity
ABC Co $225 Twine
ABC Co $500 Burlap
|
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 easily fill the fields in a simple
report. So. I looked at datasets and I think I'll try this way first. I'm
pretty new and I still don't understand much of this stuff. So. here is what
I did . and it is not working :-(.
1.-create new windows...
|
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 their own unit
test framework, and then in a lab session see if I can get them to
write their own. To give them an example I've created the following
UTF class (with a simple test program following). I would welcome and
suggestions on how anybody...
|
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 AsstMgrNum ...
Each time a "line up" is recorded, we put in the new date (as people
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |