Append to table but ensure no duplicates

[Russ via AccessMonster.com]

Help,
I have a card scanner that will create an Access database with the table
called Contacts, I need to append / insert this information into my database
table called tblContacts (tblContacts already has several hundred records)
ensuring no duplicates, I have linked the Contacts table but do not know how
to make an append query to do this. Can anyone please provide helpful
suggestions?
Thanks in advance!
Russ

Contacts tblContacts
First Name FirstName
Last Name LastName
Company Name CompanyName

 

[Marshall Barton]

I have a card scanner that will create an Access database with the table
called Contacts, I need to append / insert this information into my database
table called tblContacts (tblContacts already has several hundred records)
ensuring no duplicates, I have linked the Contacts table but do not know how
to make an append query to do this. Can anyone please provide helpful
suggestions?
Thanks in advance!
Russ

Contacts tblContacts
First Name FirstName
Last Name LastName
Company Name CompanyName

If you have a unique index on any of the fields you are
importing, the inported record will be rejected as a
duplicate index.

If you can't rely on that, you will have to use code to
check each record according your idea of duplication ans
either add it as a new record or not.

 

[Russ via AccessMonster.com]

no unique index, since there can be the same first name, last name and
company name.
michael smith
brad smith
both work at UPS
Any other ideas?

I have a card scanner that will create an Access database with the table
called Contacts, I need to append / insert this information into my database

[quoted text clipped - 9 lines]

Last Name LastName
Company Name CompanyName

If you have a unique index on any of the fields you are
importing, the inported record will be rejected as a
duplicate index.

If you can't rely on that, you will have to use code to
check each record according your idea of duplication ans
either add it as a new record or not.

 

[Russ via AccessMonster.com]

any ideas?

no unique index, since there can be the same first name, last name and
company name.
michael smith
brad smith
both work at UPS
Any other ideas?
[quoted text clipped - 9 lines]

check each record according your idea of duplication ans
either add it as a new record or not.

 

[Gary Walter]

I wonder if you did not misspeak when you said

"there can be the same first name, last name *and*
company name"

that does not leave many alternatives for determining
what is a duplicate....

if table already has record for

brad smith UPS

and you want to a query to determine if

brad smith UPS

is a duplicate,

or just a different

brad smith UPS

perhaps you see a dilemna here...

typically, one might create a multiple index
on the 3 fields which would not allow a record
with the same name/company to be added
as Marshall stated.

just to make sure I understand...

is it that you can accept more than one
record in the table with same name/company,
but, you for sure don't want to append a
record that has the same name/company?

maybe?

INSERT INTO
tblContacts (FirstName, LastName, CompanyName)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name]
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

any ideas?

no unique index, since there can be the same first name, last name and
company name.
michael smith
brad smith
both work at UPS
Any other ideas?

I have a card scanner that will create an Access database with the table
called Contacts, I need to append / insert this information into my
database

[quoted text clipped - 9 lines]

check each record according your idea of duplication ans
either add it as a new record or not.

--

Contacts tblContacts
First Name FirstName
Last Name LastName
Company Name CompanyName

 

[Russ via AccessMonster.com]

Gary,
Looks good, and seems to be doing the trick, but I am new and do not
undestand the logic to what you have done. Is there anyway you can explain
with detail to why you did what you did? Any why your using the is null on
the company name? Sure would appreciate it. I assume if I go into the design
view and duplicate what you did and add address, city state etc. this would
not complicate things?
Russ

I wonder if you did not misspeak when you said

"there can be the same first name, last name *and*
company name"

that does not leave many alternatives for determining
what is a duplicate....

if table already has record for

brad smith UPS

and you want to a query to determine if

brad smith UPS

is a duplicate,

or just a different

brad smith UPS

perhaps you see a dilemna here...

typically, one might create a multiple index
on the 3 fields which would not allow a record
with the same name/company to be added
as Marshall stated.

just to make sure I understand...

is it that you can accept more than one
record in the table with same name/company,
but, you for sure don't want to append a
record that has the same name/company?

maybe?

