Adding possible new data to existing d/b

[Guest]

Hello, I am back with another Q.

As you may recall from my previous posts, I maintain a Youth Group's
database, which was designed by my predecessors. (I am the only person who
uses this database; I send reports to my boss in EXCEL format because she
doesn't have access to ACCESS, lol.) I am a novice when it comes to this
relational d/b stuff, so please bear with me. I hope I have provided enough
information to you... I am using Access 2002 on WinXP with MS Office Prof'l.

We have an existing d/b of about 3800 records. There is a new program
coming up for next month, and the kids' registration forms will begin
arriving in my mail next week. Before I go and add new records to our d/b, I
want to know if there is a way to set the design of the d/b so that when I go
and type a new namefield for a possible new record, I can be warned of a
possible duplication of a previous record. Of kids who will attend this new
program, it is possible that they attended prior programs and are already in
our database. I don't want to duplicate records, nor do I really want to
search the database for each registration form as it comes in (but I suppose
I will if that's the only way to avoid duplications).

Thanks in advanc, again.

Max

 

[DL]

You would have to set the 'Newname' field as index no duplicates. A default
msg will then appear if you enter something which is allready there.
This is only a guide as I dont know the structure of your table/Newname
field.
If its peoples names there are usually at least two fields - FirstName,
LastName maybe MidName the index would be a combination of all three

 

[Tim Ferguson]

I
want to know if there is a way to set the design of the d/b so that
when I go and type a new namefield for a possible new record, I can be
warned of a possible duplication of a previous record.

The operative words here are "warned" and "possible". Out of 3800 records
it is possible that you don't have two different George Browns, but the
odds increase as the database grows. Setting a unique index (which will
prevent such repeats) will not do what you want.

Of kids who
will attend this new program, it is possible that they attended prior
programs and are already in our database. I don't want to duplicate
records, nor do I really want to search the database for each
registration form as it comes in (but I suppose I will if that's the
only way to avoid duplications).

How you handle this depends largely on your users and how often you
anticipate (a) apparent duplicates and (b) real duplicates.

One approach is to force the user to use a Search dialog before getting
to an empty new record form -- one way to do this is to use the Load()
event of the form. This is pretty painless if you are frequently coming
across the same children, but a bore if you make people do searches that
everyone knows are fruitless.

Another approach is to look after the user has started, or finished,
filling in the new record; using the Exit or BeforeUpdate events on the
name field(s) or the form itself. You don't have to be quite so dismal
about searching for a match: a simple DCount() expression does not take
much to program, and not long to carry out as long as the table is
properly indexed. Try something like


' put a filter together
strWhere = "LastName = """ & txtLastName & """ " & _
"AND FirstName = """ & txtFirstName & """"

' count how many existing matches
wMatches = DCount("*", "Children", strWhere)

' if there are none, don't worry about it any more
If wMatches = 0 Then Exit Sub

' otherwise, you'll have to check with the user
Call AskUserWhatToDoNext( boolCarryOn )

' user is happy so don't do any special
If boolCarryOn = True Then Exit Sub

' okay: we'll cancel the update or whatever
Cancel = True


The hardest part is knowing what to do with the matches. You probably
need to show the user some kind of dialog box with the candidates and
have him or her pick one or say, "no, it really is another brand new
George Brown". This requires stepping right back from the computer and
checking out different scenarios with your users.

Another problem is picking up near matches: like "Geogre Brown" or even
"Brown George". How sophisticated you ( want to | need to | are able to )
make this is up to you!

Hope that helps


Tim F