blank fields issue

[lmcc007]

My tblCompanies table has a field called CompanyName.

CompanyName DataType = Text
Required = No
Allow Zero Length = Yes
Indexed = Yes (Duplicates OK)

Some of the records I do not have a company name for, so they are left
blank. (There are some blank company name fields because I am responding to
blind ads or ads with fax numbers and so on.)

I had made a CompanyNames lookup table with an “(N/A)†entry whenever a
company name was not available, but after posting my database on the forum it
was suggested that CompanyName should be a field in my tblCompanies database.

I still have that little thought in the back of my head I should do it
differently or have something there when it is blank.

What I need is the following:

1) Require an entry
2) No Duplicates
3) Count the blanks (because I will be printing a report of each record and
will need an accurate count)

Should I keep the database the way it is? What is the absolute best setup
for a situation like this?

Any suggestions.

Thanks!

 

[Piet Linden]

My tblCompanies table has a field called CompanyName.  

CompanyName     DataType = Text
Required = No
Allow Zero Length = Yes
Indexed = Yes (Duplicates OK)

Some of the records I do not have a company name for, so they are left
blank.  (There are some blank company name fields because I am responding to
blind ads or ads with fax numbers and so on.)

I had made a CompanyNames lookup table with an “(N/A)” entry whenevera
company name was not available, but after posting my database on the forum it
was suggested that CompanyName should be a field in my tblCompanies database.

I still have that little thought in the back of my head I should do it
differently or have something there when it is blank.  

What I need is the following:

1)  Require an entry
2)  No Duplicates
3)  Count the blanks (because I will be printing a report of each record and
will need an accurate count)

Should I keep the database the way it is?  What is the absolute best setup
for a situation like this?

Any suggestions.

Thanks!

You have a contradiction here. You want the field to be required,
without duplicates, and then you want to be able to enter N/A for
values that are null.

NEVER use that lookup misfeature. it will cause more problems than
it's worth. Believe me.

Without your related tables, it's hard to know what to suggest. If
your Company table is on the MANY side of a relationship, then the
CompanyID foreign key in that related table can be Null (unknown or
not available). If you have a unique index on Company name in the
Company table, then you can have only one "N/A" in the table.

So explain more what your data structure/relationships look like...

 

[lmcc007]

In the old db I had CompanyName in a separate table called tblCompanyNames.
tblCompanies tables included related field CompanyNameID, which was a
foreign key in this tblCompanies table. Therefore, whenever there were no
company name I would choose "N/A." But, when I had another question I need
answered and posted the database for review, several people said having
company name in another table was a mistake; they said it should be a field
in the tblCompanies and just leave the field blank. So, the new database is
set up that way--that is, CompanyID, CompanyName, AcctCode, Industry, and so
on.