INSERT INTO
tblContacts (FirstName, LastName, CompanyName)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name]
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

any ideas?

[quoted text clipped - 11 lines]
Contacts tblContacts
First Name FirstName
Last Name LastName
Company Name CompanyName

 

[Gary Walter]

the left join says

show me all the records from Contacts
and any matching records from tblContacts

if you look at results from following query,
you will notice that all 3 fields from tblContacts
(aliased as "t") are Null when a match did not
occur. Those are the records we wanted to
append to tblContacts, i.e., the unmatched
records when firstname and lastname and
companyname were not exactly the same.

SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
t.FirstName,
t.LastName,
t.CompanyName
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName;


So, we added the WHERE clause to pick
up only those *unmatched* records for the
append query.

You can add other fields no problem.

If you still only care about first/last/company,
then just add to value list and SELECT
clause....


INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

If you want all fields then to "match,"
you will also have to add them to ON clause.

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
AND
C.Address = t.Address
AND
C.City = t.City
AND
C.State = t.State
WHERE
t.CompanyName IS NULL;

Looks good, and seems to be doing the trick, but I am new and do not
undestand the logic to what you have done. Is there anyway you can explain
with detail to why you did what you did? Any why your using the is null on
the company name? Sure would appreciate it. I assume if I go into the
design
view and duplicate what you did and add address, city state etc. this
would
not complicate things?
Russ

I wonder if you did not misspeak when you said

"there can be the same first name, last name *and*
company name"

that does not leave many alternatives for determining
what is a duplicate....

if table already has record for

brad smith UPS

and you want to a query to determine if

brad smith UPS

is a duplicate,

or just a different

brad smith UPS

perhaps you see a dilemna here...

typically, one might create a multiple index
on the 3 fields which would not allow a record
with the same name/company to be added
as Marshall stated.

just to make sure I understand...

is it that you can accept more than one
record in the table with same name/company,
but, you for sure don't want to append a
record that has the same name/company?

maybe?

INSERT INTO
tblContacts (FirstName, LastName, CompanyName)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name]
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

any ideas?

[quoted text clipped - 11 lines]

check each record according your idea of duplication ans
either add it as a new record or not.

Contacts tblContacts
First Name FirstName
Last Name LastName
Company Name CompanyName

 

[Russ via AccessMonster.com]

Gary,
I really appreciate you taking the time to help me and explain the process
you used to get to the solution and I thank you!!!
Russ

the left join says

show me all the records from Contacts
and any matching records from tblContacts

if you look at results from following query,
you will notice that all 3 fields from tblContacts
(aliased as "t") are Null when a match did not
occur. Those are the records we wanted to
append to tblContacts, i.e., the unmatched
records when firstname and lastname and
companyname were not exactly the same.

SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
t.FirstName,
t.LastName,
t.CompanyName
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName;

So, we added the WHERE clause to pick
up only those *unmatched* records for the
append query.

You can add other fields no problem.

If you still only care about first/last/company,
then just add to value list and SELECT
clause....

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

If you want all fields then to "match,"
you will also have to add them to ON clause.

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
AND
C.Address = t.Address
AND
C.City = t.City
AND
C.State = t.State
WHERE
t.CompanyName IS NULL;

Looks good, and seems to be doing the trick, but I am new and do not
undestand the logic to what you have done. Is there anyway you can explain

[quoted text clipped - 73 lines]

 

[Marshall Barton]

Very nice explanation Gary. Keep up the good work ;-)

 

[Russ via AccessMonster.com]

Gary,

Once I added some more fields, I was getting an error due to A zero-length
string is an invalid entry, what can I do to get around this I assume this is
because some fields in the Contacts table are empty?
Thanks

Russ

the left join says

show me all the records from Contacts
and any matching records from tblContacts

if you look at results from following query,
you will notice that all 3 fields from tblContacts
(aliased as "t") are Null when a match did not
occur. Those are the records we wanted to
append to tblContacts, i.e., the unmatched
records when firstname and lastname and
companyname were not exactly the same.

SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
t.FirstName,
t.LastName,
t.CompanyName
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName;

