Query visning af problemer med kun nej i Action.

Problem
  ProblemNr    Autonumber
  ProblemNavn  Text

Followup
  FolloeupNr    Autonumber
  Tekst        Text
  Loest        Yes/No
  ProblemNrRef  Number

SELECT Folluwup.ProblemNrRef, Problem.Problembeskrivelse, Folluwup.Løst
FROM Problem INNER JOIN Folluwup ON Problem.ProblemNr = Folluwup.ProblemNrRef
WHERE (((Folluwup.Løst)=Yes));

Jeg antager at du har to tabeller med en en-til-mange sammenhæng. Jeg har vist tabellerne med deres datatyper ud for kolonnenavnene.
Jeg har ikke vist dato/tid, men det er blot ekstra kolonner i tabellen Followup, som jeg ser det - du kan selv tilføje dem i forespørgslen.

Denne løsning frasorterer ikke de Problemer hvor den sidst indtastede Followup er Løst med et No i visningen. Så vidt jeg kan se viser den alle followup med Yes.
Det er med at frasortere de problemer hvor den sidst indtastede opfølgning er resulteret i No der er det vanskelige.
Her er min SQL code.
SELECT [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FINDINGS.TAGNO, FINDINGS.REMARKS, FOLLOWUP.FOLLOWUP, FINDINGS.TYPE, FOLLOWUP.USER, FOLLOWUP.TIME, SHUTDOWN.[SDA NAME], FOLLOWUP.DESCR, FOLLOWUP.OUTSTANDING, FOLLOWUP.ACTION
FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB
WHERE(((FOLLOWUP.OUTSTANDING)=Yes))
ORDER BY [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE;

If you can send your dB then I will take a look

NOSPAMeksperten@santhell.dkNOSPAM

you need to remove NOSPAM

Du kan bruge en underforesp:

SELECT *
FROM problem
WHERE problemId
Not In (SELECT problem.problemId
          FROM problem,folowup
          WHERE (problem.problemId = folowup.problemid) AND ( løst=Yes));

Niels

Hej nih. Det virkede. Nu mangler jeg bare at det kun er den sidst indtastede followup der vises i min Query.
Som det er nu får jeg alle opfølgninger på finding vist under hinanden.

SELECT [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FINDINGS.TAGNO, FINDINGS.REMARKS, FOLLOWUP.FOLLOWUP, FINDINGS.TYPE, FOLLOWUP.USER, FOLLOWUP.TIME, SHUTDOWN.[SDA NAME], FOLLOWUP.DESCR, FOLLOWUP.OUTSTANDING, FOLLOWUP.ACTION
FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB
WHERE (((FINDINGS.FOLLOWUP) Not In (SELECT FOLLOWUP.FOLLOWUP FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB WHERE (FINDINGS.FOLLOWUP =  FOLLOWUP.FOLLOWUP) AND (FOLLOWUP.OUTSTANDING=No))))
ORDER BY [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FOLLOWUP.FOLLOWUP;

Så skal du grupere på max dato

SELECT problem.problemId, problem.tekst, Max(folowup.dato) AS MaksOfdato
FROM problem INNER JOIN folowup ON problem.problemId = folowup.problemid
WHERE (((problem.problemId) Not In
        (SELECT problem.problemId
          FROM problem,folowup WHERE
        (problem.problemId = folowup.problemid) AND ( løst=Yes))))
GROUP BY problem.problemId, problem.tekst;

Jeg kan ikke helt gennemskue din tabel struktur, så jeg viser princippet vha mlhave's tabeller

Niels

SELECT [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FINDINGS.TAGNO, FINDINGS.REMARKS, FOLLOWUP.FOLLOWUP, FINDINGS.TYPE, FOLLOWUP.USER, SHUTDOWN.[SDA NAME], FOLLOWUP.DESCR, FOLLOWUP.OUTSTANDING, FOLLOWUP.ACTION,  Max(FOLLOWUP.TIME) as makstid
FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB
WHERE (((FINDINGS.FOLLOWUP) Not In (SELECT FOLLOWUP.FOLLOWUP FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB WHERE (FINDINGS.FOLLOWUP =  FOLLOWUP.FOLLOWUP) AND (FOLLOWUP.OUTSTANDING=No))))
GROUP BY [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FINDINGS.TAGNO, FINDINGS.REMARKS, FOLLOWUP.FOLLOWUP, FINDINGS.TYPE, FOLLOWUP.USER, SHUTDOWN.[SDA NAME], FOLLOWUP.DESCR, FOLLOWUP.OUTSTANDING, FOLLOWUP.ACTION
ORDER BY [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FOLLOWUP.FOLLOWUP;

Et sku i tågen :)

Hej nih. Det er det samme som før. Kan du se hvad jeg gør forkert?
SELECT [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FINDINGS.TAGNO, FINDINGS.REMARKS, FOLLOWUP.FOLLOWUP, FINDINGS.TYPE, FOLLOWUP.USER, FOLLOWUP.TIME, SHUTDOWN.[SDA NAME], FOLLOWUP.DESCR, FOLLOWUP.OUTSTANDING, FOLLOWUP.ACTION, Max(FOLLOWUP.TIME) AS MaksOfTIME
FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB
WHERE (((FINDINGS.FOLLOWUP) Not In (SELECT FOLLOWUP.FOLLOWUP
FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB
WHERE (FINDINGS.FOLLOWUP =  FOLLOWUP.FOLLOWUP) AND (FOLLOWUP.OUTSTANDING=No))) AND ((SHUTDOWN.[SDA NAME]) Like "DAE*") AND ((FOLLOWUP.ACTION)="OFF"))
GROUP BY [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FINDINGS.TAGNO, FINDINGS.REMARKS, FOLLOWUP.FOLLOWUP, FINDINGS.TYPE, FOLLOWUP.USER, FOLLOWUP.TIME, SHUTDOWN.[SDA NAME], FOLLOWUP.DESCR, FOLLOWUP.OUTSTANDING, FOLLOWUP.ACTION
ORDER BY [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FOLLOWUP.FOLLOWUP;

folowuptabel har den et autonummer felt ??

Så skal du nemlig finde max autonummerfelt i folowup tabellen OG fjerne de andre felter fra foresp.

Herefter laver du en ny foresp. der er bygger den vi lige har lavet og din folowtabel hvor du kan vise alle felterne.

SELECT [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FINDINGS.TAGNO, FINDINGS.REMARKS,  FINDINGS.TYPE, FOLLOWUP.TIME, SHUTDOWN.[SDA NAME], Max(FOLLOWUP.id) AS MaksOfid
FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB
WHERE (((FINDINGS.FOLLOWUP) Not In (SELECT FOLLOWUP.FOLLOWUP
FROM SHUTDOWN INNER JOIN (([CE DIAGRAM] INNER JOIN FINDINGS ON [CE DIAGRAM].CE = FINDINGS.CE) INNER JOIN FOLLOWUP ON FINDINGS.FOLLOWUP = FOLLOWUP.FOLLOWUP) ON SHUTDOWN.RP_JOB = FINDINGS.RP_JOB
WHERE (FINDINGS.FOLLOWUP =  FOLLOWUP.FOLLOWUP) AND (FOLLOWUP.OUTSTANDING=No))) AND ((SHUTDOWN.[SDA NAME]) Like "DAE*") AND ((FOLLOWUP.ACTION)="OFF"))
GROUP BY [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FINDINGS.TAGNO, FINDINGS.REMARKS,  FINDINGS.TYPE, FOLLOWUP.TIME, SHUTDOWN.[SDA NAME]
ORDER BY [CE DIAGRAM].[CE DIAGRAM], FINDINGS.CE_LINE, FOLLOWUP.FOLLOWUP;