Composite unique key with some fields that are null

[SherryScrapDog]

Hi,
I have a database full of names (for geneology) where I have Last, First,
Middle and Title. My table has an auto-number for primary key. From reading
posts here, I figured out how to make a composite unique key with the 4
fields. However, it is routine that some of the fields are null, especially
(but not limited to) the middle and title fields. I load this table from
muliptle imported Excel tables. I could have Doe, Jane in one record, Doe,
Jane, Mary in another, and Doe, Jane, Mary, Mrs. in a 3rd record. The
problem I am having is that it is loading all records even when they are the
same. For example, if I have Doe, Jane, null, null in 2 records, the second
record loads. I tried both options on the 'Ignore Null' on the Index. I
can't require the fields because they are many times properly null. Am I
missing something simple here? Do I need something specific in my append
query to prevent loading the duplicates? Thanks in advance if you can help!

 

[Allen Browne]

You cannot have a null in a primary key field.

If you have an AutoNumber for primary key, that's not a problem. You can
have nulls in the other fields.

You can make a composite index on the fields that contain nulls, but I don't
think making it a unique index is a good idea. Having 2 people with the same
name is not uncommon - particularly in geneologies where children are
sometimes named after their parents. It might be better to just give a
warning if the name is not unique rather than to block it via a unique
index. To give the warning, use the BeforeUpdate event procedure of the form
where you enter people.

If you do need to create a unique index where some fields are known to have
no value (e.g. you know the person actually has no middle name), you can use
a zero-length string (zls) instead of a Null. In theory, a Null means
unknown/not applicable/undefined, whereas a zls means the value is known not
to exist. So if you don't know someone's phone number, that's stored as a
Null, whereas if you know someone has no phone, that's a zls. You enter a
zls by opening and closing quotes with nothing between them.

In practice, there is no visible difference between a Null and a zls, so it
tends to really confuse any non-technical people who use the database.
Addionally, you must remember to handle both cases whenever you design
queries, filters, reports, search forms, and so on.

If you want to create the unique composite index on the 4 fields and use
zero-length stings instead of nulls, open the table in design view and set
these properties for each of the Text type fields you have in the index:
Allow Zero Length Yes
Default Value ""

Again, I don't really see how this will be useful for your case. To me it
does not seem logical to make a unique index such that there can be only
exactly one Jane-Doe-with-no-other-name. In most tables, I personally think
you want to block the possibility of zero-length strings in your text
fields:
http://allenbrowne.com/bug-09.html

Hope that's useful.

 

[SherryScrapDog]

Hi Allen and thank you for your response! Yes, there can be more than one
person with the same name, and it is another table I have that gives the
researcher the detail to a name that will let them decide if this is the
person they want. For example, we might have the name Jane Doe in 15
different places in our records and it might be the same Jane Doe, or it
could be different Jane Does. The detail records (such as the year and other
potential info) connected to the name Jane Doe will let them know. The
detail records are related to my names table by the auto-number primary key.
I did as you suggested with the Allow Zero Length and the default of "". My
append query, which is:
INSERT INTO Master1 ( [Last], [First], Middle, Title )
SELECT [Lastname], [Firstname], [Middle], [Title]
FROM ExcelData1;
is still loading all of the records. Please let me know if there is
anything else you can see that I am doing wrong. It does prevent the
duplicate if all 4 fields have a value.
thanks again, Sherry

 

[Allen Browne]

I'm not completely clear on what's happening here, but use Nz() if you want
the Append query to insert zero-length strings into Master1 where ExcelData1
has nulls.

This kind of thing:
INSERT INTO Master1 ( [Last], [First], Middle, Title )
SELECT Nz([Lastname], ""), Nz([Firstname], ""),
Nz([Middle], ""), Nz([Title], "")
FROM ExcelData1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen and thank you for your response! Yes, there can be more than one
person with the same name, and it is another table I have that gives the
researcher the detail to a name that will let them decide if this is the
person they want. For example, we might have the name Jane Doe in 15
different places in our records and it might be the same Jane Doe, or it
could be different Jane Does. The detail records (such as the year and
other
potential info) connected to the name Jane Doe will let them know. The
detail records are related to my names table by the auto-number primary
key.
I did as you suggested with the Allow Zero Length and the default of "".
My
append query, which is:
INSERT INTO Master1 ( [Last], [First], Middle, Title )
SELECT [Lastname], [Firstname], [Middle], [Title]
FROM ExcelData1;
is still loading all of the records. Please let me know if there is
anything else you can see that I am doing wrong. It does prevent the
duplicate if all 4 fields have a value.
thanks again, Sherry

You cannot have a null in a primary key field.

