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

 

[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

 

[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

Message posted via AccessMonster.comhttp://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!