Added field causes repetitive record output

[Punch01]

Within a large database we inherited there is a problem of repetitive output of certain records.

In the query below for example (ID# 489),
when records for the (Metrics) CHLA or HISTC are produced,
and if the (Impaired) field lists " " the record will
print one line as seen in the 2nd row of ID#-489.

But if the (Impaired) field lists "IM" the record will
print one line as seen in the 3rd row of ID#-489.

This will ONLY happen on records where both the (Impaired) field is listed as "IM" & " ", for a given ID#.
An example of the records not repeating is seen at the bottom record (ID# 489A).


As you may have noticed this ONLY effects the (Metrics) with the (Impaired Metric) listed as "NUTR".
It is essential we that we have BOTH the (Impaired) "IM" and " " listing, but on only one line per ID#.

Any assistance would be greatly appreciated!!!

Punch01


ID# Impaired Metric Impaired Metric Name
Metric
-- -------- ------ -------- ---------------
489 BIO Biology
489 CHLA Chlorophyll
489 IM CHLA NUTR Chlorophyll
489 CHLOR Chloride
489 COND Conductance
489 DISS Dissolved Solids
489 IM DO DO Dissolved Oxygen
489 F Fluoride
489 IM FCOL FCOL Fecal Coliform
489 HISTC Historic Chlorophyll
489 IM HISTC NUTR Historic Chlorophyll

489A CHLA Chlorophyll
489A CHLOR Chloride
489A COND Conductance
489A DO Dissolved Oxygen

 

[traingamer]

What is the query?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Punch01]

Thr data comes directly from a table produced by another program.

The query was produced in order to break the various fields down to determine the cause of the repetitive output.

We were able to determine the cause of the repetition, the (Impaied -- "IM" & " ") field.

When the field is added to the query with the (Metric) field the problem starts.

 

[PHV]

I must be missing something but in you posted data I see NO repeated record.

 

[lespaul]

do you not want the records where the Impaired is missing ?

Leslie

 

[Punch01]

Leslie,

That's correct.

But also......If there is not any impairment such as the case with ID#489A it should be produce as written.

Output Now:

(ex) ID# Impaired Metric Imp. Metric Metric Name
------------------------------------------------------------

489 CHLA Chlorophyll
489 IM CHLA NUTR Chlorophyll




Thanks for all of your help !!!!

Punch01


489A CHLA Chlorophyll


Output Needed:

ID# Impaired Metric Imp. Metic Metric Name
------------------------------------------------------------

489 IM CHLA NUTR Chlorophyll

489A CHLA Chlorophyll

 

[PHV]

Something like this ?
SELECT A.[ID#], A.Impaired, A.Metric, A.[Imp. Metic], A.[Metric Name]
FROM yourTable AS A INNER JOIN (
SELECT [ID#], Max(Impaired) AS MaxImp, Metric FROM yourTable GROUP BY [ID#], Metric
) AS B ON A.[ID#] = B.[ID#] AND A.Metric = B.Metric AND A.Impaired = B.MaxImp;

I assumed that Impaired don't allow null but zero length string.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Punch01]

Thanks, PH