Avoiding Duplicate Entries

[meaghantron]

So far, I have a very siplistic DB. I am trying to create a DB that allows me
to enter in and search for information on specific newspaper/journal/media
articles relating to a specific firm. I have created three tables by
importing a previously created excel spreadsheet and using the table wizard.
The tables are as follows:

table one: Article
fields: ID [autonumber/primary key], Date, Summary, PDF attachment, and
lookups to the other two tables.
table two: Author
fields: ID [autonumber/foreign key], Author Name
table three: Newspaper
fields: ID [autonumber/foreign key], Publisher, Location

the relationships are one (Newspaper, Author) to many (Article)

i have a form to allow data entry into each of these fields with the author,
publisher and location fields each containing a combo box with the option of
'limit to list' turned off.

here is the problem: whenever i enter an author, publisher or location that
already exists on the list/in these tables, instead of attributing that
article to that author ID (for example) it creates a new record of that
author with a new ID number. how can i create a situation where i can
attribute a new article record to an existing author, publisher, and location
record by using the same form?

thx

 

[Fred]

If I may start by addressing a few points of confusion and missing
information.

I'm assuming that you have a good structure in place as follows but just
mis-described it:

-Your ID fields in your author and newspaper tables are actually Primary
Keys, not foreign keys. This number is a sort of a "code" for those
entities.


- By "lookups to the other two tables" you mean that you have a field in
your article table for the author code (e.g. AuthorID) , and field in your
article table for the newspaper code (e.g. NewspaperID) BTW, THESE are your
foreign keys.

- You have linked these (using my example names)

-AuthorID field in articles table to ID field in the Author table
-NewspaperID field in the articles table to the ID field in your
newspaper table

Now you have a main "articles" form which has dropdown lists to help your
load the AuthorID and NewspaperID codes into those fields.

So, the PK of your articles table is not linked to either of those other two
tables.

Now, when entering an article where the Author and ID are already in those
lists, you just use the dropdown boxes to load those codes, and it works
fine.

I assume that your answer to one or more of the above questions was "no" in
which case that points to a probable problem.

Now, if you only occasionally add an author or newspaper, you might want to
just update those tables separately in a single table query or form. If
you want to enter brand-new newspapers and authors at the same time as
entering a new article, I'd repost with that question. In any case, the
data-enterer has to see if it's in already before entering it again. Some
automatic checking for duplicates is doable, but that concept still has to be
considered a part of "data entry 101" and even "lists 101"

 

[meaghantron]

Yes, my vocabulary for describing this is definitely confused, as I am only
now becoming familiar with databases.

The structure is basically the way you redescribed. The NewspaperID and
AuthorID fields in the in the "Article" table are linked to the ID fields in
the "Newspaper" and "Author" tables. (acess has designated the names of the
fields as "Lookup to Newspaper" and "Lookup to Author" and generated it such
that it displays the related text fields instead of the corresponding ID
number) On the form, I have linked the "Author", "location", and "publisher"
lists to their corresponding fields in their own tables, and not the ID
fields in the main "Article" table. (this may be the problem?)

In order to set the property Limit to List = NO (so that I can add new
authors or newspapers at the same time as entering new articles) I have been
resetting the bound column to match the selected field. (ie. author = column
2). This works fine for adding new author/newspaper data along with the
articles.

However, when I select one of the options from the drop down list, it
assigns that author a new automatic ID, causing me to have multiple entries
of that author with different Primary Key ID #'s.

Does this fill in any of the blanks?

 

[Fred]

This may end up being clarifying so that somebody who is better than me at
combo boxes can answer, but:

"Lists" come from / are tables. Tables are the essential nouns for any
communications regarding structure. Now you are mention "Location" and
"Publisher" as "lists" whereas before you mentioned them as being fields in
your newspaper table. ?? Step 1 is that we have to really know what your
tables are.

Answering your question it is not a problem that you have not linked
anything to your article PK. I've not seen anything that should be linking
to that field.

"Limit to list" refers to removing restrictions on what can loaded into that
field in your ARTICLES table, not on editing the dropdown list.

It sounds like you may have the text from multiple fields from your
newspaper table linked to seperate combo boxes, and that they are loading
text and not the ID fields. If so, you should nuke that portion and start
over.

Decide on your list of tables. Make a separate table for each dropdown
list. Unless the lists are tiny and static, include an ID field in each
of those tables. Load those directly.

Make a form that is based on your articles table. Make combo boxes for all
of your dropdowns. Have them load the ID number into your articles table.

Once all of that is working , repost about how to edit the dropdown lists
through the combo boxes.

 

[Ken Sheridan]

To add to what Fred has said, your form for entering data into the Articles
table should include two combo boxes for the author and newspaper, with
ControlSource properties of AuthorID and NewspaperID (or whatever the foreign
key fields in Articles are called). These should be set up as follows,
taking author as an example:

RowSource: SELECT ID, [Author Name] FROM Author ORDER BY [Author Name];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

to select an author for an article you simply select from the list. To add
a new author you can type the new name into the combo box and include code in
the combo box's NotInList event procedure as follows:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

strSQL = "INSERT INTO Author([Author Name]) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

The newspaper combo box would be set up in the same way, but in this case as
well as inserting the new value typed into the combo box into the Newspaper
table, you also need to insert other data. I'm assuming it’s the Location
values listed in the combo box, so when you type in a new one you also need
to be able to enter a value into the Publisher field for the new location.
The code for the NotInList event differs therefore in that it needs to pop up
a form bound to the Newspaper table, frmNewspaper in the example below, for
this data to be entered, like so:

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmNewsPaper", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmNewsPaper closed
DoCmd.Close acForm, "frmNewsPaper"
' ensure newspaper has been added
If Not IsNull(DLookup("ID", "Newspaper", "Location = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Newspaper table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If


In the frmNewsPaper form's Open event procedure put the following code to
assign the value you entered in the combo box, and passed to the form via the
OpenArgs mechanism, to the Location control's DefaultValue property

If Not IsNull(Me.OpenArgs) Then
Me.Location.DefaultValue = """" & Me.OpenArgs & """"
End If


A couple of supplementary points:

1. Your tables as set up assume that there will only one author for each
article. With newspaper articles this is usually the case, but with academic
articles, particularly in the scientific or medical fields, its very
frequently the case that an article will have multiple authors. There is
therefore a many-to-many relationship type between articles and authors,
which must be modelled by another table ArticleAuthors say, with foreign key
columns referencing the primary keys of Article and Author respectively. The
Articles table would now no longer have an AuthorID foreign key as the extra
table models the relationship.

2. Your current model also assumes than any author can write for any
newspaper. If, however, each author writes for a subset of the total set of
papers or other media, then again this would be modelled by another table
NewspaperAuthos say, with foreign key columns referencing the primary keys of
Newspaper and Author respectively. The ArticleAuthors table then be related
to this table, not to Authors, the relationship being on both the AuthorID
and NewspaperID columns.

Finally, beware the dreaded 'lookup wizard' in table design. For the
reasons why see:

http://www.mvps.org/access/lookupfields.htm


Ken Sheridan
Stafford, England