Unique Records BUT with Additional Criteria (1 Viewer)

Pullingmyhair

New member
Local time
Today, 06:11
Joined
Oct 8, 2014
Messages
4
The main table for the database I am working on contains the following fields:
ID
Mfr Control Number
Initial or Follow-Up
Follow-up Number
Suspect
Date of Initial Email
Date Received
Date Submitted
Date of Report
Serious
Brief Description
Causality
Notes

With some additional qualifications I wanted to find records that had an intial report but no follow-up. Which translates too I want records that are unique in the Mfr Control Number field (no duplicates).

I am trying to build a query and keep getting hung up on the unique aspect of fields. I started by trying to query only "Mfr Control Number" fields that are unique (no duplicates). As best I can figure for some reason I can not add any additional fields to that query. My current query is set up in the query build table such that I have added "Mfr Control Number" in two columns. The first column in Total I have "Group By". In the next column I have set Total to "count" with a criteria of 1. If I try to add any other fields from my table than I seem to lose those unique results. But I need to further filter to get the exact information I need.

I want criteria on the "Initial or Follow-up" field to only bring "initial"
I want criteria on the "Serious" field to only bring "serious"
I want criteria on the "Date of This Report" field of "<Date()-"15""

Is there some way to take the results of that initial query to then build a another query based just on those records? I could then apply the further criteria and run my report. Or is there a way to do this in one step?

I've tried to make clear my intention but know it can be difficult to get this kind of stuff down in writing in a clear fashion. I have to be careful to keep information confidential also so some of the details are vague. Any advice you can offer is greatly appreciated!

Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:11
Joined
Feb 19, 2013
Messages
16,607
try the following:


Code:
SELECT * 
FROM myTable 
WHERE (SELECT count(*) from myTable as T WHERE [Mfr Control Number]=myTable.[Mfr Control Number])=1
 

Pullingmyhair

New member
Local time
Today, 06:11
Joined
Oct 8, 2014
Messages
4
Thanks CJ_London. That did the trick!
 

Users who are viewing this thread

Top Bottom