So, we added the WHERE clause to pick
up only those *unmatched* records for the
append query.

You can add other fields no problem.

If you still only care about first/last/company,
then just add to value list and SELECT
clause....

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

If you want all fields then to "match,"
you will also have to add them to ON clause.

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
AND
C.Address = t.Address
AND
C.City = t.City
AND
C.State = t.State
WHERE
t.CompanyName IS NULL;

Looks good, and seems to be doing the trick, but I am new and do not
undestand the logic to what you have done. Is there anyway you can explain

[quoted text clipped - 73 lines]

 

[Gary Walter]

Sorry Russ, once you've said "thank you"
you are not allowed to ask any further questions.

You have at least 2 options:

1) open up tblContacts in Design mode,
identify fields that may be a problem
and change 2 settings for those fields to

Required = no
Allow Zero Length = yes

2) identify potentially "blank" Contacts fields
you may be appending, and massage
query for those fields. For example....

In the append query Design grid,
it might look something like:

Field: City
Table: Contacts
Sort:
Append To: City
Criteria:
Or:

change it to:

Field: IIF(Len([City]) > 0, [City], "N/A")
Table: Contacts
Sort:
Append To: City
Criteria:
Or:

if Contacts.City was "blank", it will append "N/A"

if Contacts.City is not "blank," it will append value
of Contacts.City



"Russ wrote:>

Once I added some more fields, I was getting an error due to A zero-length
string is an invalid entry, what can I do to get around this I assume this
is
because some fields in the Contacts table are empty?
Thanks

Russ

the left join says

show me all the records from Contacts
and any matching records from tblContacts

if you look at results from following query,
you will notice that all 3 fields from tblContacts
(aliased as "t") are Null when a match did not
occur. Those are the records we wanted to
append to tblContacts, i.e., the unmatched
records when firstname and lastname and
companyname were not exactly the same.

SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
t.FirstName,
t.LastName,
t.CompanyName
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName;

So, we added the WHERE clause to pick
up only those *unmatched* records for the
append query.

You can add other fields no problem.

If you still only care about first/last/company,
then just add to value list and SELECT
clause....

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

If you want all fields then to "match,"
you will also have to add them to ON clause.

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
AND
C.Address = t.Address
AND
C.City = t.City
AND
C.State = t.State
WHERE
t.CompanyName IS NULL;

Looks good, and seems to be doing the trick, but I am new and do not
undestand the logic to what you have done. Is there anyway you can
explain

[quoted text clipped - 73 lines]

Last Name LastName
Company Name CompanyName

 

[Russ via AccessMonster.com]

Gary, before I say thanks again...
What is the advantage and or disadvantage of doing this;
Required = no
Allow Zero Length = yes
what problmes my arise in the future by allowing Zero Length, there must be a
reason the default is no?

Sorry Russ, once you've said "thank you"
you are not allowed to ask any further questions.

You have at least 2 options:

1) open up tblContacts in Design mode,
identify fields that may be a problem
and change 2 settings for those fields to

Required = no
Allow Zero Length = yes

2) identify potentially "blank" Contacts fields
you may be appending, and massage
query for those fields. For example....

In the append query Design grid,
it might look something like:

Field: City
Table: Contacts
Sort:
Append To: City
Criteria:
Or:

change it to:

Field: IIF(Len([City]) > 0, [City], "N/A")
Table: Contacts
Sort:
Append To: City
Criteria:
Or:

if Contacts.City was "blank", it will append "N/A"

if Contacts.City is not "blank," it will append value
of Contacts.City

"Russ wrote:>

Once I added some more fields, I was getting an error due to A zero-length
string is an invalid entry, what can I do to get around this I assume this

[quoted text clipped - 106 lines]

 

[John Spencer]

If the source file has zero-length string (ZLS) values in it then you will need
to convert the values to nulls in the query or change the relevant fields in
tblContacts to accept ZLS values. I prefer the former solution myself, but the
latter is quicker and easier.

