Windows XP 
Access 2007
The main table for the database I am working on is set similar to what follows:
ID Mfr Control
NumberInitial or
Follow-UpFollow-up
NumberSuspect Date of Initial Date Received Date Submitted Date of Report Serious Brief Description Causality Notes 49 2013DK Follow-up 1 45 9/17/2014 9/17/2014 Non-serious 8 2014AU Initial 22 8/6/2014 Serious 9 2015AU Initial 7 8/6/2014 8/1/2014 8/8/2014 8/6/2014 Serious 10 2015AU Follow-up 1 7 8/6/2014 8/8/2014 8/8/2014 8/8/2014 Serious
In plain English what I am trying to do is get any records that have had an initial report made but no follow-up (with additional criteria I will explain below).
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!
Reply With Quote