If you have an AutoNumber for primary key, that's not a problem. You can
have nulls in the other fields.

You can make a composite index on the fields that contain nulls, but I
don't
think making it a unique index is a good idea. Having 2 people with the
same
name is not uncommon - particularly in geneologies where children are
sometimes named after their parents. It might be better to just give a
warning if the name is not unique rather than to block it via a unique
index. To give the warning, use the BeforeUpdate event procedure of the
form
where you enter people.

If you do need to create a unique index where some fields are known to
have
no value (e.g. you know the person actually has no middle name), you can
use
a zero-length string (zls) instead of a Null. In theory, a Null means
unknown/not applicable/undefined, whereas a zls means the value is known
not
to exist. So if you don't know someone's phone number, that's stored as a
Null, whereas if you know someone has no phone, that's a zls. You enter a
zls by opening and closing quotes with nothing between them.

In practice, there is no visible difference between a Null and a zls, so
it
tends to really confuse any non-technical people who use the database.
Addionally, you must remember to handle both cases whenever you design
queries, filters, reports, search forms, and so on.

If you want to create the unique composite index on the 4 fields and use
zero-length stings instead of nulls, open the table in design view and
set
these properties for each of the Text type fields you have in the index:
Allow Zero Length Yes
Default Value ""

Again, I don't really see how this will be useful for your case. To me it
does not seem logical to make a unique index such that there can be only
exactly one Jane-Doe-with-no-other-name. In most tables, I personally
think
you want to block the possibility of zero-length strings in your text
fields:
http://allenbrowne.com/bug-09.html

Hope that's useful.

message

 

[SherryScrapDog]

Thanks so much, now it works! And especially thanks for spelling it out for
me as I would not have known how to do it without you doing the SQL for me.
I'm self-taught and still have lots to learn, although I try to find what I
need by research as much as possible.

I know you probably don't care why I want to do this, but just for
information, here's my basic system (with limited details)
Name file
M-ID D-ID M-ID
Detail
1 Doe Jane 1 1
Book 20 1904
2
1 Book 35 1954
3
1 Book 60 1935
2 Doe John 4 2
Obituary 1942
5
2 Book 16 1909

Again, many thanks! Sherry

I'm not completely clear on what's happening here, but use Nz() if you want
the Append query to insert zero-length strings into Master1 where ExcelData1
has nulls.

This kind of thing:
INSERT INTO Master1 ( [Last], [First], Middle, Title )
SELECT Nz([Lastname], ""), Nz([Firstname], ""),
Nz([Middle], ""), Nz([Title], "")
FROM ExcelData1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen and thank you for your response! Yes, there can be more than one
person with the same name, and it is another table I have that gives the
researcher the detail to a name that will let them decide if this is the
person they want. For example, we might have the name Jane Doe in 15
different places in our records and it might be the same Jane Doe, or it
could be different Jane Does. The detail records (such as the year and
other
potential info) connected to the name Jane Doe will let them know. The
detail records are related to my names table by the auto-number primary
key.
I did as you suggested with the Allow Zero Length and the default of "".
My
append query, which is:
INSERT INTO Master1 ( [Last], [First], Middle, Title )
SELECT [Lastname], [Firstname], [Middle], [Title]
FROM ExcelData1;
is still loading all of the records. Please let me know if there is
anything else you can see that I am doing wrong. It does prevent the
duplicate if all 4 fields have a value.
thanks again, Sherry

You cannot have a null in a primary key field.

If you have an AutoNumber for primary key, that's not a problem. You can
have nulls in the other fields.

You can make a composite index on the fields that contain nulls, but I
don't
think making it a unique index is a good idea. Having 2 people with the
same
name is not uncommon - particularly in geneologies where children are
sometimes named after their parents. It might be better to just give a
warning if the name is not unique rather than to block it via a unique
index. To give the warning, use the BeforeUpdate event procedure of the
form
where you enter people.

If you do need to create a unique index where some fields are known to
have
no value (e.g. you know the person actually has no middle name), you can
use
a zero-length string (zls) instead of a Null. In theory, a Null means
unknown/not applicable/undefined, whereas a zls means the value is known
not
to exist. So if you don't know someone's phone number, that's stored as a
Null, whereas if you know someone has no phone, that's a zls. You enter a
zls by opening and closing quotes with nothing between them.

In practice, there is no visible difference between a Null and a zls, so
it
tends to really confuse any non-technical people who use the database.
Addionally, you must remember to handle both cases whenever you design
queries, filters, reports, search forms, and so on.

If you want to create the unique composite index on the 4 fields and use
zero-length stings instead of nulls, open the table in design view and
set
these properties for each of the Text type fields you have in the index:
Allow Zero Length Yes
Default Value ""