So for each field in Contacts (unless you know which might contain ZLS) you need
an expression such as
IIF(Len(TRIM(C.[First Name] & "") = 0, Null, C.[First Name]) as fFirst

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
IIF(Len(TRIM(C.[First Name] & "") = 0, Null, C.[First Name]) as fFirst,
IIF(Len(TRIM(C.[Last name] & "") =0,Null, C.[Last Name]) as fLast,
<<< Repeat for other fields as needed >>>
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
AND
C.Address = t.Address
AND
C.City = t.City
AND
C.State = t.State
WHERE
t.CompanyName IS NULL;

You might want to add to the where clause and check for null values in
t.FirstName and t.LastName also. That depends on your data - For instance, can
t.CompanyName ever be null when t.FirstName and t.lastName have a value.

the left join says

show me all the records from Contacts
and any matching records from tblContacts

if you look at results from following query,
you will notice that all 3 fields from tblContacts
(aliased as "t") are Null when a match did not
occur. Those are the records we wanted to
append to tblContacts, i.e., the unmatched
records when firstname and lastname and
companyname were not exactly the same.

SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
t.FirstName,
t.LastName,
t.CompanyName
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName;

So, we added the WHERE clause to pick
up only those *unmatched* records for the
append query.

You can add other fields no problem.

If you still only care about first/last/company,
then just add to value list and SELECT
clause....

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

If you want all fields then to "match,"
you will also have to add them to ON clause.

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name],
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
AND
C.Address = t.Address
AND
C.City = t.City
AND
C.State = t.State
WHERE
t.CompanyName IS NULL;

Looks good, and seems to be doing the trick, but I am new and do not
undestand the logic to what you have done. Is there anyway you can explain
with detail to why you did what you did? Any why your using the is null on
the company name? Sure would appreciate it. I assume if I go into the
design
view and duplicate what you did and add address, city state etc. this
would
not complicate things?
Russ

I wonder if you did not misspeak when you said

"there can be the same first name, last name *and*
company name"

that does not leave many alternatives for determining
what is a duplicate....

if table already has record for

brad smith UPS

and you want to a query to determine if

brad smith UPS

is a duplicate,

or just a different

brad smith UPS

perhaps you see a dilemna here...

typically, one might create a multiple index
on the 3 fields which would not allow a record
with the same name/company to be added
as Marshall stated.

just to make sure I understand...

is it that you can accept more than one
record in the table with same name/company,
but, you for sure don't want to append a
record that has the same name/company?

maybe?

INSERT INTO
tblContacts (FirstName, LastName, CompanyName)
SELECT
C.[First Name],
C.[Last name],
C.[Company Name]
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName
WHERE
t.CompanyName IS NULL;

any ideas?

[quoted text clipped - 11 lines]
check each record according your idea of duplication ans
either add it as a new record or not.

Contacts tblContacts
First Name FirstName
Last Name LastName
Company Name CompanyName

 

[Gary Walter]

A zero-length string ("") contains no characters
but "looks like" a NULL. You see posts all the
time here where someone is filtering for Null on
some field but not getting all expected records
from the query results because some (or all) of
the "blanks" are not really Nulls.
..

I might shy away from setting AllowZeroLength
to Yes for that reason. But spaces "look like"
a Null also. In a perfect world, I would set

AZL = No
Req = Yes

*** mostly quote from Access Help ****

The Required (Req) property determines only
whether a Null value is valid for the field.

If the AllowZeroLength (AZL) property is set to Yes,
a zero-length string (ZLS) will be a valid value
for the field regardless of the setting of the
Required property.

AZL Req User's action Value stored

No No Presses ENTER Null
Presses SPACEBAR Null
Enters ZLS (not allowed)

Yes No Presses ENTER Null
Presses SPACEBAR Null
Enters ZLS ZLS

No Yes Presses ENTER (not allowed)
Presses SPACEBAR (not allowed)
Enters ZLS (not allowed)

Yes Yes Presses ENTER (not allowed)
Presses SPACEBAR ZLS
Enters ZLS ZLS
*** unquote ***

But it's not a perfect world....
Your business and what you intend to do with
the data drives data design...

In one of our apps at work we keep track of
special orders for books. Our "business" says
we want to be able to contact a customer when
a book comes in. We don't allow a record to be
saved unless it has at least a phone number and address
for the customer.

You can also "make contact" via email address or fax
but not everyone may be able to (or want to) provide those.
I know I give out as little personal info as I can to complete
a transaction nowadays.

Our users are trained in what fields are required or not.
And for those "not" fields, I set AZL=Yes / Req = No
This eliminates confusion when a record won't save
because some irrelevant field has a "blank." Note
in table above that is the only setting w/o "(not allowed)."
Go ahead, enter whatever you want (or don't enter anything),
we don't care what your devilish minds can think up to
throw a wrench in the works, just serve the customer
as quickly as you can...

We complete this data while communicating with the
customer. It sounds like your situation is different, i.e.,
your card scanner will provide only what is available
on a card. You don't want what info it does provide
to not be "enterable" just because the card did not
provide a fax number I imagine.

Some data aquisition software (possibly like your
card scanner) like to provide a ZLS in a field rather
than Null.

Your purpose for and means of gathering the data
will determine what is required/allowed. My guess
is that for the "don't-care" fields in your tblContacts,
you want

AZL = No
Req = No

(allowing Null's but not ZLS)

and use "method 2" when you import from card table
where any "blank" is changed to "N/A."

But

AZL = Yes
Req = No

will only require you to remember to use

WHERE Len(Field) > 0

instead of

WHERE Field IS NOT NULL

in filter query for "non-blanks"

You know your data best....

Gary, before I say thanks again...
What is the advantage and or disadvantage of doing this;
Required = no
Allow Zero Length = yes
what problmes my arise in the future by allowing Zero Length, there must
be a
reason the default is no?

Sorry Russ, once you've said "thank you"
you are not allowed to ask any further questions.

You have at least 2 options:

1) open up tblContacts in Design mode,
identify fields that may be a problem
and change 2 settings for those fields to

Required = no
Allow Zero Length = yes

2) identify potentially "blank" Contacts fields
you may be appending, and massage
query for those fields. For example....

In the append query Design grid,
it might look something like:

Field: City
Table: Contacts
Sort:
Append To: City
Criteria:
Or:

change it to:

Field: IIF(Len([City]) > 0, [City], "N/A")
Table: Contacts
Sort:
Append To: City
Criteria:
Or:

if Contacts.City was "blank", it will append "N/A"

if Contacts.City is not "blank," it will append value
of Contacts.City

"Russ wrote:>

Once I added some more fields, I was getting an error due to A
zero-length
string is an invalid entry, what can I do to get around this I assume
this

[quoted text clipped - 106 lines]

Last Name LastName
Company Name CompanyName

 

[Russ via AccessMonster.com]

Gary,
You do a great job explaining things, we need people like you to help us out!
Thanks!
Russ

A zero-length string ("") contains no characters
but "looks like" a NULL. You see posts all the
time here where someone is filtering for Null on
some field but not getting all expected records
from the query results because some (or all) of
the "blanks" are not really Nulls.
.

I might shy away from setting AllowZeroLength
to Yes for that reason. But spaces "look like"
a Null also. In a perfect world, I would set

AZL = No
Req = Yes

*** mostly quote from Access Help ****

The Required (Req) property determines only
whether a Null value is valid for the field.

If the AllowZeroLength (AZL) property is set to Yes,
a zero-length string (ZLS) will be a valid value
for the field regardless of the setting of the
Required property.

AZL Req User's action Value stored

No No Presses ENTER Null
Presses SPACEBAR Null
Enters ZLS (not allowed)

Yes No Presses ENTER Null
Presses SPACEBAR Null
Enters ZLS ZLS

No Yes Presses ENTER (not allowed)
Presses SPACEBAR (not allowed)
Enters ZLS (not allowed)

Yes Yes Presses ENTER (not allowed)
Presses SPACEBAR ZLS
Enters ZLS ZLS
*** unquote ***

But it's not a perfect world....
Your business and what you intend to do with
the data drives data design...

In one of our apps at work we keep track of
special orders for books. Our "business" says
we want to be able to contact a customer when
a book comes in. We don't allow a record to be
saved unless it has at least a phone number and address
for the customer.

You can also "make contact" via email address or fax
but not everyone may be able to (or want to) provide those.
I know I give out as little personal info as I can to complete
a transaction nowadays.

Our users are trained in what fields are required or not.
And for those "not" fields, I set AZL=Yes / Req = No
This eliminates confusion when a record won't save
because some irrelevant field has a "blank." Note
in table above that is the only setting w/o "(not allowed)."
Go ahead, enter whatever you want (or don't enter anything),
we don't care what your devilish minds can think up to
throw a wrench in the works, just serve the customer
as quickly as you can...

We complete this data while communicating with the
customer. It sounds like your situation is different, i.e.,
your card scanner will provide only what is available
on a card. You don't want what info it does provide
to not be "enterable" just because the card did not
provide a fax number I imagine.

Some data aquisition software (possibly like your
card scanner) like to provide a ZLS in a field rather
than Null.

Your purpose for and means of gathering the data
will determine what is required/allowed. My guess
is that for the "don't-care" fields in your tblContacts,
you want

AZL = No
Req = No

(allowing Null's but not ZLS)

and use "method 2" when you import from card table
where any "blank" is changed to "N/A."

But

AZL = Yes
Req = No

will only require you to remember to use

WHERE Len(Field) > 0

instead of

WHERE Field IS NOT NULL

in filter query for "non-blanks"

You know your data best....

Gary, before I say thanks again...
What is the advantage and or disadvantage of doing this;

[quoted text clipped - 52 lines]

 

[Gary Walter]

Not meaning to complicate things, but....

there are subtleties to all this...

The big one is don't ignore the effects of Null values
in the fields you use in the ON clause.

ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName

example:

you have a record in tblContacts

FirstName: Homer
LastName: Simpson
CompanyName: Null

if Contacts contains exact same record,
will it be added by our "no match" append
query?

Sadly, yes.

The reason is that in the test for equivalence
in the ON clause

C.[Company Name] = t.CompanyName ?
Null = Null ?

produces Null

which is treated as False (so "no match"),
so record will be added again!

Workarounds (again) are driven by your data...

if you set field properties for C.CompanyName to

Default "N/A"
AZL No
Req Yes

you "should" be confident that any CompanyName
in tblContacts will not be Null and any unknown
will probably be stored as "N/A".

Then you would need to tweak the append query
(so many ways...here's one)

the NZ function ("Null-to-zero") can convert any
Null to "N/A."

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
NZ(C.[Company Name], "N/A") As CComp,
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
Nz(C.[Company Name],"N/A") = t.CompanyName
WHERE
t.CompanyName IS NULL;

to handle all "blanks,"
i.e., Null, ZLS, or number of SPACES,
I hope you noticed John's use of
immediate IF function (IIF).

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name])

when we tack on ZLS to [Company Name]

(C.[Company Name] & "")

that changes all Null's to ZLS, plus has no effect on any
other strings.

when we then TRIM that result

TRIM(C.[Company Name] & "")

we get rid of all SPACES on left or right
of string.

Then, if we test for a length of 0

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,

we will get TRUE if string was a Null,
ZLS, or SPACE(s), so our IIF() returns "N/A".
Otherwise the IIF() returns the string.

That sure sounds like we covered *all* the bases
(for [Company Name], at least)

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name]) AS CComp,
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name]) = t.CompanyName
WHERE
t.CompanyName IS NULL;

