Query creates duplicate results

[Egg 'n' Bacon]

Hi, I'm at a loss as to why the query (below) is creating a duplicate resulting record. I know which field is causing the problem, but not why (it's; ToDo: IIf([Action] Is Not Null,"*","").

Any ideas what I need to change?

Code: Select all

SELECT tblMain.ID, tblMain.Dept, tblMain.ActivityArea, tblMain.DateRec, tblMain.RevDate, tblMain.Version, tblMain.Assessor, tblMain.Live, Max((([Likelyhood]+[Freq])*([Severity]+[PersonnelEnvi]))) AS Score, tblMain.Legis, DConcatUnique("HazardImpact","tblSub","tblSub.ID=" & Chr(34) & [tblSub]![ID] & Chr(34)) AS Impakt, tblMain.PrevScore, IIf([Action] Is Not Null,"*","") AS ToDo
FROM tblSub RIGHT JOIN tblMain ON tblSub.ID = tblMain.ID
GROUP BY tblMain.ID, tblMain.Dept, tblMain.ActivityArea, tblMain.DateRec, tblMain.RevDate, tblMain.Version, tblMain.Assessor, tblMain.Live, tblMain.Legis, DConcatUnique("HazardImpact","tblSub","tblSub.ID=" & Chr(34) & [tblSub]![ID] & Chr(34)), tblMain.PrevScore, IIf([Action] Is Not Null,"*","")
HAVING (((tblMain.ID)="EAK002"))
ORDER BY tblMain.RevDate;

TIA
EnB

[HansV]

What if you remove the ToDo field?

[Egg 'n' Bacon]

Yes, removing this field elminates the duplicates, unfortunately, the user wants (amongst other things) this query to indicate where there is anything in the ToDo field, hence the inclusion of it.

[HansV]

If you don't want the duplicates, you'll have to indicate what you want to do with the ToDo field.
If it contains "*" for all records that are grouped together on the other fields, I assume that you want the query to return "*".
If it contains "" for all records that are grouped together on the other fields, I assume that you want the query to return "".
But if there is a mixture of "*" and "", what do you want to return: "*" or '" or something else?

[Egg 'n' Bacon]

The requirement is to group records in TblMain and denote (with the "*") when there is any entries in the related tblSub in ToDo.

So there should only be one record showing for each tblMain!ID, with any related records that have anything in tblSub!ToDo a * is displayed.

Hope that makes sense

[HansV]

OK, use this:

Code: Select all

SELECT tblMain.ID, tblMain.Dept, tblMain.ActivityArea, tblMain.DateRec, tblMain.RevDate, tblMain.Version, tblMain.Assessor, tblMain.Live, Max((([Likelyhood]+[Freq])*([Severity]+[PersonnelEnvi]))) AS Score, tblMain.Legis, DConcatUnique("HazardImpact","tblSub","tblSub.ID=" & Chr(34) & [tblSub]![ID] & Chr(34)) AS Impakt, tblMain.PrevScore, Max(IIf([Action] Is Not Null,"*","")) AS ToDo
FROM tblSub RIGHT JOIN tblMain ON tblSub.ID = tblMain.ID
GROUP BY tblMain.ID, tblMain.Dept, tblMain.ActivityArea, tblMain.DateRec, tblMain.RevDate, tblMain.Version, tblMain.Assessor, tblMain.Live, tblMain.Legis, DConcatUnique("HazardImpact","tblSub","tblSub.ID=" & Chr(34) & [tblSub]![ID] & Chr(34)), tblMain.PrevScore,
HAVING (((tblMain.ID)="EAK002"))
ORDER BY tblMain.RevDate;

[Egg 'n' Bacon]

Well that gets rid of the duplicates, but now it's give a * in some that have no data (also happens with; first, last, etc)

PS not sure why, but the SQL you provided threw up an error. Here is my version;

Code: Select all

SELECT tblMain.ID, tblMain.Dept, tblMain.ActivityArea, tblMain.DateRec, tblMain.RevDate, tblMain.Version, tblMain.Assessor, tblMain.Live, Max((([Likelyhood]+[Freq])*([Severity]+[PersonnelEnvi]))) AS Score, tblMain.Legis, DConcatUnique("HazardImpact","tblSub","tblSub.ID=" & Chr(34) & [tblSub]![ID] & Chr(34)) AS Impakt, tblMain.PrevScore, Max(IIf([Action] Is Not Null,"*","")) AS ToDo
FROM tblSub RIGHT JOIN tblMain ON tblSub.ID = tblMain.ID
GROUP BY tblMain.ID, tblMain.Dept, tblMain.ActivityArea, tblMain.DateRec, tblMain.RevDate, tblMain.Version, tblMain.Assessor, tblMain.Live, tblMain.Legis, DConcatUnique("HazardImpact","tblSub","tblSub.ID=" & Chr(34) & [tblSub]![ID] & Chr(34)), tblMain.PrevScore
HAVING (((tblMain.ID)="EAK002"))
ORDER BY tblMain.RevDate;

[HansV]

I left a comma after tblMain.PrevScore (before HAVING) by accident. You correctly removed it from your version.

If you get a "*" there MUST be a record with something in one of the Action records.

Does this work better:

Max(IIf([Action] & ""="","","*")) As ToDo

or

Max(IIf(Trim([Action] & "")="","","*")) As ToDo

[Egg 'n' Bacon]

Not sure what is being detected, because I went into the (related) records and deleted the data in the [Action] field but got the same result

That looks promising (Max(IIf([Action] & ""="","","*")) As ToDo).

Will need to test results. Thank you Hans

[Egg 'n' Bacon]

I think that's done it

[HansV]

Great! That means that there were empty strings "" in the Action field. Those do not count as Null, so they returned "*" in ToDo.