Re: Show just one record each person-different results

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Ok! Since it works I'm keeping the fee.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6DDCBC2E-95F2-4547-8ADA-5FD22BE4C0AD@xxxxxxxxxxxxxxxx
Thank you, John. I tried it and it works beautifully.

"Dennis" wrote:

He he. OK, John, I'll give it a try. And thanks for your assistance.
I'll
let you know how it turns out.

"John Spencer" wrote:

The first query should show one record for each name and case number
along
with the Max result.

Assuming that your table is actually storing the string "No
Identification"
or "Positive Identification", the Max result would be "Positive
Identification" if any record for that combination of Name and case
Number
had a Positive Identification. If not, the "No identification" would
be
returned for that combination.

Now that you know those three items, you can join the query on those
three
items to the original table and return the highest Request (max)
request
number that matches those three facts.

Try the solution, if it fails I will refund the cost of my advice.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7786AF09-92D2-44EA-B0AB-F0AAF917C044@xxxxxxxxxxxxxxxx
It does take me a while to go through what's been suggested and put
it
together to see if it will help me, but in looking at your solution,
I'm
thinking you were of the understanding that the last (most recent)
record
having to do with a particular name was the one I wanted to display,
and
that's not always the case. I always want to display "Positive
Identification" if any records on that person show that, but "No
Identification" only if there are no positives to display. But I
want
each
person's name to appear just once in the query results.

"John Spencer" wrote:

Did the solution I proposed earlier not work?
Or did you not understand how to implement it?
Or did it give you the wrong results?
Or was there some other problem with it - such as the response was
too
terse
for you to use.

I did make the assumptions that you wanted the last applicable
Request #
and
that Results held only one of two values - No Identification or
Positive
Identification. IF your field names and table names follow the
naming
conventions - Only letters, numbers, and underscore characters -
then the
solution could be done in one query using a subquery in the FROM
clause.

Two query solution
Save as Q1
SELECT [Name]
, Max(Results)as IDState
, [Case Number]
FROM theTable
GROUP BY [Name], [Case Number]

Second query uses the first (saved query) and the original table
SELECT T.[Name], T.IdState, T.CaseNumber
, Max(T.[Request#]) as Request
FROM theTable as T INNER JOIN Q1
ON T.[Name] = Q1.Name
AND T.Results = Q1.IDState
AND T.[Case Number] = Q1.[Case Number]
WHERE T.[Case Number] = "0000015"
GROUP BY T.[Name], T.IdState, T.CaseNumber


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C95E2F86-A185-4678-AC79-6BC4A03C5F8D@xxxxxxxxxxxxxxxx
I compare fingerprints on criminal cases for a living. I want to
reflect
in
my database in one place the names of all the people who have been
compared
on a particular case (the case number) with the evidence. They
may be
compared more than once (hence, the multiple request numbers)
because
new
evidence comes to light. Once they are identified, however, I
want to
show
that they have been identified in this case, and I don't care
about
showing
all the times they weren't identified. If they were never
identified,
however, I want the database to show in this one place (and only
once)
that
they've been compared, but not identified.

"Van T. Dinh" wrote:

What is the logic to select the [Request#] if there are more than
1
Record
for a specific name?

For example, if you have the data set:

Name Request# Results Case
Number
Joe Smith 1234 No Identification
0000015
Joe Smith 2345 Positive Identification 0000015
Bill Jones 3456 No Identification
0000015
Bill Jones 4567 Positive Identification
0000015
Bill Jones 5678 No Identification
0000015
Joan Smith 6789 No Identification
0000015

Which [Request#] would you select for the "Bill Jones"?

--
HTH
Van T. Dinh
MVP (Access)




"Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:398D7D18-70D1-46CB-8CD1-DC2158163FE0@xxxxxxxxxxxxxxxx
I have two tables: Requests, Suspects with inner join (by
request#)

I want to run a query by case number (a field on the requests
table)
and
show all the people who have been compared, having each
suspect's
name
appear
just once with the result, but showing "Positive
Identification" as
the
result if such a record exists, but "No Identification" if not.
In
other
words:

If I have these records available:

Name Request# Results Case
Number
Joe Smith 1234 No Identification
0000015
Joe Smith 2345 Positive Identification
0000015
Bill Jones 3456 No Identification
0000015
Bill Jones 4567 No Identification
0000015
Bill Jones 5678 Positive Identification
0000015
Joan Smith 6789 No Identification
0000015

I want the query to return the following result:

Name Request# Result Case
Number
Joe Smith 2345 Positive Identification
0000015
Bill Jones 5678 Positive Identification
0000015
Joan Smith 6789 No Identification
0000015













.

Relevant Pages

  • Re: Show just one record each person-different results
    ... "John Spencer" wrote: ... Now that you know those three items, you can join the query on those three ... items to the original table and return the highest Request request ... I always want to display "Positive ...
    (microsoft.public.access.queries)
  • Re: Show just one record each person-different results
    ... "John Spencer" wrote: ... Now that you know those three items, you can join the query on those three ... items to the original table and return the highest Request request ... I always want to display "Positive ...
    (microsoft.public.access.queries)
  • Re: IIf statement in Access 2003 query
    ... Can you use the same thing in your weekends calculation? ... You might find that you need to do the following, since at times Access won't recognize a calculated field within the same query. ... Access MVP 2002-2005, 2007-2009 ... "John Spencer MVP" wrote: ...
    (microsoft.public.access.queries)
  • Re: Insert query with null column and null value
    ... INSERT INTO tPack (MachineId, OperatorId, FinishTime) ... Access MVP 2002-2005, 2007 ... query runs fine. ... "John Spencer" wrote: ...
    (microsoft.public.access.queries)
  • Re: DCount function for filtered data in table
    ... Access MVP 2002-2005, 2007-2008 ... "John Spencer" wrote: ... Here is the master formula that I use in the query field that filtered ... How can I use this formula to direct Access to filter out the date for ...
    (microsoft.public.access.queries)