Min

*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?

"Melissa" wrote in message

*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)

wrote:

*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
--
RuralGuy

Please reply to the newsgroup so all may benefit.

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



--
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/conrad...essjunkie.html



"RuralGuy" wrote in message
. ..
wrote:

*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
--
RuralGuy

Please reply to the newsgroup so all may benefit.

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!

"Dirk Goldgar" wrote:

"Melissa" wrote in message

*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)

"Melissa" wrote in message

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?

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

(please reply to the newsgroup)

"Lynn Trapp" wrote:

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!

--
RuralGuy

Please reply to the newsgroup so all may benefit.

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/conrad...essjunkie.html



"RuralGuy" wrote in message
. ..
"Lynn Trapp" wrote:

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!

--
RuralGuy

Please reply to the newsgroup so all may benefit.

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" wrote:

"Melissa" wrote in message

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?

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

(please reply to the newsgroup)

"Melissa" wrote in message

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.

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

(please reply to the newsgroup)