Again, I don't really see how this will be useful for your case. To me it
does not seem logical to make a unique index such that there can be only
exactly one Jane-Doe-with-no-other-name. In most tables, I personally
think
you want to block the possibility of zero-length strings in your text
fields:
http://allenbrowne.com/bug-09.html

Hope that's useful.

message
Hi,
I have a database full of names (for geneology) where I have Last,
First,
Middle and Title. My table has an auto-number for primary key. From
reading
posts here, I figured out how to make a composite unique key with the 4
fields. However, it is routine that some of the fields are null,
especially
(but not limited to) the middle and title fields. I load this table
from
muliptle imported Excel tables. I could have Doe, Jane in one record,
Doe,
Jane, Mary in another, and Doe, Jane, Mary, Mrs. in a 3rd record. The
problem I am having is that it is loading all records even when they
are
the
same. For example, if I have Doe, Jane, null, null in 2 records, the
second
record loads. I tried both options on the 'Ignore Null' on the Index.
I
can't require the fields because they are many times properly null. Am
I
missing something simple here? Do I need something specific in my
append
query to prevent loading the duplicates? Thanks in advance if you can
help!

 

[Dale Fye]

Sherry,

It would seem to me that you are going to lose a significant amount of data
from this table if you disallow the duplicates, unless you have some other
way of ensuring that the names are in fact the "same person".
I assume that your are importing other data from your "Book 20 1904' and
'Book 35 1954' data tables. How are you relating that data to the Name
infor you previously mentioned? I hope you are not just joining by the name
fields, and grabbing the autonumber PK value from your names table. If so,
you are undoubtedly corrupting the rest of your data. You might want to
consider including the date of birth in your first table, to help you
identify unique people, although I would venture a guess that there are
several dozen Jane Doe births every day. A additional field that might help
ensure no duplicates is Place of Birth as well as the DOB, although that
still gives you a small probability of multiples.

Once you import all of those fields, then you could write a query like the
following to insert data into your table.

INSERT INTO tbl_Geneoloy_Names ( Last_Name, First_Name, Middle_Name, Title,
DOB, POB_City )
SELECT NZ( ExcelData1.[Last_Name],"") AS Expr1,
NZ( ExcelData1.[First_Name],"") AS Expr2,
NZ( ExcelData1.[Middle_Name],"") AS Expr3,
NZ( ExcelData1.[Title],"") AS Expr4,
ExcelData1.DOB,
NZ(ExcelData1.POB_City, "") as Expr6
FROM ExcelData1
LEFT JOIN tbl_Geneoloy_Names
ON NZ(ExcelData1.Last_Name, "") = tbl_Geneoloy_Names.Last_Name)
AND NZ(ExcelData1.First_Name, "") = tbl_Geneoloy_Names.First_Name
AND NZ(ExcelData1.Middle_Name, "") = tbl_Geneoloy_Names.Middle_Name
AND NZ(ExcelData1.Title, "") = tbl_Geneoloy_Names.Title
AND NZ(ExcelData1.DOB, "") = tbl_Geneoloy_Names.DOB
AND NZ(ExcelData1.POB_City, "") = tbl_Geneoloy_Names.POB_City
WHERE (((tbl_Geneoloy_Names.ID) Is Null));

What this does, is only insert into the table those records where there is
not already a matching record. If you change the select statement to
include the ID value from tbl_Geneology_Names, and the other fields in the
SELECT and INSERT clauses of this query you can write a query to add other
fields to other tables, while captureing the Name_ID field in those tables
as well. To do this, however, you will need to change your WHERE clause to
read IS NOT NULL.

HTH
Dale

 

[SherryScrapDog]

Hi Dale,
I appreciate all of this information, especially since I am self-taught and
still have much to learn. Here is what I am dealing with: For years,
volunteers at the Courthouse have 'indexed' various books and holdings they
have. It's an on-going process. What they do is read each page of a book
for the names and write down the name and the page number. When a book is
done being read, it is typed, in Excel now (used to be Works), and then
someone proofs the typed copy. The index file for the book (in Excel) is
then corrected, sorted and printed for the genealogy society. Then, people
can go thru these hundreds of different printed lists to see if they can find
anyone they are researching. What gets put on these index files is the Last
name, First name (which can include middle and title) and the page number.
Some of the later versions folks type have the middle and title fields
separate.

So, what I am trying to do is to combine all of these files together in one
database so a person can look up a name and if they see a promising name, it
will show them the books the name appears in. Researchers (from what I
understand) are very used to pouring thru names and possible mis-spellings.
I am wanting to show a name only one time so they do not have to scroll thru
many instances of the same name. What they do scroll thru, after they have
selected a name, is a list of books the name appeared in, and what year they
belong to.