In our original example, we would expect record
in tblContacts to be

FirstName: Homer
LastName: Simpson
CompanyName: N/A

and if we tried to append record

FirstName: Homer
LastName: Simpson
CompanyName: Null

it "shouldn't" happen.

Why is shouldn't in quotes?

Because Access can "fiddle" with where
it thinks segments of ON and WHERE
should actually end up as it parses your
query out for efficency.

And the only ("for sure") valid filter you want to
apply to an outer join query is the test for a
Null inner table field in a Jet query.

Whether Access will move

AND

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name]) = t.CompanyName

to the WHERE clause, I don't know. Nor whether
that would effect results....it is just something I always
fear in outer joins...

Typically, just to be sure, I would filter in preliminary
query(s), then use this prelim query(s) in the outer join.

Q1 ("Contacts" prelim)

SELECT
C.[First Name] As CFirstName,
C.[Last name] As CLastName,

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name]) AS CComp,
IIF(LEN(TRIM(C.Address & "")) = 0,
"N/A", C.Address) AS CAddr,
IIF(LEN(TRIM(C.City & "")) = 0,
"N/A", C.City) AS CCity,
IIF(LEN(TRIM(C.State & "")) = 0,
"N/A", C.State) AS CState
FROM
Contacts As C;

Q2 ("tblContacts" prelim)

