Duplicate records in qry, many-to-many issue? (1 Viewer)

[WalterInOz]

I can't work out how to set-up a qry based on multiple criteria on a single field without obtaining duplicate records. DISTINCT doesn't do the trick here. What should be the approach?

This is for a library database holding a collection of publications. For ach publication one or more special interest areas can be indicated (Area1, Area2, Area3 etc). Area and publication are connected through a many-to-many table.

When I search for papers that are marked for eg Area1 and Area3 the records that are marked for both areas will show up twice in the table. I understand why that happens, I don't understand what to do to avoid it.

Anyone got any pointers?

 

[John Big Booty]

Have you tried using a Totals query and using the Group By option. You will need to use your current query as the source for this query so you can simply group the duplicate titles.

 

[WalterInOz]

Thanks for the reply john.

No, I hadn't considered a totals qry.
I don't see how a totals query would help but as I'm not a very advanced user that doesn't mean much at all. So, I have given it a try but the result is the same, ie duplicate records for publications marked for multiple areas.

I've played around with DISTINCT and DISTINCTROW to no avail and I'm really at a loss. I'm obviously not the only one, similar questions have been asked before but I haven't seen an answer that works or applies to my problem.

 

[John Big Booty]

One of the options of a totals query is Group By, which groups records of the same value.

 

[WalterInOz]

I have done a "group by" John, but that doesn't change anything. Am I perhaps missing something obvious or simple?

Here's the SQL of the query:

SELECT DISTINCTROW tblRecords.RecordID, tblRecords.ArchiveNumber, tblAreas.Area, tblRecords.Title, tblRecords.KeyWords, tblRecords.Authors, tblRecords.Abstract, tblRecords.Journal, tblRecords.LinkPDF, tblRecords.HardCopy, tblRecords.CBioPub, tblRecords.Note, tblRecords.ModficationDate, tblRecords.DeletionRequested, tblRecords.Type
FROM tblRecords INNER JOIN (tblAreas INNER JOIN tblRecord_Area ON tblAreas.AreaID = tblRecord_Area.AreaID) ON tblRecords.RecordID = tblRecord_Area.RecordID
GROUP BY tblRecords.RecordID, tblRecords.ArchiveNumber, tblAreas.Area, tblRecords.Title, tblRecords.KeyWords, tblRecords.Authors, tblRecords.Abstract, tblRecords.Journal, tblRecords.LinkPDF, tblRecords.HardCopy, tblRecords.CBioPub, tblRecords.Note, tblRecords.ModficationDate, tblRecords.DeletionRequested, tblRecords.Type, InParam(tblAreas!Area,Forms!frmSelectedAreas2!txtSelected)
HAVING (((InParam([tblAreas]![Area],[Forms]![frmSelectedAreas2]![txtSelected]))=True))
ORDER BY tblRecords.ArchiveNumber DESC;

 

[John Big Booty]

Group By will only work where you have identical records, so you will either have to use another argument ie. count, Max, or min for the other records. Or as I said before use the first query as the source for your group by query.

 

[WalterInOz]

Ahh, now I understand what you meant previously. I should have understood what you meant in your first reply but didn't quite get it.
And guess what! After doing exactly what you suggested it now works perfectly!

Thank you very much John. You've been very patient and your pointers were very helpful.

 

[John Big Booty]

Glad you got it working