Unique Records (But not UNIQUE or DISTINCT records)

[Pullingmyhair]

I am trying to accomplish something with a query that I thought would be fairly simple and instead seems not to be. Basically we have reports under a specific report number. Each report is required to have follow-up reports (under the same report number) so there is a field to indicate "initial" or "follow-up". What I want is to be able to create a report that will show me any reports that have only an "initial" record and no "follow-up" record. Bottom line if in the report number field the same report # shows up more than once I don't want to see it. If the report number is unique than I want to see it. The DISTINCT and UNIQUE queries want to show me a single record of the duplicates and I don't want that. Any suggestions are greatly appreciated.

 

[jdraw]

You didn't say what table(s) are involved, so it's a guess on my part.

Suppose you had a table of Reports (tblReports) with fields ReportNumber and ReportStatus.

Try

SELECT COUNT(ReportNumber), ReportStatus FROM tblReports
Group by ReportStatus
having Count(ReportNumber) =1

 

[Pullingmyhair]

jdraw thank you so much for responding to my post! Sorry I am just getting back to this. I think I had been staring at my own database too long and wasn't very clear in my initial post. And your response actually made me give this more thought and realize I should be able to make this even more simplistic. On my table there is a field called "MfrControlNumber". I want a query to bring me only the MfrControlNumber records that are unique. I don't know if it matters but this is an alpha/numeric field. So if I had the following:

MfrControlNumber​

1234AB​

2341BC​

1234AB​

1234AB​

I would only want a query that shows me 2341BC (as there is more than one instance of 1234AB). I hope this helps to clarify. Any further assistance you can offer is greatly appreciated!

 

[jdraw]

Try

SELECT MfrControlNumber, count(MfrControlNumber)
FROM YourTableNameGoesHere  <======================= Change this!!!!!!!!!
group by MfrControlNumber
having count(MfrControlNumber) = 1