Creating new record in a table

[Guest]

Table Household fields are Household Of,Last,Sufix(ex."Sr."), First,Middle
when I enter the Last,Sufix,First,Middle I then want Household Of to be
automaticly filled with the "Last Sufix, First Middle" when I exit the table
or when I go to add another new record. If this is posible please give me
the correct expression. Household Of , is my Primary Key as it will be
unique with no duplicates allowed.

 

[Douglas J. Steele]

Are you saying you want to store the data redundantly, once as separate
fields and once combined? That's definitely not recommended.

Instead, keep the fields separate, and create a query with a computed field
that concatenates them together for display purposes. Use the query wherever
you would otherwise have used the table.

 

[Guest]

Yes that is what I want to do. Though this may not be recomended, It does
stop duplicate records from being added to the table andt this field
Household Of becomes my foreign key in many of my other tables. So if there
is the correct expression and syntax for doing this. What would it be.

 

[John W. Vinson]

Yes that is what I want to do. Though this may not be recomended, It does
stop duplicate records from being added to the table andt this field
Household Of becomes my foreign key in many of my other tables. So if there
is the correct expression and syntax for doing this. What would it be.

Again... YOU REALLY DON'T WANT TO DO THIS.

Names are *NOT* unique. I once worked with Dr. Lawrence David Wise, Ph.D. and
his colleague, Dr. Lawrence David Wise, Ph.D.; I know three people name Fred
Brown.

It will *not* prevent invalid duplicates from being added to the database, if
one gets entered with an abbreviated or missing middle name.

It *will* prevent valid records from being added to the database (unless the
data entry person gets around the restriction as above) if you happen to have
two records for people who happen to share a name.

You can *check* (not prevent) the addition of duplicates by using the Form's
BeforeUpdate event to look up names in the form's recordsource query and
alerting the data entry person of the duplication.


John W. Vinson [MVP]

 

[Joseph Meehan]

Table Household fields are Household Of,Last,Sufix(ex."Sr."),
First,Middle when I enter the Last,Sufix,First,Middle I then want
Household Of to be automaticly filled with the "Last Sufix, First
Middle" when I exit the table or when I go to add another new
record. If this is posible please give me the correct expression.
Household Of , is my Primary Key as it will be unique with no
duplicates allowed.

No you don't. You just think you do. You don't store redundant data
in a properly designed database. You really don't want to store that
information do you? You want to be able to see it and display it and use it
in reports. So what you do it to use the information already in the table
to display it in a query, report or form.

One reason you don't want to store it is some time down the road you are
going to find you had someone's middle name misspelled. Then you will
correct it, but forget to correct the second field. It will happen.

Remember also that tables are not very good tools to add, display or
edit data. There are lots of reasons. Use forms queries and reports.

 

[Joseph Meehan]

Yes that is what I want to do. Though this may not be recomended, It
does stop duplicate records from being added to the table andt this
field Household Of becomes my foreign key in many of my other tables.
So if there is the correct expression and syntax for doing this.
What would it be.

From the Access help file:

In situations where you can't guarantee the uniqueness of any single field,
you may be able to designate two or more fields as the primary key. The most
common situation where this arises is in the table used to relate two other
tables in a many-to-many relationship. For example, an Order Details table
can relate the Orders and Products tables. Its primary key consists of two
fields: OrderID and ProductID. The Order Details table can list many
products and many orders, but each product can only be listed once per
order, so combining the OrderID and ProductID fields produces an appropriate
primary key.

Each product can be listed only once per order.
Another example would be an inventory database that uses a field part number
of two or more fields (part and subpart).
If you are in doubt about whether you can select an appropriate combination
of fields for a multiple-field primary key, you should probably add an
AutoNumber field and designate it as the primary key instead. For example,
combining FirstName and LastName fields to produce a primary key is not a
good choice, since you may eventually encounter duplication in the
combination of these two fields.
In a multiple-field primary key, field order may be important to you. The
fields in a multiple-field primary key are sorted according to their order
in table Design view. You can change the order of the primary key fields in
the Indexes window.

 

[Tom Wimpernark]

im not sure I agree with this.

in SQL Server you can use calculated fields-- and they're readily available
whenever you want them-- just query the field from the table

sounds to me like you need to go back to architecture school and learn how
to choose a decent database

 

[George Hepworth]

Aaron Kem.pf is posting under a new alias.

im not sure I agree with this.

in SQL Server you can use calculated fields-- and they're readily
available whenever you want them-- just query the field from the table

sounds to me like you need to go back to architecture school and learn how
to choose a decent database

 

[Joseph Meehan]

Aaron Kem.pf is posting under a new alias.

New name same old line. It was very obvious.