Brain Teaser (for me)

  • Thread starter Thread starter RobUCSD
  • Start date Start date
R

RobUCSD

Here's my query. It works as it is. But I need to limit the results to the
records that have the smallest difference between tblACT.fldTime and
tblVisit.OutOfRoom for each patient visit. A patient will have a unique
VistNo for each visit with many ACT's per visit. I only want the ACT's that
are closest to the OutOfRoom time.

Any help would be greatly appreciated. Thanks Rob

SELECT tblVisit.VisitNo, tblVisitComplications.fldVisitComplicationsNo,
tblACT.fldTime, tblACT.fldACT, tblVisit.OutOfRoom, tblVisit.OutOfRoom
FROM (tblVisit INNER JOIN tblACT ON tblVisit.VisitNo = tblACT.fldVisitNo)
INNER JOIN tblVisitComplications ON tblVisit.VisitNo =
tblVisitComplications.fldVisitNo
WHERE (((tblVisitComplications.fldVisitComplicationsNo)=4)) OR
(((tblVisitComplications.fldVisitComplicationsNo)=36));
 
You do not have any patient identification information. Add that to this
query ---
SELECT Min(DateDiff("n",[tblACT.fldTime],[tblVisit.OutOfRoom])) AS [Minumum
time]
FROM (tblVisit INNER JOIN tblACT ON tblVisit.VisitNo = tblACT.fldVisitNo)
INNER JOIN tblVisitComplications ON tblVisit.VisitNo =
tblVisitComplications.fldVisitNo
WHERE (((tblVisitComplications.fldVisitComplicationsNo)="4" Or
(tblVisitComplications.fldVisitComplicationsNo)="36"));
 
I get a bracket error when I run the below

SELECT Min(DateDiff("n",[tblACT.fldTime],[tblVisit.OutOfRoom])) AS
MinumumTime ,

KARL DEWEY said:
You do not have any patient identification information. Add that to this
query ---
SELECT Min(DateDiff("n",[tblACT.fldTime],[tblVisit.OutOfRoom])) AS [Minumum
time]
FROM (tblVisit INNER JOIN tblACT ON tblVisit.VisitNo = tblACT.fldVisitNo)
INNER JOIN tblVisitComplications ON tblVisit.VisitNo =
tblVisitComplications.fldVisitNo
WHERE (((tblVisitComplications.fldVisitComplicationsNo)="4" Or
(tblVisitComplications.fldVisitComplicationsNo)="36"));
--
KARL DEWEY
Build a little - Test a little


RobUCSD said:
Here's my query. It works as it is. But I need to limit the results to the
records that have the smallest difference between tblACT.fldTime and
tblVisit.OutOfRoom for each patient visit. A patient will have a unique
VistNo for each visit with many ACT's per visit. I only want the ACT's that
are closest to the OutOfRoom time.

Any help would be greatly appreciated. Thanks Rob

SELECT tblVisit.VisitNo, tblVisitComplications.fldVisitComplicationsNo,
tblACT.fldTime, tblACT.fldACT, tblVisit.OutOfRoom, tblVisit.OutOfRoom
FROM (tblVisit INNER JOIN tblACT ON tblVisit.VisitNo = tblACT.fldVisitNo)
INNER JOIN tblVisitComplications ON tblVisit.VisitNo =
tblVisitComplications.fldVisitNo
WHERE (((tblVisitComplications.fldVisitComplicationsNo)=4)) OR
(((tblVisitComplications.fldVisitComplicationsNo)=36));
Click to expand...
Click to expand...
 
RobUCSD said:
I get a bracket error when I run the below

SELECT Min(DateDiff("n",[tblACT.fldTime],[tblVisit.OutOfRoom])) AS
MinumumTime ,
Click to expand...


Remove the [ ]

They are hiding the table dot field syntax.
 
Thanks Marshall,

But when I remove the brackets as it is below, MS Access crashes. Am I doing
something wrong?

SELECT Min(DateDiff("n",tblACT.fldTime, tblVisit.OutOfRoom)) AS
MinumumTime

Marshall Barton said:
RobUCSD said:
I get a bracket error when I run the below

SELECT Min(DateDiff("n",[tblACT.fldTime],[tblVisit.OutOfRoom])) AS
MinumumTime ,
Click to expand...


Remove the [ ]

They are hiding the table dot field syntax.
Click to expand...
 
Thanks Marshall,

But when I remove the brackets as it is below, MS Access crashes. Am I doing
something wrong?
Click to expand...

Try

SELECT Min(DateDiff("n",[fldTime], [OutOfRoom])) AS MinumumTime

There's no need to specify the tablename if both fields are selected in the
Query design (and just so they're unique, and if you have fields named fldTime
in both tables you're probably making a mistake).
 
Thanks John, I'm not getting what I need from the query. Here is my original
post; what do you think?

Here's my query. It works as it is. But I need to limit the results to the
records that have the smallest difference between tblACT.fldTime and
tblVisit.OutOfRoom for each patient visit. A patient will have a unique
VistNo for each visit with many ACT's per visit. I only want the ACT's that
are closest to the OutOfRoom time.

Any help would be greatly appreciated. Thanks Rob

SELECT tblVisit.VisitNo, tblVisitComplications.fldVisitComplicationsNo,
tblACT.fldTime, tblACT.fldACT, tblVisit.OutOfRoom, tblVisit.OutOfRoom
FROM (tblVisit INNER JOIN tblACT ON tblVisit.VisitNo = tblACT.fldVisitNo)
INNER JOIN tblVisitComplications ON tblVisit.VisitNo =
tblVisitComplications.fldVisitNo
WHERE (((tblVisitComplications.fldVisitComplicationsNo)=4)) OR
(((tblVisitComplications.fldVisitComplicationsNo)=36));


John W. Vinson said:
Thanks Marshall,

But when I remove the brackets as it is below, MS Access crashes. Am I doing
something wrong?
Click to expand...

Try

SELECT Min(DateDiff("n",[fldTime], [OutOfRoom])) AS MinumumTime

There's no need to specify the tablename if both fields are selected in the
Query design (and just so they're unique, and if you have fields named fldTime
in both tables you're probably making a mistake).
Click to expand...
 
Back
Top