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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|