Min

[Guest]

*If you know of a posting that can assist me, please advise. Otherwise, let
me know if you have a suggestion for this.

I have a table set-up with about 20 fields. Two of the fields include a 9
digit number, which appear more than once, and a cycle time. Ex:

Number Cycle Time
123456789 5
123456789 14
536857429 6
987654321 9
987654321 18

I need to use Min, or some other function, to say "if 123456789 is duplicate
in this table, ONLY show me the lowest cycle time".

Therefore, I would only be shown the following from the table above:

Number Cycle Time
123456789 5
536857429 6
987654321 9

Any suggestions?

 

[Dirk Goldgar]

*If you know of a posting that can assist me, please advise.
Otherwise, let me know if you have a suggestion for this.

I have a table set-up with about 20 fields. Two of the fields
include a 9 digit number, which appear more than once, and a cycle
time. Ex:

Number Cycle Time
123456789 5
123456789 14
536857429 6
987654321 9
987654321 18

I need to use Min, or some other function, to say "if 123456789 is
duplicate in this table, ONLY show me the lowest cycle time".

Therefore, I would only be shown the following from the table above:

Number Cycle Time
123456789 5
536857429 6
987654321 9

Any suggestions?

Do you need an updatable query? If not, you can just use SQL like this:

SELECT [Number], Min([CycleTime] As MinCycle
FROM YourTable
GROUP BY [Number];

 

[RuralGuy]

*If you know of a posting that can assist me, please advise.
Otherwise, let me know if you have a suggestion for this.

I have a table set-up with about 20 fields. Two of the fields include
a 9 digit number, which appear more than once, and a cycle time. Ex:

Number Cycle Time
123456789 5
123456789 14
536857429 6
987654321 9
987654321 18

I need to use Min, or some other function, to say "if 123456789 is
duplicate in this table, ONLY show me the lowest cycle time".

Therefore, I would only be shown the following from the table above:

Number Cycle Time
123456789 5
536857429 6
987654321 9

Any suggestions?

Along with Dirk's suggestion I think you could use:

SELECT DISTINCT [Number], [CycleTime]
FROM YourTable
GROUP BY [Number]
ORDER BY [Number], [CycleTime]

An ascending [CycleTime] will give you the [Min] first and DISTINCT will
keep from duplicating [Number]

Or at least that is another starting point for you.

hth

 

[Lynn Trapp]

I'm afraid that won't work, RuralGuy. DISTINCT will prevent duplicates of
BOTH [Number] AND [Cycle Time], but not of any one of them individually.
Thus in the example offered both of the following are DISTINCT.



--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

*If you know of a posting that can assist me, please advise.
Otherwise, let me know if you have a suggestion for this.

I have a table set-up with about 20 fields. Two of the fields include
a 9 digit number, which appear more than once, and a cycle time. Ex:

Number Cycle Time
123456789 5
123456789 14
536857429 6
987654321 9
987654321 18

I need to use Min, or some other function, to say "if 123456789 is
duplicate in this table, ONLY show me the lowest cycle time".

Therefore, I would only be shown the following from the table above:

Number Cycle Time
123456789 5
536857429 6
987654321 9

Any suggestions?

Along with Dirk's suggestion I think you could use:

SELECT DISTINCT [Number], [CycleTime]
FROM YourTable
GROUP BY [Number]
ORDER BY [Number], [CycleTime]

An ascending [CycleTime] will give you the [Min] first and DISTINCT will
keep from duplicating [Number]

Or at least that is another starting point for you.

hth

 

[Guest]

Dirk,

Thank you for your help, but I'm not sure if this will work. As of right
now, I have a query written that takes the fields and data from multiple
tables and finds duplicates. The cycle time is a separate field in the "find
duplicates" query that is populated based on a formula created from the other
fields. I have tried just selecting the totals button and changing it to
Min, but I can't quite get it.

Do you think your initial suggestion will work, even though my SQL is about
20 lines long? (Referring to each of the fields and formulas?)

Thank you!

*If you know of a posting that can assist me, please advise.
Otherwise, let me know if you have a suggestion for this.

I have a table set-up with about 20 fields. Two of the fields
include a 9 digit number, which appear more than once, and a cycle
time. Ex:

Number Cycle Time
123456789 5
123456789 14
536857429 6
987654321 9
987654321 18

I need to use Min, or some other function, to say "if 123456789 is
duplicate in this table, ONLY show me the lowest cycle time".

Therefore, I would only be shown the following from the table above:

Number Cycle Time
123456789 5
536857429 6
987654321 9

Any suggestions?

Do you need an updatable query? If not, you can just use SQL like this:

SELECT [Number], Min([CycleTime] As MinCycle
FROM YourTable
GROUP BY [Number];

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

Thank you for your help, but I'm not sure if this will work. As of
right now, I have a query written that takes the fields and data from
multiple tables and finds duplicates. The cycle time is a separate
field in the "find duplicates" query that is populated based on a
formula created from the other fields. I have tried just selecting
the totals button and changing it to Min, but I can't quite get it.

Do you think your initial suggestion will work, even though my SQL is
about 20 lines long? (Referring to each of the fields and formulas?)

I'm not sure what you're really after, at this point. The SQL I posted
would work, I think, if you used your original find-duplicates query as
the "table" in the query, but it that's not the same as modifying the
find-duplicates query itself.

Can you describe the actual source tables and what you ultimately want
to get out of them?

 

[RuralGuy]

I'm afraid that won't work, RuralGuy. DISTINCT will prevent duplicates
of BOTH [Number] AND [Cycle Time], but not of any one of them
individually. Thus in the example offered both of the following are
DISTINCT.

Thanks Lynn. I knew I should have tried it in a test. I'll never stop
learning. It is what I like best!

 

[Lynn Trapp]

You're welcome

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

I'm afraid that won't work, RuralGuy. DISTINCT will prevent duplicates
of BOTH [Number] AND [Cycle Time], but not of any one of them
individually. Thus in the example offered both of the following are
DISTINCT.

123456789 5
123456789 14

Thanks Lynn. I knew I should have tried it in a test. I'll never stop
learning. It is what I like best!

 

[Guest]

This is related to insurance. (if that helps at all)

The find duplicates query examines a table that includes the case number,
holiday 1, holiday 2, up to holiday 5, and a cycle time field with a formula
that takes the number of days it takes someone to complete a case and
subtracts the holidays from that number, so the individuals do not have those
days that they're off added on to their time service. Case numbers may come
up as duplicates because of reissues, etc. I want a query that will compare
the case numbers and if there are any duplicates (which there are always at
least 200) I only want to see the instance that shows the lowest number of
days for time service. (Right now I am having to go through hundreds of
duplicate case numbers and delete, manually, all the duplicates except the
one with the lowest cycle time)

I just don't know how to say "if this case number appears more than once,
show me the one with the lowest cycle time".

Does that help?

 

[Dirk Goldgar]

This is related to insurance. (if that helps at all)

The find duplicates query examines a table that includes the case
number, holiday 1, holiday 2, up to holiday 5, and a cycle time field
with a formula that takes the number of days it takes someone to
complete a case and subtracts the holidays from that number, so the
individuals do not have those days that they're off added on to their
time service. Case numbers may come up as duplicates because of
reissues, etc. I want a query that will compare the case numbers and
if there are any duplicates (which there are always at least 200) I
only want to see the instance that shows the lowest number of days
for time service. (Right now I am having to go through hundreds of
duplicate case numbers and delete, manually, all the duplicates
except the one with the lowest cycle time)

I just don't know how to say "if this case number appears more than
once, show me the one with the lowest cycle time".

Does that help?

Sorry for the delay in replying, Melissa -- I ran (rather hard) into a
deadline at work.

Your explanation does help, but I'd like to see the SQL. Your query is
probably complicated by the fact that there are 5 holiday fields in the
table. Repeating fields like that are usually better off "normalized"
into separate records in a related table. But anyway, if you'll post
the SQL I may be able to figure out how to modify it to get you what you
want.

 

[Guest]

Right after the holidays (HD1, etc) you will see CycleTime. All I want to do
is display the lowest cycle time per case number. (Due to those duplicates.)
Thanks in advance.

SELECT [Time Service Table].CaseNumber, [Time Service Table].Dist, [Time
Service1]-([HD1]+[HD2]+[HD3]+[HD4]+[HD5]) AS CycleTime, IIf([Requirement
AURAINT_Reviewed] Is Null,[DOT],[Requirement AURAINT_Reviewed]) AS Start,
IIf([Requirement CAPPVD_Reviewed] Is Null,[Process Date],IIf([Process
Date]<=[Requirement CAPPVD_Reviewed],[Process Date],[Requirement
CAPPVD_Reviewed])) AS ThisDate, IIf([Requirement SPLCOF_Reviewed] Is
Null,[ThisDate],IIf([ThisDate]<=[Requirement
SPLCOF_Reviewed],[ThisDate],[Requirement SPLCOF_Reviewed])) AS ThisDate1,
IIf([Requirement APPROVAL_Reviewed] Is
Null,[ThisDate1],IIf([ThisDate1]<=[Requirement
APPROVAL_Reviewed],[ThisDate1],[Requirement APPROVAL_Reviewed])) AS
ThisDate3, IIf([Requirement POLREC_Reviewed] Is
Null,[ThisDate3],IIf([ThisDate3]<=[Requirement
POLREC_Reviewed],[ThisDate3],[Requirement POLREC_Reviewed])) AS DFCT,
[DFCT]-[START] AS [Time Service1], IIf([Start]<#11/25/2004# And
#11/25/2004#<[DFCT],2,0) AS HD1, IIf([Start]<#12/24/2004# And
#12/24/2004#<[DFCT],1,0) AS HD2, IIf([Start]<#12/31/2004# And
#12/31/2004#<[DFCT],1,0) AS HD3, IIf([Start]<#1/17/2005# And
#1/17/2005#<[DFCT],1,0) AS HD4, IIf([Start]<#2/21/2005# And
#2/21/2005#<[DFCT],1,0) AS HD5, [Time Service Table].[Site Code], [Time
Service Table].DOT, [Time Service Table].[PROCESS DATE], [Time Service
Table].[Time Service], [Time Service Table].IGO_NOGO, [Time Service
Table].[Special Project], [Time Service Table].[Requirement
Approval_Reviewed], [Time Service Table].[Requirement AURAINT_Reviewed] AS
Expr1, [Time Service Table].[Requirement CAPPVD_Reviewed], [Time Service
Table].[Requirement PolRec_Reviewed], [Time Service Table].[Requirement
SplcOf_Reviewed]
FROM [Time Service Table]
WHERE ((([Time Service Table].CaseNumber) In (SELECT [CaseNumber] FROM [Time
Service Table] As Tmp GROUP BY [CaseNumber] HAVING Count(*)>1 )))
ORDER BY [Time Service Table].CaseNumber;

 

[Dirk Goldgar]

Right after the holidays (HD1, etc) you will see CycleTime. All I
want to do is display the lowest cycle time per case number. (Due to
those duplicates.) Thanks in advance.

SELECT [Time Service Table].CaseNumber, [Time Service Table].Dist,
[Time Service1]-([HD1]+[HD2]+[HD3]+[HD4]+[HD5]) AS CycleTime,
IIf([Requirement AURAINT_Reviewed] Is Null,[DOT],[Requirement
AURAINT_Reviewed]) AS Start, IIf([Requirement CAPPVD_Reviewed] Is
Null,[Process Date],IIf([Process Date]<=[Requirement
CAPPVD_Reviewed],[Process Date],[Requirement CAPPVD_Reviewed])) AS
ThisDate, IIf([Requirement SPLCOF_Reviewed] Is
Null,[ThisDate],IIf([ThisDate]<=[Requirement
SPLCOF_Reviewed],[ThisDate],[Requirement SPLCOF_Reviewed])) AS
ThisDate1, IIf([Requirement APPROVAL_Reviewed] Is
Null,[ThisDate1],IIf([ThisDate1]<=[Requirement
APPROVAL_Reviewed],[ThisDate1],[Requirement APPROVAL_Reviewed])) AS
ThisDate3, IIf([Requirement POLREC_Reviewed] Is
Null,[ThisDate3],IIf([ThisDate3]<=[Requirement
POLREC_Reviewed],[ThisDate3],[Requirement POLREC_Reviewed])) AS DFCT,
[DFCT]-[START] AS [Time Service1], IIf([Start]<#11/25/2004# And
#11/25/2004#<[DFCT],2,0) AS HD1, IIf([Start]<#12/24/2004# And
#12/24/2004#<[DFCT],1,0) AS HD2, IIf([Start]<#12/31/2004# And
#12/31/2004#<[DFCT],1,0) AS HD3, IIf([Start]<#1/17/2005# And
#1/17/2005#<[DFCT],1,0) AS HD4, IIf([Start]<#2/21/2005# And
#2/21/2005#<[DFCT],1,0) AS HD5, [Time Service Table].[Site Code],
[Time Service Table].DOT, [Time Service Table].[PROCESS DATE], [Time
Service Table].[Time Service], [Time Service Table].IGO_NOGO, [Time
Service Table].[Special Project], [Time Service Table].[Requirement
Approval_Reviewed], [Time Service Table].[Requirement
AURAINT_Reviewed] AS Expr1, [Time Service Table].[Requirement
CAPPVD_Reviewed], [Time Service Table].[Requirement PolRec_Reviewed],
[Time Service Table].[Requirement SplcOf_Reviewed]
FROM [Time Service Table]
WHERE ((([Time Service Table].CaseNumber) In (SELECT [CaseNumber]
FROM [Time Service Table] As Tmp GROUP BY [CaseNumber] HAVING
Count(*)>1 )))
ORDER BY [Time Service Table].CaseNumber;

Sorry for the delay in replying, Melissa -- I ran (rather hard) into
a deadline at work.

Your explanation does help, but I'd like to see the SQL. Your query
is probably complicated by the fact that there are 5 holiday fields
in the table. Repeating fields like that are usually better off
"normalized" into separate records in a related table. But anyway,
if you'll post the SQL I may be able to figure out how to modify it
to get you what you want.

I'm not in a good position to test this, but here's how I think I'd go
about it.

Let's start by making life simpler, and create a saved query that
performs all the calculations. I'm not entirely sure which of all those
fields you need for your current purpose, so for now I'll just take them
all. Create a query with this SQL (copied from part of your original
query):

------------start of SQL -------------
SELECT [Time Service Table].CaseNumber, [Time Service Table].Dist, [Time
Service1]-([HD1]+[HD2]+[HD3]+[HD4]+[HD5]) AS CycleTime, IIf([Requirement
AURAINT_Reviewed] Is Null,[DOT],[Requirement AURAINT_Reviewed]) AS
Start,
IIf([Requirement CAPPVD_Reviewed] Is Null,[Process Date],IIf([Process
Date]<=[Requirement CAPPVD_Reviewed],[Process Date],[Requirement
CAPPVD_Reviewed])) AS ThisDate, IIf([Requirement SPLCOF_Reviewed] Is
Null,[ThisDate],IIf([ThisDate]<=[Requirement
SPLCOF_Reviewed],[ThisDate],[Requirement SPLCOF_Reviewed])) AS
ThisDate1,
IIf([Requirement APPROVAL_Reviewed] Is
Null,[ThisDate1],IIf([ThisDate1]<=[Requirement
APPROVAL_Reviewed],[ThisDate1],[Requirement APPROVAL_Reviewed])) AS
ThisDate3, IIf([Requirement POLREC_Reviewed] Is
Null,[ThisDate3],IIf([ThisDate3]<=[Requirement
POLREC_Reviewed],[ThisDate3],[Requirement POLREC_Reviewed])) AS DFCT,
[DFCT]-[START] AS [Time Service1], IIf([Start]<#11/25/2004# And
#11/25/2004#<[DFCT],2,0) AS HD1, IIf([Start]<#12/24/2004# And
#12/24/2004#<[DFCT],1,0) AS HD2, IIf([Start]<#12/31/2004# And
#12/31/2004#<[DFCT],1,0) AS HD3, IIf([Start]<#1/17/2005# And
#1/17/2005#<[DFCT],1,0) AS HD4, IIf([Start]<#2/21/2005# And
#2/21/2005#<[DFCT],1,0) AS HD5, [Time Service Table].[Site Code], [Time
Service Table].DOT, [Time Service Table].[PROCESS DATE], [Time Service
Table].[Time Service], [Time Service Table].IGO_NOGO, [Time Service
Table].[Special Project], [Time Service Table].[Requirement
Approval_Reviewed], [Time Service Table].[Requirement AURAINT_Reviewed]
AS
Expr1, [Time Service Table].[Requirement CAPPVD_Reviewed], [Time Service
Table].[Requirement PolRec_Reviewed], [Time Service Table].[Requirement
SplcOf_Reviewed]
FROM [Time Service Table];
------------end of SQL -------------

You may have to clean that up a bit. All it is is the original query,
minus its WHERE and ORDER BY clauses. Save this query as
"qryTimeService" -- or whatever other name you want, but I'll use that
name here.

Now, if we wanted to select from this query each unique CaseNumber with
its smallest cycle time, we could write a query like this:

SELECT CaseNumber, Min(CycleTime) As MinCycle
FROM qryTimeService;

What you really want is only those records from qryTimeService that have
CaseNumbers in the result set from the above query. That can be
achieved by SQL like this:

SELECT DISTINCT qryTimeService.*
FROM
qryTimeService
INNER JOIN
(SELECT
CaseNumber,
Min(CycleTime) As MinCycle
FROM qryTimeService)
As T
ON qryTimeService.CaseNumber = T.CaseNumber
AND qryTimeService.CycleTime = T.MinCycle;

At least, I think it can, but I'm no great SQL wizard, and I may have
made a mistake.

 

[Guest]

Dirk,

Thank you for your help! I will try it out tomorrow, or as soon as I can,
and get back to you after I've tested it.

Right after the holidays (HD1, etc) you will see CycleTime. All I
want to do is display the lowest cycle time per case number. (Due to
those duplicates.) Thanks in advance.

SELECT [Time Service Table].CaseNumber, [Time Service Table].Dist,
[Time Service1]-([HD1]+[HD2]+[HD3]+[HD4]+[HD5]) AS CycleTime,
IIf([Requirement AURAINT_Reviewed] Is Null,[DOT],[Requirement
AURAINT_Reviewed]) AS Start, IIf([Requirement CAPPVD_Reviewed] Is
Null,[Process Date],IIf([Process Date]<=[Requirement
CAPPVD_Reviewed],[Process Date],[Requirement CAPPVD_Reviewed])) AS
ThisDate, IIf([Requirement SPLCOF_Reviewed] Is
Null,[ThisDate],IIf([ThisDate]<=[Requirement
SPLCOF_Reviewed],[ThisDate],[Requirement SPLCOF_Reviewed])) AS
ThisDate1, IIf([Requirement APPROVAL_Reviewed] Is
Null,[ThisDate1],IIf([ThisDate1]<=[Requirement
APPROVAL_Reviewed],[ThisDate1],[Requirement APPROVAL_Reviewed])) AS
ThisDate3, IIf([Requirement POLREC_Reviewed] Is
Null,[ThisDate3],IIf([ThisDate3]<=[Requirement
POLREC_Reviewed],[ThisDate3],[Requirement POLREC_Reviewed])) AS DFCT,
[DFCT]-[START] AS [Time Service1], IIf([Start]<#11/25/2004# And
#11/25/2004#<[DFCT],2,0) AS HD1, IIf([Start]<#12/24/2004# And
#12/24/2004#<[DFCT],1,0) AS HD2, IIf([Start]<#12/31/2004# And
#12/31/2004#<[DFCT],1,0) AS HD3, IIf([Start]<#1/17/2005# And
#1/17/2005#<[DFCT],1,0) AS HD4, IIf([Start]<#2/21/2005# And
#2/21/2005#<[DFCT],1,0) AS HD5, [Time Service Table].[Site Code],
[Time Service Table].DOT, [Time Service Table].[PROCESS DATE], [Time
Service Table].[Time Service], [Time Service Table].IGO_NOGO, [Time
Service Table].[Special Project], [Time Service Table].[Requirement
Approval_Reviewed], [Time Service Table].[Requirement
AURAINT_Reviewed] AS Expr1, [Time Service Table].[Requirement
CAPPVD_Reviewed], [Time Service Table].[Requirement PolRec_Reviewed],
[Time Service Table].[Requirement SplcOf_Reviewed]
FROM [Time Service Table]
WHERE ((([Time Service Table].CaseNumber) In (SELECT [CaseNumber]
FROM [Time Service Table] As Tmp GROUP BY [CaseNumber] HAVING
Count(*)>1 )))
ORDER BY [Time Service Table].CaseNumber;

This is related to insurance. (if that helps at all)

The find duplicates query examines a table that includes the case
number, holiday 1, holiday 2, up to holiday 5, and a cycle time
field with a formula that takes the number of days it takes someone
to complete a case and subtracts the holidays from that number, so
the individuals do not have those days that they're off added on to
their time service. Case numbers may come up as duplicates because
of reissues, etc. I want a query that will compare the case
numbers and if there are any duplicates (which there are always at
least 200) I only want to see the instance that shows the lowest
number of days for time service. (Right now I am having to go
through hundreds of duplicate case numbers and delete, manually,
all the duplicates except the one with the lowest cycle time)

I just don't know how to say "if this case number appears more than
once, show me the one with the lowest cycle time".

Does that help?

Sorry for the delay in replying, Melissa -- I ran (rather hard) into
a deadline at work.

Your explanation does help, but I'd like to see the SQL. Your query
is probably complicated by the fact that there are 5 holiday fields
in the table. Repeating fields like that are usually better off
"normalized" into separate records in a related table. But anyway,
if you'll post the SQL I may be able to figure out how to modify it
to get you what you want.

I'm not in a good position to test this, but here's how I think I'd go
about it.

Let's start by making life simpler, and create a saved query that
performs all the calculations. I'm not entirely sure which of all those
fields you need for your current purpose, so for now I'll just take them
all. Create a query with this SQL (copied from part of your original
query):

------------start of SQL -------------
SELECT [Time Service Table].CaseNumber, [Time Service Table].Dist, [Time
Service1]-([HD1]+[HD2]+[HD3]+[HD4]+[HD5]) AS CycleTime, IIf([Requirement
AURAINT_Reviewed] Is Null,[DOT],[Requirement AURAINT_Reviewed]) AS
Start,
IIf([Requirement CAPPVD_Reviewed] Is Null,[Process Date],IIf([Process
Date]<=[Requirement CAPPVD_Reviewed],[Process Date],[Requirement
CAPPVD_Reviewed])) AS ThisDate, IIf([Requirement SPLCOF_Reviewed] Is
Null,[ThisDate],IIf([ThisDate]<=[Requirement
SPLCOF_Reviewed],[ThisDate],[Requirement SPLCOF_Reviewed])) AS
ThisDate1,
IIf([Requirement APPROVAL_Reviewed] Is
Null,[ThisDate1],IIf([ThisDate1]<=[Requirement
APPROVAL_Reviewed],[ThisDate1],[Requirement APPROVAL_Reviewed])) AS
ThisDate3, IIf([Requirement POLREC_Reviewed] Is
Null,[ThisDate3],IIf([ThisDate3]<=[Requirement
POLREC_Reviewed],[ThisDate3],[Requirement POLREC_Reviewed])) AS DFCT,
[DFCT]-[START] AS [Time Service1], IIf([Start]<#11/25/2004# And
#11/25/2004#<[DFCT],2,0) AS HD1, IIf([Start]<#12/24/2004# And
#12/24/2004#<[DFCT],1,0) AS HD2, IIf([Start]<#12/31/2004# And
#12/31/2004#<[DFCT],1,0) AS HD3, IIf([Start]<#1/17/2005# And
#1/17/2005#<[DFCT],1,0) AS HD4, IIf([Start]<#2/21/2005# And
#2/21/2005#<[DFCT],1,0) AS HD5, [Time Service Table].[Site Code], [Time
Service Table].DOT, [Time Service Table].[PROCESS DATE], [Time Service
Table].[Time Service], [Time Service Table].IGO_NOGO, [Time Service
Table].[Special Project], [Time Service Table].[Requirement
Approval_Reviewed], [Time Service Table].[Requirement AURAINT_Reviewed]
AS
Expr1, [Time Service Table].[Requirement CAPPVD_Reviewed], [Time Service
Table].[Requirement PolRec_Reviewed], [Time Service Table].[Requirement
SplcOf_Reviewed]
FROM [Time Service Table];
------------end of SQL -------------

You may have to clean that up a bit. All it is is the original query,
minus its WHERE and ORDER BY clauses. Save this query as
"qryTimeService" -- or whatever other name you want, but I'll use that
name here.

Now, if we wanted to select from this query each unique CaseNumber with
its smallest cycle time, we could write a query like this:

SELECT CaseNumber, Min(CycleTime) As MinCycle
FROM qryTimeService;

What you really want is only those records from qryTimeService that have
CaseNumbers in the result set from the above query. That can be
achieved by SQL like this:

SELECT DISTINCT qryTimeService.*
FROM
qryTimeService
INNER JOIN
(SELECT
CaseNumber,
Min(CycleTime) As MinCycle
FROM qryTimeService)
As T
ON qryTimeService.CaseNumber = T.CaseNumber
AND qryTimeService.CycleTime = T.MinCycle;

At least, I think it can, but I'm no great SQL wizard, and I may have
made a mistake.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)