I could possibly store the name with the year of the book (most are actually
a year-range). I will ask the folks who will be using this system if they
would like that. If they do, I will re-design and use your method, I just
won't have the same fields as your example.

Please believe I am not losing any records; I have been carefully checking
with each file I load.

Thanks for the information if I need it, and I'm always happy to learn more
about Access. I'm just beginning to realize all it can do. Sherry

Sherry,

It would seem to me that you are going to lose a significant amount of data
from this table if you disallow the duplicates, unless you have some other
way of ensuring that the names are in fact the "same person".
I assume that your are importing other data from your "Book 20 1904' and
'Book 35 1954' data tables. How are you relating that data to the Name
infor you previously mentioned? I hope you are not just joining by the name
fields, and grabbing the autonumber PK value from your names table. If so,
you are undoubtedly corrupting the rest of your data. You might want to
consider including the date of birth in your first table, to help you
identify unique people, although I would venture a guess that there are
several dozen Jane Doe births every day. A additional field that might help
ensure no duplicates is Place of Birth as well as the DOB, although that
still gives you a small probability of multiples.

Once you import all of those fields, then you could write a query like the
following to insert data into your table.

INSERT INTO tbl_Geneoloy_Names ( Last_Name, First_Name, Middle_Name, Title,
DOB, POB_City )
SELECT NZ( ExcelData1.[Last_Name],"") AS Expr1,
NZ( ExcelData1.[First_Name],"") AS Expr2,
NZ( ExcelData1.[Middle_Name],"") AS Expr3,
NZ( ExcelData1.[Title],"") AS Expr4,
ExcelData1.DOB,
NZ(ExcelData1.POB_City, "") as Expr6
FROM ExcelData1
LEFT JOIN tbl_Geneoloy_Names
ON NZ(ExcelData1.Last_Name, "") = tbl_Geneoloy_Names.Last_Name)
AND NZ(ExcelData1.First_Name, "") = tbl_Geneoloy_Names.First_Name
AND NZ(ExcelData1.Middle_Name, "") = tbl_Geneoloy_Names.Middle_Name
AND NZ(ExcelData1.Title, "") = tbl_Geneoloy_Names.Title
AND NZ(ExcelData1.DOB, "") = tbl_Geneoloy_Names.DOB
AND NZ(ExcelData1.POB_City, "") = tbl_Geneoloy_Names.POB_City
WHERE (((tbl_Geneoloy_Names.ID) Is Null));

What this does, is only insert into the table those records where there is
not already a matching record. If you change the select statement to
include the ID value from tbl_Geneology_Names, and the other fields in the
SELECT and INSERT clauses of this query you can write a query to add other
fields to other tables, while captureing the Name_ID field in those tables
as well. To do this, however, you will need to change your WHERE clause to
read IS NOT NULL.

HTH
Dale

Hi,
I have a database full of names (for geneology) where I have Last, First,
Middle and Title. My table has an auto-number for primary key. From
reading
posts here, I figured out how to make a composite unique key with the 4
fields. However, it is routine that some of the fields are null,
especially
(but not limited to) the middle and title fields. I load this table from
muliptle imported Excel tables. I could have Doe, Jane in one record,
Doe,
Jane, Mary in another, and Doe, Jane, Mary, Mrs. in a 3rd record. The
problem I am having is that it is loading all records even when they are
the
same. For example, if I have Doe, Jane, null, null in 2 records, the
second
record loads. I tried both options on the 'Ignore Null' on the Index. I
can't require the fields because they are many times properly null. Am I
missing something simple here? Do I need something specific in my append
query to prevent loading the duplicates? Thanks in advance if you can
help!

 

[Dale Fye]

Now that I understand better about what you are trying to do, I'll look back
at your posts and those that Allen did and get back to you.

