Deafult Values & Relationships

[Andy]

I have created a DB with 3 tables each of the tables has the primary key in
the form of a Name my first question is if typing a name into a form from one
table can i get this to auto fill all 3 tables.

Also I have a date field that I would like to have a default value in the
form of text, I have tried but get no where fast. Any help would be great.

 

[Klatuu]

If you have 3 tables that all have the same primary key, your database design
needs rethinking. Are the tables related? What sort of data do these tables
contain?

About the text value for your date field. Can you tell me what you want it
to be and why you want to store a date as text instead of in a date/time
field?

 

[Andy]

The DB contains information on equipment held by employees the equipment
falls into diffrent catogories but would be held by the same person the only
reason I split into different tables was I reached the max size for 1 table.
The date field that I would like to show as text by defalt is in relation to
training records rather than haveing an empty box on the form or a blank
space on the report or random charactors I would like to be able to show text
saying NOT REQUIRED or NOT KNOWN.

Many thanks in advance

 

[Andy]

The DB contains information on equipment held by employees the equipment
falls into diffrent catogories but would be held by the same person the only
reason I split into different tables was I reached the max size for 1 table.
The date field that I would like to show as text by defalt is in relation to
training records rather than haveing an empty box on the form or a blank
space on the report or random charactors I would like to be able to show text
saying NOT REQUIRED or NOT KNOWN.

Many thanks in advance

 

[Andy]

The DB contains information on equipment held by employees the equipment
falls into diffrent catogories but would be held by the same person the only
reason I split into different tables was I reached the max size for 1 table.
The date field that I would like to show as text by defalt is in relation to
training records rather than haveing an empty box on the form or a blank
space on the report or random charactors I would like to be able to show text
saying NOT REQUIRED or NOT KNOWN.

Many thanks in advance

 

[Klatuu]

What do you mean by max size for a table? There is not really a size limit.
The only limits on a table are a max of 255 fields in a record and a total
record size of 4000 bytes excluding Memo and OLE Object fields when the
UnicodeCompression property of the fields is set to Yes. If you hit either
of those limits, there is something wrong with your design.

Perhaps you can describe what you are wanting to do and we can help with
some design suggestions.

 

[Andy]

Ok let me see if can explain
I am trying to build a db for keeping records on employee training and
equipment. The training is required by some and not all so rather than having
empty fields in report view I would like to have a text string. So eithier a
txt field that changes to date format dd/mmm/yyyy on input or a date field
that has a deafault value of a txt string would be great.
Also on the training and equipment there are expiration dates which are
dependant on start date so rather than filling these manualy is it possible
to change these to auto fill whilst looking up the date given and adding for
example 2 years.
As for my db I have now replanned and no longer have the same primary key
for all tables, but would like to know how to change the auto number to be an
auto number of alpha numeric string eg a-xx-000.
As for filling the table yes i reached the max number of fields due to
mulitple items requiring to date fields serial number and so on.
I belive the tables are now ok and the relationships work ok any help you
can offer on my points above would be great.
Many thanks in advance.

 

[Klatuu]

In almost all cases, if you have a table with 255 fields, your design needs
to be redone. Usually, you are using columns that should be rows in other
tables.
I would suggest finding a good book on relational database design. I
haven't read it, but I hear one by the name of "Relational Database Design
for Mere Mortals" (or something like that) referred to frequently.

As to autonumber. You can't. Autonumber fields should be used for nothing
surrogate primary keys. Their values are usefull for relating parent record
with the child records by using a field in the child record that carries the
value of the primary key of the parent record in the child key.

If you want to use some other unique value for primary keys, that is okay,
but you have to manage their uniqueness yourself.

I think if you do some study on relational database design and reevaluate
your tables, you will see what I mean.

As a test, if you will post the list of fields for your large table, I will
have a look and see if I can come up with any suggestions. If you add a
comment to the fields to describe their use, that would be helpful.