Selecting first occurrences

[DPrent]

Hi Could someone please help me. I have the following data structure (this example is a small selection of data and there will be several thousands of records within the database)

"id" "badge" "Date" "Match"

35 "01444" 16/02/2004 "yes"
36 "01444" 17/02/2004
37 "01444" 29/03/2004 "yes"
38 "01444" 30/03/2004 "yes"
39 "01444" 31/03/2004 "yes"
40 "01444" 01/04/2004 "yes"
41 "01444" 02/04/2004
42 "01444" 05/04/2004 "yes"
43 "01444" 06/04/2004 "yes"
44 "01444" 07/04/2004 "yes"
45 "01444" 08/04/2004 "yes"
46 "01444" 21/04/2004 "yes"
47 "01444" 22/04/2004 "yes"
48 "01444" 23/04/2004
49 "01444" 06/05/2004 "yes"
What I am trying to do is in a query select all the first occurrences of the “Match” fields that are equal to “yes” either output the complete record or mark the record with an identifier so that I could write another query to select those records. With the above records I would expect 4 (four) marked or output records. As follows:


"id" "badge" "Date" "Match"

35 "01444" 16/02/2004 "yes"
37 "01444" 29/03/2004 "yes"
42 "01444" 05/04/2004 "yes"
49 "01444" 06/05/2004 "yes"

I am an old cobol programmer and this would be very easy to do, but I must admit I am having trouble in Access…………….Thanks for any help
Kind Regards

Dave

 

[ByteMyzer]

Given the following - a table named MyTable, with id, badge and Match as text fields, and Date as a date field, the following query should work:

SELECT T1.*
FROM MyTable AS T1
WHERE T1.[id]=(
    SELECT Min(T2.[id])
    FROM MyTable AS T2
    WHERE T2.[badge]=T1.[badge]
    AND Month(T2.[Date])=Month(T1.[Date])
    AND Year(T2.[Date])=Year(T1.[Date])
    AND T2.[Match]='yes';);

 

[DLB]

Byte, I tried out your query. It returned:

id	badge	Date		Match
35	01444	16/2/2004	yes
37	01444	29/3/2004	yes
40	01444	1/4/2004	yes
49	01444	6/5/2004	yes

Not the id's 35, 37, 42, 49

 

[ByteMyzer]

Perhaps this:

SELECT T1.*
FROM MyTable AS T1,
(SELECT [id] FROM MyTable WHERE Nz([Match],'')<>'yes';) AS T2
WHERE CDbl(T1.[id])=CDbl(T2.[id])+1;