You might also want to consider using a version of the Soundex algorithm for
your searches ( http://en.wikipedia.org/wiki/Soundex ). It is an algorithm
that allows a user to type Smith in a search box and get variants of that
the might include Smithe, Smyth, etc.

Dale

Hi Dale,
I appreciate all of this information, especially since I am self-taught
and
still have much to learn. Here is what I am dealing with: For years,
volunteers at the Courthouse have 'indexed' various books and holdings
they
have. It's an on-going process. What they do is read each page of a book
for the names and write down the name and the page number. When a book is
done being read, it is typed, in Excel now (used to be Works), and then
someone proofs the typed copy. The index file for the book (in Excel) is
then corrected, sorted and printed for the genealogy society. Then,
people
can go thru these hundreds of different printed lists to see if they can
find
anyone they are researching. What gets put on these index files is the
Last
name, First name (which can include middle and title) and the page number.
Some of the later versions folks type have the middle and title fields
separate.

So, what I am trying to do is to combine all of these files together in
one
database so a person can look up a name and if they see a promising name,
it
will show them the books the name appears in. Researchers (from what I
understand) are very used to pouring thru names and possible
mis-spellings.
I am wanting to show a name only one time so they do not have to scroll
thru
many instances of the same name. What they do scroll thru, after they
have
selected a name, is a list of books the name appeared in, and what year
they
belong to.

I could possibly store the name with the year of the book (most are
actually
a year-range). I will ask the folks who will be using this system if they
would like that. If they do, I will re-design and use your method, I just
won't have the same fields as your example.

Please believe I am not losing any records; I have been carefully checking
with each file I load.

Thanks for the information if I need it, and I'm always happy to learn
more
about Access. I'm just beginning to realize all it can do. Sherry

Sherry,

It would seem to me that you are going to lose a significant amount of
data
from this table if you disallow the duplicates, unless you have some
other
way of ensuring that the names are in fact the "same person".
I assume that your are importing other data from your "Book 20 1904' and
'Book 35 1954' data tables. How are you relating that data to the Name
infor you previously mentioned? I hope you are not just joining by the
name
fields, and grabbing the autonumber PK value from your names table. If
so,
you are undoubtedly corrupting the rest of your data. You might want to
consider including the date of birth in your first table, to help you
identify unique people, although I would venture a guess that there are
several dozen Jane Doe births every day. A additional field that might
help
ensure no duplicates is Place of Birth as well as the DOB, although that
still gives you a small probability of multiples.

Once you import all of those fields, then you could write a query like
the
following to insert data into your table.

INSERT INTO tbl_Geneoloy_Names ( Last_Name, First_Name, Middle_Name,
Title,
DOB, POB_City )
SELECT NZ( ExcelData1.[Last_Name],"") AS Expr1,
NZ( ExcelData1.[First_Name],"") AS Expr2,
NZ( ExcelData1.[Middle_Name],"") AS Expr3,
NZ( ExcelData1.[Title],"") AS Expr4,
ExcelData1.DOB,
NZ(ExcelData1.POB_City, "") as Expr6
FROM ExcelData1
LEFT JOIN tbl_Geneoloy_Names
ON NZ(ExcelData1.Last_Name, "") =
tbl_Geneoloy_Names.Last_Name)
AND NZ(ExcelData1.First_Name, "") =
tbl_Geneoloy_Names.First_Name
AND NZ(ExcelData1.Middle_Name, "") =
tbl_Geneoloy_Names.Middle_Name
AND NZ(ExcelData1.Title, "") = tbl_Geneoloy_Names.Title
AND NZ(ExcelData1.DOB, "") = tbl_Geneoloy_Names.DOB
AND NZ(ExcelData1.POB_City, "") = tbl_Geneoloy_Names.POB_City
WHERE (((tbl_Geneoloy_Names.ID) Is Null));

What this does, is only insert into the table those records where there
is
not already a matching record. If you change the select statement to
include the ID value from tbl_Geneology_Names, and the other fields in
the
SELECT and INSERT clauses of this query you can write a query to add
other
fields to other tables, while captureing the Name_ID field in those
tables
as well. To do this, however, you will need to change your WHERE clause
to
read IS NOT NULL.

HTH
Dale

message

Hi,
I have a database full of names (for geneology) where I have Last,
First,
Middle and Title. My table has an auto-number for primary key. From
reading
posts here, I figured out how to make a composite unique key with the 4
fields. However, it is routine that some of the fields are null,
especially
(but not limited to) the middle and title fields. I load this table
from
muliptle imported Excel tables. I could have Doe, Jane in one record,
Doe,
Jane, Mary in another, and Doe, Jane, Mary, Mrs. in a 3rd record. The
problem I am having is that it is loading all records even when they
are
the
same. For example, if I have Doe, Jane, null, null in 2 records, the
second
record loads. I tried both options on the 'Ignore Null' on the Index.
I
can't require the fields because they are many times properly null. Am
I
missing something simple here? Do I need something specific in my
append
query to prevent loading the duplicates? Thanks in advance if you can
help!

 

[SherryScrapDog]

Thanks Dale! And I hear you about the Soundex. I do plan on tackling that
and actually have the Soundex field in the table already. I will just need
help with the code to do it. I will need to do an update query (I assume),
but it seems like it would take VBA code to create the soundex code. I
really appreciate your web link and will be looking at this carefully. I
already know what soundex is and what the rules are; I just don't know how to
make it happen in Access. That, and allowing them to also enter an optional
first name for the search are the 2 big goals I have now for this system. I
just got back from our regular Monday night indexing session and showed a
couple of folks what I have so far (with about 35,000 test, but real,
records) and they loved it. Of course, when their option is to look thru all
of those individual printed reports, they would probably love anything. I
can't get the first name option to work yet (getting compile errors), but
will continue to try by looking at examples. Thanks for all of your info!!
Sherry