SELECT
t.FirstName As tFirstName,
t.Lastname As tLastName,
IIF(LEN(TRIM(t.[Company Name] & "")) = 0,
"N/A", t.[Company Name]) AS tComp,
IIF(LEN(TRIM(t.Address & "")) = 0,
"N/A", t.Address) AS tAddr,
IIF(LEN(TRIM(t.City & "")) = 0,
"N/A", t.City) AS tCity,
IIF(LEN(TRIM(t.State & "")) = 0,
"N/A", t.State) AS CState
FROM
tblContacts As t;

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
Q1.CFirstName,
Q1.CLastName,
Q1.CComp,
Q1.CAddr,
Q1.CCity,
Q1.CState
FROM
Q1
LEFT JOIN
Q2
ON
Q1.CFirstName = Q2.tFirstName
AND
Q1.CLastName = Q2.tLastName
AND
Q1.CComp= Q2.tComp
WHERE
Q2.tComp IS NULL;



John's alternative to allow storing Null's
(especially if you know you will never get
a ZLS from the card scanner ....and it is
pretty tough for a user to enter a ZLS from
a form) is an equally valid option, you will
need to decide based on your data. Just
don't forget the effect of Nulls within the
ON clause......

Default
AZL No
Req No

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
Null, C.[Company Name]) AS CComp,
IIF(LEN(TRIM(C.Address & "")) = 0,
Null, C.Address) AS CAddr,
IIF(LEN(TRIM(C.City & "")) = 0,
Null, C.City) AS CCity,
IIF(LEN(TRIM(C.State & "")) = 0,
Null, C.State) AS CState
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND

