One annoying duplicate record problem!!!

[bees292]

I have a search form passing text from unbound controls to a query. These two fields (AuthorName & Title) of the query are from two different tables (Authors & Papers) set up in a many to many join. The 3 dummy records I am using are as follows:

Record 1
AuthorName: Smith (Author 1)
Title: SmithTitle

Record 2
AuthorName: Smith (Author 1)
AuthorName: Jones (Author 2)
Title:SmithJonesTitle
(i.e. two authors for this record)

Record 3
Author: Jones (Author 2)
Title: JonesTitle

If I stick in 'Jones' into the Author field and 'JonesTitle' in the Title field I correctly get 1 result from the query (record 3). However, oddly, if I put in Smith and SmithTitle I get two records (2 x record 1)!!!!! There aren't two records!!! Similarly if I leave both blank I get all the records and again there is a duplicate of the Smith record (and only this one is duplicated!)

The query criteria are:

[AuthorName] Like [Forms].[Search]![Author] & "*"
[Title] Like [Forms].[Search]![Title] & "*"

Any ideas???

Thanks

 

[mresann]

I think this happens when you have several books assigned to one author. I assume you have authors and books assigned in a many to many relationship with each other, creating a third table that organizes the joins.

I wonder if using SELECT DISTINCT would work for you. On the other hand, you many have right or left joins in one of the join table relationships.

If this doesn't work, can you post the table structure with what you already have? Just table names and fields will suffice.

 

[bees292]

Sorry. Do I just put this at the front of my existing criteria?

 

[bees292]

Ok this is still a problem.

3 tables: Authors, Papers, Join.

Authors
-------
AuthorID
AuthorName

Papers
--------
PaperID
Title
etc (unimportant fields)

Join
----
AuthorID
PaperID

Authors and Papers relate to Join in one to many. Join type 1 in each case. Join table fields set to 'duplicates ok' (is this correct? i didn't set it i dont think.but logically seems correct).

I think this happens when you have several books assigned to one author.

The dummy fields are set up so Jones and Smith have 2 books each so I dont think this is the problem. Any other ideas would be welcome...thanks

 

[bees292]

selecting unique records in query design solved, sorry.

 

[mresann]

Sorry for the late reply. For some reason my forum "reply" indicator wasn't working.

Join tables are different in their Primary Key setups. For records to be unique, the combination of the foreign keys comprise the unique record indicator for that table. In this case, the AuthorID and the PaperID fields, TOGETHER, must be unique throughout the table.

In other words, you can have (adjust for formatting):

AuthorID PaperID

1 2
1 3
2 3


Note there AuthorID "1" is in more than one record, while PaperID "3" is in more than one record. However, no two records are similar in BOTH Fields. In other words, you can NOT have:

AuthorID PaperID
1 2
1 3
1 3

You notice that Author "1" and PaperID "3" is duplicated. If you have the table set with CO-Primary keys, you will get a key violation error.

Hope this clears up any loose threads you may have.