Now that I understand better about what you are trying to do, I'll look back
at your posts and those that Allen did and get back to you.

You might also want to consider using a version of the Soundex algorithm for
your searches ( http://en.wikipedia.org/wiki/Soundex ). It is an algorithm
that allows a user to type Smith in a search box and get variants of that
the might include Smithe, Smyth, etc.

Dale

Hi Dale,
I appreciate all of this information, especially since I am self-taught
and
still have much to learn. Here is what I am dealing with: For years,
volunteers at the Courthouse have 'indexed' various books and holdings
they
have. It's an on-going process. What they do is read each page of a book
for the names and write down the name and the page number. When a book is
done being read, it is typed, in Excel now (used to be Works), and then
someone proofs the typed copy. The index file for the book (in Excel) is
then corrected, sorted and printed for the genealogy society. Then,
people
can go thru these hundreds of different printed lists to see if they can
find
anyone they are researching. What gets put on these index files is the
Last
name, First name (which can include middle and title) and the page number.
Some of the later versions folks type have the middle and title fields
separate.

So, what I am trying to do is to combine all of these files together in
one
database so a person can look up a name and if they see a promising name,
it
will show them the books the name appears in. Researchers (from what I
understand) are very used to pouring thru names and possible
mis-spellings.
I am wanting to show a name only one time so they do not have to scroll
thru
many instances of the same name. What they do scroll thru, after they
have
selected a name, is a list of books the name appeared in, and what year
they
belong to.

I could possibly store the name with the year of the book (most are
actually
a year-range). I will ask the folks who will be using this system if they
would like that. If they do, I will re-design and use your method, I just
won't have the same fields as your example.

Please believe I am not losing any records; I have been carefully checking
with each file I load.

Thanks for the information if I need it, and I'm always happy to learn
more
about Access. I'm just beginning to realize all it can do. Sherry

Sherry,

It would seem to me that you are going to lose a significant amount of
data
from this table if you disallow the duplicates, unless you have some
other
way of ensuring that the names are in fact the "same person".
I assume that your are importing other data from your "Book 20 1904' and
'Book 35 1954' data tables. How are you relating that data to the Name
infor you previously mentioned? I hope you are not just joining by the
name
fields, and grabbing the autonumber PK value from your names table. If
so,
you are undoubtedly corrupting the rest of your data. You might want to
consider including the date of birth in your first table, to help you
identify unique people, although I would venture a guess that there are
several dozen Jane Doe births every day. A additional field that might
help
ensure no duplicates is Place of Birth as well as the DOB, although that
still gives you a small probability of multiples.

Once you import all of those fields, then you could write a query like
the
following to insert data into your table.

INSERT INTO tbl_Geneoloy_Names ( Last_Name, First_Name, Middle_Name,
Title,
DOB, POB_City )
SELECT NZ( ExcelData1.[Last_Name],"") AS Expr1,
NZ( ExcelData1.[First_Name],"") AS Expr2,
NZ( ExcelData1.[Middle_Name],"") AS Expr3,
NZ( ExcelData1.[Title],"") AS Expr4,
ExcelData1.DOB,
NZ(ExcelData1.POB_City, "") as Expr6
FROM ExcelData1
LEFT JOIN tbl_Geneoloy_Names
ON NZ(ExcelData1.Last_Name, "") =
tbl_Geneoloy_Names.Last_Name)
AND NZ(ExcelData1.First_Name, "") =
tbl_Geneoloy_Names.First_Name
AND NZ(ExcelData1.Middle_Name, "") =
tbl_Geneoloy_Names.Middle_Name
AND NZ(ExcelData1.Title, "") = tbl_Geneoloy_Names.Title
AND NZ(ExcelData1.DOB, "") = tbl_Geneoloy_Names.DOB
AND NZ(ExcelData1.POB_City, "") = tbl_Geneoloy_Names.POB_City
WHERE (((tbl_Geneoloy_Names.ID) Is Null));

What this does, is only insert into the table those records where there
is
not already a matching record. If you change the select statement to
include the ID value from tbl_Geneology_Names, and the other fields in
the
SELECT and INSERT clauses of this query you can write a query to add
other
fields to other tables, while captureing the Name_ID field in those
tables
as well. To do this, however, you will need to change your WHERE clause
to
read IS NOT NULL.

HTH
Dale

