A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

blank fields issue



 
 
Thread Tools Display Modes
  #1  
Old September 9th, 2009, 02:49 AM posted to microsoft.public.access.tablesdbdesign
lmcc007
external usenet poster
 
Posts: 63
Default blank fields issue

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!

  #2  
Old September 9th, 2009, 03:55 AM posted to microsoft.public.access.tablesdbdesign
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default blank fields issue

On Sep 8, 8:49*pm, lmcc007 wrote:
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!


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...
  #3  
Old September 9th, 2009, 04:39 AM posted to microsoft.public.access.tablesdbdesign
lmcc007
external usenet poster
 
Posts: 63
Default blank fields issue

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.


"Piet Linden" wrote:

On Sep 8, 8:49 pm, lmcc007 wrote:
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!


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...

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:34 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.