(C.[Company Name] & "") = (t.CompanyName & "")
WHERE
t.CompanyName IS NULL;

If I missed a point or was in error, I hope
John will correct it.

Did that complicate things enough for you?

 

[Russ via AccessMonster.com]

Once again I thank you for the help and information!

Not meaning to complicate things, but....

there are subtleties to all this...

The big one is don't ignore the effects of Null values
in the fields you use in the ON clause.

ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
C.[Company Name] = t.CompanyName

example:

you have a record in tblContacts

FirstName: Homer
LastName: Simpson
CompanyName: Null

if Contacts contains exact same record,
will it be added by our "no match" append
query?

Sadly, yes.

The reason is that in the test for equivalence
in the ON clause

C.[Company Name] = t.CompanyName ?
Null = Null ?

produces Null

which is treated as False (so "no match"),
so record will be added again!

Workarounds (again) are driven by your data...

if you set field properties for C.CompanyName to

Default "N/A"
AZL No
Req Yes

you "should" be confident that any CompanyName
in tblContacts will not be Null and any unknown
will probably be stored as "N/A".

Then you would need to tweak the append query
(so many ways...here's one)

the NZ function ("Null-to-zero") can convert any
Null to "N/A."

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],
NZ(C.[Company Name], "N/A") As CComp,
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND
Nz(C.[Company Name],"N/A") = t.CompanyName
WHERE
t.CompanyName IS NULL;

to handle all "blanks,"
i.e., Null, ZLS, or number of SPACES,
I hope you noticed John's use of
immediate IF function (IIF).

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name])

when we tack on ZLS to [Company Name]

(C.[Company Name] & "")

that changes all Null's to ZLS, plus has no effect on any
other strings.