message
Hi,
I have a database full of names (for geneology) where I have Last,
First,
Middle and Title. My table has an auto-number for primary key. From
reading
posts here, I figured out how to make a composite unique key with the 4
fields. However, it is routine that some of the fields are null,
especially
(but not limited to) the middle and title fields. I load this table
from
muliptle imported Excel tables. I could have Doe, Jane in one record,
Doe,
Jane, Mary in another, and Doe, Jane, Mary, Mrs. in a 3rd record. The
problem I am having is that it is loading all records even when they
are
the
same. For example, if I have Doe, Jane, null, null in 2 records, the
second
record loads. I tried both options on the 'Ignore Null' on the Index.
I
can't require the fields because they are many times properly null. Am
I
missing something simple here? Do I need something specific in my
append
query to prevent loading the duplicates? Thanks in advance if you can
help!

 

[Allen Browne]

Here's a Soundex() you can copy:
http://allenbrowne.com/vba-Soundex.html

 

[SherryScrapDog]

Thanks for the code Allen! I've made the module (I am so excited about this)
and it compiles just fine. I am having trouble using it though, and I know
this is because I just don't understand how things work exactly yet. I tried
to do an update query, to update my records with the soundex code, but I
don't know how to make it work. I made a form and followed your instructions
with the query (replacing my field name) and I get an error: Undefined
Function 'Soundex' in expression. I'm sure there is just a little piece I'm
not understanding. Can I do an update query to store the soundex in my
records? And, should I? I think this file will grow to 500,000 records or
more. Or, it looks like this is not necessary from your code (once I can get
it to work). I think your code means I would put the txtName on Form1, then
use command button to bring up form with list of names, and that is what I
tried and go the above error. Please let me know if I am way off base here
and what I should be doing. I named the module Soundex, was this the right
thing to do? I'm sorry to be such a pain. Thanks if you can help! Sherry

 

[Allen Browne]

1. Make sure you put the code into a stand-alone module, i.e. one that shows
up on the Modules tab of the Database window, not in the module of a form.

2. To test it, open the Immediate Window, and enter:
? Soundex("Sherry")

3. If you have a field named CompanyName, and another one named CNSoundex to
hold the soundex value, type this expression into the Update row in query
design under your CNSoundex field:
Soundes([CompanyName])

 

[SherryScrapDog]

Hi Allen,
I have a module under the Modules tab that is named Soundex. I copied your
code exactly ( you have a great site! I am studying it). When I created the
new module, it had 'Option Compare Database' at the top, and I left this
there. So, the module has that at the top, then the soundex code. I brought
up the module, then brought up an immediate window and typed in your test,
and it came back with: Compile error, Expected variable or procecdure, not
module. Does this tell you anything? Also, I made the query as you
suggested (knowing it probably would not work) and came up with the same
error as before, which is: undefined function 'Soundex' in expression. I'm
hoping maybe the compile error with the test I did might tell you what I am
doing wrong.
Thanks so much for your time! Sherry

1. Make sure you put the code into a stand-alone module, i.e. one that shows
up on the Modules tab of the Database window, not in the module of a form.

2. To test it, open the Immediate Window, and enter:
? Soundex("Sherry")

3. If you have a field named CompanyName, and another one named CNSoundex to
hold the soundex value, type this expression into the Update row in query
design under your CNSoundex field:
Soundes([CompanyName])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the code Allen! I've made the module (I am so excited about
this)
and it compiles just fine. I am having trouble using it though, and I
know
this is because I just don't understand how things work exactly yet. I
tried
to do an update query, to update my records with the soundex code, but I
don't know how to make it work. I made a form and followed your
instructions
with the query (replacing my field name) and I get an error: Undefined
Function 'Soundex' in expression. I'm sure there is just a little piece
I'm
not understanding. Can I do an update query to store the soundex in my
records? And, should I? I think this file will grow to 500,000 records
or
more. Or, it looks like this is not necessary from your code (once I can
get
it to work). I think your code means I would put the txtName on Form1,
then
use command button to bring up form with list of names, and that is what I
tried and go the above error. Please let me know if I am way off base
here
and what I should be doing. I named the module Soundex, was this the
right
thing to do? I'm sorry to be such a pain. Thanks if you can help!
Sherry

 

[Allen Browne]

You cannot name the module the same as the function.
Name it basSoundex, or Module1, or something.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I have a module under the Modules tab that is named Soundex. I copied
your
code exactly ( you have a great site! I am studying it). When I created
the
new module, it had 'Option Compare Database' at the top, and I left this
there. So, the module has that at the top, then the soundex code. I
brought
up the module, then brought up an immediate window and typed in your test,
and it came back with: Compile error, Expected variable or procecdure, not
module. Does this tell you anything? Also, I made the query as you
suggested (knowing it probably would not work) and came up with the same
error as before, which is: undefined function 'Soundex' in expression.
I'm
hoping maybe the compile error with the test I did might tell you what I
am
doing wrong.
Thanks so much for your time! Sherry

