Need Unique Entries From Across Tables

[tdahsu]

Hi,

I'm sorry if this is very basic but I haven't had much sleep lately.
I have three tables:

Machines
Processes
Process Instances

Machines is a listing of computers by name (has a unique id for each
machine).
Processes is a listing of processes running on each machine (has a
unique id for each process).
Process Instances is a table that lists each process with the process
id and machine id as well as other information related to the
particular process instance (has a unique id for each process
instance).

What I'd like is a query that will allow me to show, in a table:

Machine
- Process (with instance information)
- Process2 (with instance information)

 

[John Spencer MVP]

If I understand your table set up the following should identify processes that
have run on only one machine. Of course, you may need to add further data
into this query. For instance a where clause restricting this by date range if
you are interested in processes that only executed on one machine during the
current week.

SELECT ProcessID
FROM [Process Instances]
GROUP BY ProcessID
HAVING Max(MachineID) = Min(MachineID)

Using that you should be able to use a query like the following to list any
process that has only run on one machine.

SELECT *
FROM (Machines INNER JOIN [Process Instances] as PI
ON Machines.MachineID = PI.MachineID)
INNER JOIN Process
ON PI.ProcessID = Process.ProcessID
WHERE PI.ProcessID in
(SELECT ProcessID
FROM [Process Instances]
GROUP BY ProcessID
HAVING Max(MachineID) = Min(MachineID))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale_Fye via AccessMonster.com]

Let me make sure I've got this straight.

You want to know, by machine, what proceses are unique to that machine only?

How about if two machines out of 100 run the same "process"?

If you really want the first, then something like:

SELECT PI.Machine, PI.Process
FROM ProcessInstances as PI
INNER JOIN (SELECT PI2.Process
FROM ProcessInstances as PI2
GROUP BY PI2.Process
HAVING Count(PI2.Machine) = 1) as SP
ON PI.Process = SP.Process

HTH
Dale

 

[John Spencer MVP]

Dale,
Will that work if the same machine runs the process multiple times even though
no other machine runs the process?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale_Fye via AccessMonster.com]

John,

Was that a question or a statement? ;-)

Nope, it would not work if he has multiple instances of the same process
running on the same machine.

Dale

Dale,
Will that work if the same machine runs the process multiple times even though
no other machine runs the process?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Let me make sure I've got this straight.

[quoted text clipped - 67 lines]

 

[Dale_Fye via AccessMonster.com]

But you could probably do:

SELECT Distinct PI.Machine, PI.Process
FROM ProcessInstances as PI
INNER JOIN (SELECT PI2.Process
FROM (SELECT DISTINCT Machine, Process
FROM ProcessInstances) as PI2
GROUP BY PI2.Process
HAVING Count(PI2.Machine) = 1) as SP

Dale,
Will that work if the same machine runs the process multiple times even though
no other machine runs the process?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Let me make sure I've got this straight.

[quoted text clipped - 67 lines]

 

[tdahsu]

But you could probably do:

SELECT Distinct PI.Machine, PI.Process
FROM ProcessInstances as PI
INNER JOIN (SELECT PI2.Process
                   FROM (SELECT DISTINCT Machine, Process
                              FROM ProcessInstances) as PI2
                   GROUP BY PI2.Process
                   HAVING Count(PI2.Machine) = 1) as SP

Dale,
Will that work if the same machine runs the process multiple times even though
no other machine runs the process?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

[quoted text clipped - 67 lines]

Thanks.

--
HTH

Dale Fye

http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200907/1

Thanks guys - you've all been a very big help. This last piece from
Dale seems to have done the trick. It successfully gives me the
unique Process IDs and their associated Machine IDs - now I just have
to add a query that will take those results and show the associated
names to make it human readable.

Thanks again!