when we then TRIM that result

TRIM(C.[Company Name] & "")

we get rid of all SPACES on left or right
of string.

Then, if we test for a length of 0

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,

we will get TRUE if string was a Null,
ZLS, or SPACE(s), so our IIF() returns "N/A".
Otherwise the IIF() returns the string.

That sure sounds like we covered *all* the bases
(for [Company Name], at least)

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name]) AS CComp,
C.Address,
C.City,
C.State
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name]) = t.CompanyName
WHERE
t.CompanyName IS NULL;

In our original example, we would expect record
in tblContacts to be

FirstName: Homer
LastName: Simpson
CompanyName: N/A

and if we tried to append record

FirstName: Homer
LastName: Simpson
CompanyName: Null

it "shouldn't" happen.

Why is shouldn't in quotes?

Because Access can "fiddle" with where
it thinks segments of ON and WHERE
should actually end up as it parses your
query out for efficency.

And the only ("for sure") valid filter you want to
apply to an outer join query is the test for a
Null inner table field in a Jet query.

Whether Access will move

AND

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name]) = t.CompanyName

to the WHERE clause, I don't know. Nor whether
that would effect results....it is just something I always
fear in outer joins...

Typically, just to be sure, I would filter in preliminary
query(s), then use this prelim query(s) in the outer join.

Q1 ("Contacts" prelim)

SELECT
C.[First Name] As CFirstName,
C.[Last name] As CLastName,

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
"N/A", C.[Company Name]) AS CComp,
IIF(LEN(TRIM(C.Address & "")) = 0,
"N/A", C.Address) AS CAddr,
IIF(LEN(TRIM(C.City & "")) = 0,
"N/A", C.City) AS CCity,
IIF(LEN(TRIM(C.State & "")) = 0,
"N/A", C.State) AS CState
FROM
Contacts As C;

Q2 ("tblContacts" prelim)

SELECT
t.FirstName As tFirstName,
t.Lastname As tLastName,
IIF(LEN(TRIM(t.[Company Name] & "")) = 0,
"N/A", t.[Company Name]) AS tComp,
IIF(LEN(TRIM(t.Address & "")) = 0,
"N/A", t.Address) AS tAddr,
IIF(LEN(TRIM(t.City & "")) = 0,
"N/A", t.City) AS tCity,
IIF(LEN(TRIM(t.State & "")) = 0,
"N/A", t.State) AS CState
FROM
tblContacts As t;

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
Q1.CFirstName,
Q1.CLastName,
Q1.CComp,
Q1.CAddr,
Q1.CCity,
Q1.CState
FROM
Q1
LEFT JOIN
Q2
ON
Q1.CFirstName = Q2.tFirstName
AND
Q1.CLastName = Q2.tLastName
AND
Q1.CComp= Q2.tComp
WHERE
Q2.tComp IS NULL;

John's alternative to allow storing Null's
(especially if you know you will never get
a ZLS from the card scanner ....and it is
pretty tough for a user to enter a ZLS from
a form) is an equally valid option, you will
need to decide based on your data. Just
don't forget the effect of Nulls within the
ON clause......

Default
AZL No
Req No

INSERT INTO
tblContacts (FirstName, LastName, CompanyName,
Address, City, State)
SELECT
C.[First Name],
C.[Last name],

IIF(LEN(TRIM(C.[Company Name] & "")) = 0,
Null, C.[Company Name]) AS CComp,
IIF(LEN(TRIM(C.Address & "")) = 0,
Null, C.Address) AS CAddr,
IIF(LEN(TRIM(C.City & "")) = 0,
Null, C.City) AS CCity,
IIF(LEN(TRIM(C.State & "")) = 0,
Null, C.State) AS CState
FROM
Contacts As C
LEFT JOIN
tblContacts As t
ON
C.[First Name] = t.FirstName
AND
C.[Last Name] = t.LastName
AND

(C.[Company Name] & "") = (t.CompanyName & "")
WHERE
t.CompanyName IS NULL;

If I missed a point or was in error, I hope
John will correct it.

Did that complicate things enough for you?