1. Make sure you put the code into a stand-alone module, i.e. one that
shows
up on the Modules tab of the Database window, not in the module of a
form.

2. To test it, open the Immediate Window, and enter:
? Soundex("Sherry")

3. If you have a field named CompanyName, and another one named CNSoundex
to
hold the soundex value, type this expression into the Update row in query
design under your CNSoundex field:
Soundes([CompanyName])

message

Thanks for the code Allen! I've made the module (I am so excited about
this)
and it compiles just fine. I am having trouble using it though, and I
know
this is because I just don't understand how things work exactly yet. I
tried
to do an update query, to update my records with the soundex code, but
I
don't know how to make it work. I made a form and followed your
instructions
with the query (replacing my field name) and I get an error: Undefined
Function 'Soundex' in expression. I'm sure there is just a little
piece
I'm
not understanding. Can I do an update query to store the soundex in my
records? And, should I? I think this file will grow to 500,000
records
or
more. Or, it looks like this is not necessary from your code (once I
can
get
it to work). I think your code means I would put the txtName on Form1,
then
use command button to bring up form with list of names, and that is
what I
tried and go the above error. Please let me know if I am way off base
here
and what I should be doing. I named the module Soundex, was this the
right
thing to do? I'm sorry to be such a pain. Thanks if you can help!
Sherry

:

Here's a Soundex() you can copy:
http://allenbrowne.com/vba-Soundex.html

message
Thanks Dale! And I hear you about the Soundex. I do plan on
tackling
that
and actually have the Soundex field in the table already. I will
just
need
help with the code ...

 

[SherryScrapDog]

Thanks, Thanks, Thanks! I knew it was something ignorant on my part. Works
beautiful. And now I have learned about modules. I have downloaded your
search database and will now make a search form to use multiple options to
look up names using your example. This is a volunteer project for me and I
really love it, especially the more I learn and can do with Access. After I
get this all set up and loaded for them at the courthouse, my next project
will be to put it on the website (I also maintain the website), which will be
a whole new learning experience. I am progressing! Thanks for all of your
time on this! Sherry

You cannot name the module the same as the function.
Name it basSoundex, or Module1, or something.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I have a module under the Modules tab that is named Soundex. I copied
your
code exactly ( you have a great site! I am studying it). When I created
the
new module, it had 'Option Compare Database' at the top, and I left this
there. So, the module has that at the top, then the soundex code. I
brought
up the module, then brought up an immediate window and typed in your test,
and it came back with: Compile error, Expected variable or procecdure, not
module. Does this tell you anything? Also, I made the query as you
suggested (knowing it probably would not work) and came up with the same
error as before, which is: undefined function 'Soundex' in expression.
I'm
hoping maybe the compile error with the test I did might tell you what I
am
doing wrong.
Thanks so much for your time! Sherry

1. Make sure you put the code into a stand-alone module, i.e. one that
shows
up on the Modules tab of the Database window, not in the module of a
form.

2. To test it, open the Immediate Window, and enter:
? Soundex("Sherry")

3. If you have a field named CompanyName, and another one named CNSoundex
to
hold the soundex value, type this expression into the Update row in query
design under your CNSoundex field:
Soundes([CompanyName])

message
Thanks for the code Allen! I've made the module (I am so excited about
this)
and it compiles just fine. I am having trouble using it though, and I
know
this is because I just don't understand how things work exactly yet. I
tried
to do an update query, to update my records with the soundex code, but
I
don't know how to make it work. I made a form and followed your
instructions
with the query (replacing my field name) and I get an error: Undefined
Function 'Soundex' in expression. I'm sure there is just a little
piece
I'm
not understanding. Can I do an update query to store the soundex in my
records? And, should I? I think this file will grow to 500,000
records
or
more. Or, it looks like this is not necessary from your code (once I
can
get
it to work). I think your code means I would put the txtName on Form1,
then
use command button to bring up form with list of names, and that is
what I
tried and go the above error. Please let me know if I am way off base
here
and what I should be doing. I named the module Soundex, was this the
right
thing to do? I'm sorry to be such a pain. Thanks if you can help!
Sherry

:

Here's a Soundex() you can copy:
http://allenbrowne.com/vba-Soundex.html

message
Thanks Dale! And I hear you about the Soundex. I do plan on
tackling
that
and actually have the Soundex field in the table already. I will
just
need
help with the code ...