Flag duplicate records in a query

[jeffreybrown]

I have a query where I need to flag if a Projects.ID has been duplicated due to a Notes.Notes field being added to the query.

SELECT Projects.ID, Max(Notes.ChangedDate) AS MaxOfChangedDate
FROM Notes RIGHT JOIN Projects ON Notes.PID = Projects.ID
WHERE (((Projects.ProjStatus)="In Progress"))
GROUP BY Projects.ID;

The sql above works fine to show the max date in the Notes.ChangedDate field; however, as soon as the field Notes, the query expands to show all notes as they are unique.

How can I add a field to the query which would annotate the Projects.ID field with a count like we would in Excel with countif?

In this field though, I would like the max date of the Notes.ChangedDate field to be the one and then any other duplicated records as 2, 3, 4, etc.

I could then filter the new field by 1 and get all the Projects.ID and their associated notes.

 

[Joe4]

Are you looking for something like this?

SELECT Projects.ID, Max(Notes.ChangedDate) AS MaxOfChangedDate[COLOR=#ff0000], Count(Notes.ChangedDate) AS CountOfNotes[/COLOR]
FROM Notes RIGHT JOIN Projects ON Notes.PID = Projects.ID
WHERE (((Projects.ProjStatus)="In Progress"))
GROUP BY Projects.ID;

 

[jeffreybrown]

Hi Joe,

Actually, that just gives me the count to say there are 2 Projects.IDs.

Instead, I'm looking for the first record by the max date to show 1 and then second record would show 2 and if there was a third Projects.ID, it would show 3 and etc.

 

[Joe4]

So, what you are looking for is a counter in your query, to number all the records?
If so, see: https://www.599cd.com/tips/access/140703-row-number/

 

[jeffreybrown]

Sorry for the poor explanation, I dont' use Access near as much as Excel.

Here are the results with using max date
[Table="class: grid"][tr][td] [/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

ID​

[/td][td]

MaxOfChangedDate​

[/td][/tr]

[tr][td]

2​

[/td][td]

9​

[/td][td]

5/8/2017​

[/td][/tr]

[tr][td]

3​

[/td][td]

12​

[/td][td]

5/15/2017​

[/td][/tr]

[tr][td]

4​

[/td][td]

13​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

5​

[/td][td]

19​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

6​

[/td][td]

20​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

7​

[/td][td]

21​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

8​

[/td][td]

40​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

9​

[/td][td]

42​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

10​

[/td][td]

43​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

11​

[/td][td]

45​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

12​

[/td][td]

46​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

13​

[/td][td]

48​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

14​

[/td][td]

49​

[/td][td]

5/16/2017​

[/td][/tr]

[tr][td]

15​

[/td][td]

50​

[/td][td]

5/16/2017​

[/td][/tr]
[/table]

The problem comes in when I add the notes field which now make the max field miss the true max date based off of the ID as the notes makes everything unique.
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

ID​

[/td][td]

ChangedDate​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

2​

[/td][td]

9​

[/td][td]

5/8/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

3​

[/td][td]

9​

[/td][td]

1/18/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

4​

[/td][td]

12​

[/td][td]

5/15/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

5​

[/td][td]

13​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

6​

[/td][td]

19​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

7​

[/td][td]

19​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

8​

[/td][td]

20​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

9​

[/td][td]

21​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

10​

[/td][td]

40​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

11​

[/td][td]

40​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

12​

[/td][td]

42​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

13​

[/td][td]

42​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

14​

[/td][td]

43​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

15​

[/td][td]

43​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

16​

[/td][td]

45​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

17​

[/td][td]

45​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

18​

[/td][td]

46​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

19​

[/td][td]

46​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

20​

[/td][td]

48​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

21​

[/td][td]

49​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

22​

[/td][td]

49​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

23​

[/td][td]

50​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][/tr]

[tr][td]

24​

[/td][td]

50​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][/tr]
[/table]

This is what I would like to achieve thru the query
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

ID​

[/td][td]

ChangedDate​

[/td][td]

Notes​

[/td][td]

Cnt​

[/td][/tr]

[tr][td]

2​

[/td][td]

9​

[/td][td]

5/8/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

3​

[/td][td]

9​

[/td][td]

1/18/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]

12​

[/td][td]

5/15/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

5​

[/td][td]

13​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

6​

[/td][td]

19​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

7​

[/td][td]

19​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]

[tr][td]

8​

[/td][td]

20​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

9​

[/td][td]

21​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

10​

[/td][td]

40​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

11​

[/td][td]

40​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]

[tr][td]

12​

[/td][td]

42​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

13​

[/td][td]

42​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]

[tr][td]

14​

[/td][td]

43​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

15​

[/td][td]

43​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]

[tr][td]

16​

[/td][td]

45​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

17​

[/td][td]

45​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]

[tr][td]

18​

[/td][td]

46​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

19​

[/td][td]

46​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]

[tr][td]

20​

[/td][td]

48​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

21​

[/td][td]

49​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

22​

[/td][td]

49​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]

[tr][td]

23​

[/td][td]

50​

[/td][td]

5/16/2017​

[/td][td]

Notes​

[/td][td]

1​

[/td][/tr]

[tr][td]

24​

[/td][td]

50​

[/td][td]

2/13/2017​

[/td][td]

Notes​

[/td][td]

2​

[/td][/tr]
[/table]

Now I can filter the query to the 1's and get the one max date with the associated note

Of course I'm using Excel to hopefully paint a better picture

 

[Joe4]

You cannot add the Notes field directly in to the Aggregate Query without Grouping on it, which would create those extra records, because uniqueness would then be determined on BOTH the ID and Notes field.

What you need to do start a new query, and add your original Aggregate Query you posted in your original post and your original Notes table, and join the two on BOTH the ID and DateChanged fields. Then you can return any fields from those two that you want to show.

 

[jeffreybrown]

Ok, got it now. Thanks Joe

 

[Joe4]

You are welcome!