Show field records with no duplicates

[CEV]

I am having a mind blank today, it's Friday. I want to create a report that
shows a list of the data in a field but do not want it to show any
duplicates. How do I do this?

Thanks,

Chad

 

[John Spencer]

Query would be

SELECT DISTINCT Somefield
FROM SomeTable
ORDER BY SomeField

 

[CEV]

That did work except for a couple of things. I wanted to add a second field
to the report with the same result of only showing the persons name once. It
also see's if one of the records field is blank. My qry is below. There are
over 100 records in the individuals table. There are only about 9 different
distinct names in each of the fields for SC and Supervisor. This qry is
currently showing me the names more than once because there are say records
that have the same SC name in that field but may have different Supervisors
in the records. Would I need to create 2 different qry's for this and then
base the rpt on those 2 qry's?

Thanks

SELECT DISTINCT individuals.SC, individuals.supervisor AS Expr1
FROM individuals
ORDER BY individuals.SC, individuals.supervisor;

 

[Peter Yang [MSFT]]

Hello Chad,

If I understand you correctly, you only need the reprot to show a single
record even if the same SC has different supervisor in the table. For
example, if table has the records:

sc1 supervisor1
sc1 supervisor1
sc1 supervisor2
sc2 supervisor3
sc2 supervisor4

You only want to report to show:

sc1 supervisor1
sc2 supervisor3


or

sc1 supervisor2
sc2 supervisor3

If I'm off-base, please correct me.

You could try the following query to get the result you want in SQL view of
the query:

select SC, min (supervisor) as supervisor1 from individuals group by SC

If this does not meet your requirement, please let's know more details.
Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[CEV]

That would probably work, but we have since decided to just use the SC
field. So now I have it working except it will also show a record for the
fields that are blank. How can I not include it if the field is blank? My
SQL view is below.

Thanks

SELECT DISTINCT individuals.SC
FROM individuals
ORDER BY individuals.SC;

 

[John Spencer]

Filter the results with a WHERE clause. Probably using the criteria of IS NOT NULL

SELECT DISTINCT individuals.SC
FROM individuals
WHERE SC is Not Null
ORDER BY individuals.SC;

 

[Peter Yang [MSFT]]

Hello Chad,

Yes. If you only need SC field in the query, as CEV mentioned, you could
use the following query to filter rows with SC is null.

SELECT DISTINCT sc FROM individuals WHERE sc is not null ORDER BY sc;

If you need further assistance on the issue, please feel free to let's
know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[CEV]

Thanks John, that was exactly what I wanted. And it was even easy. I'm not
SQL savy, but I'm slowly learning.

Thanks,

Chad