Hi Fred, Well this is a slowww process for me. I am so
new to access, I'm really sorry to keep bothering you.
My latest error message is:
The changes you requested to the table were not
successful because they would create duplicate values in
the index, primary key, or relationship. (Error 3022)
You tried to duplicate a value in a field that is the
underlying table's primary key or an index that does not
allow duplicates.
The message includes the following instruction: Change
the data in the field or fields that contain duplicate
data, remove the index, or redefine the index to permit
duplicate entries, and try again.
I went to the education table and tried to change the
indexed properties to allow duplicates but it won't let
me do that if it's a primary key. I removed
the "personsname" as a primary key and tried it without a
primary key. That didn't work. I'm lost. I bought two
books yesterday but can't seem to find my dilemma. Thanks
for your help
-----Original Message-----
Hi Fred, My main table does not have a "newhireid" prime
key field of number datatype. I have a unique identifier
called the "Staffid" which is assigned by accounting. It
is some degree of the persons name in all letters. When
I tried to follow your directions, I could not do a one
to many relationship. When I would choose the tables it
would automatically make it a one to one. I am using
Access 2003. I appreciate your help.
-----Original Message-----
On Tue, 17 Aug 2004 06:25:27 -0700, JoAnn wrote:
I am creating a staff background form to be completed
by
new hires. It will then be exported into Excel format
and imported into a marketing database.
There are two areas, education and registration that
have
the possibility of multiple degrees/registrations. How
do
I create a table that adds a row when there is are
multiple responses. For example:
Jones Masters Civil Engineering 1998 University
xyz
Bachelors Planning 1995 University
xyz
Can anyone help???
Create a separate table, linked to the Main table, to
hold the degree
information.
Assuming your Main table has a [NewHireID] prime key
field of Number
datatype, here is a basic version.
PersonsName Number datatype Long Integer
Degree Text (Use a combo box (see * below)
for this field)
InWhatField Text
DegreeYear Text
University Text
Save the table.
In the Relationship window create a one to many
relationship between
the Main Table's [NewHireID] and this table's
[PersonsName] fields.
Create a new form bound to this table.
Set the form's Default View property to Datasheet.
Add a sub-form control to your Main entry form.
Set the control's Source Object to this table and link
the fields to
the Main Form's [NewHireID] and this form's [PersonName]
field.
Change the field names to whatever suits you.
That should do it for you.
* Have a command button with a value list of BS, BA,
MS, PhD, etc.
and use that to enter the Degree name so that the list
is consistently
stored, otherwise some BS entries, for example, may be
entered as BS
or B.S. or B of S, etc., which will make record finding
rather iffy.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
I guess I shouldn't have assumed the Prime Key field was a Number.
[StaffID] is fine. It's Text datatype, probably in the form of
"MCDNLD" for an individual named 'McDonald'.
I'm surprised you were able to get any relationship, as [StaffID]
(Text) and [PersonsName] (Number) are 2 different datatypes, which
can't be joined.
Change the [PersonsName] field from a Number datatype to Text
datatype.
In the Relationship window, set the relationship between [StaffID] and
[PersonName]. Drag from StaffID to PersonsName. Put a check mark in
the 'Enforce referential integrity' check box.
When you add the sub-form control to the Main form, link the [StaffID]
and the [PersonsName] fields.
The rest should be OK.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
I'm confused. What is the "Education" Table?
Is that the name of this table you are creating or the name of the
original table?
You already have an exiting table with a [StaffID] field and
[StaffName] field, among others. DON'T TOUCH IT.
You are creating a SECOND table which will operate in conjunction with
you're existing one.
Let's start again.
You have your original table. As I'm not sure if that table is the
Education table or not, I'll just call it "TableA".
Now we'll create a second table.
Let's name it for convenience "TableB".
PersonsName Text Indexed YES DUPLICATES OK
Degree Text
InWhatField Text
DegreeYear Text
University Text
The whole reason for this table is to permit duplicate entries for a
person. The Person can have only one Main record ([StaffID]), but he
may have many degrees, so he must be able have many records in this
new table. The records in this new table are bound to the main table's
("TableA") record by the [StaffID] field.
In your main table (TableA), [StaffID] is the Indexed No Duplicates
field.
You can rename the fields in TableB to whatever you feel comfortable
with, as long as the datatype for the TableB [PersonsName] field
matches the datatype of the TableA [StaffID] (which is text), but set
Indexed Duplicates OK in TableB.
Save the new table and name it, perhaps "tblDegreeDetails".
You now have two tables in the database; TableA containing the
StaffID, Name, and perhaps some other fields, and TableB containing a
[PersonsName] field (which does permit duplicates and which is linked
to the [StaffID] field in Table), and the University, Degree,
DegreeYear, and InWhatField fields.
Now create the relationship as in my previous post.
Then open the main form that uses TableA as it's record source and add
a Subform control. If you use the wizard to create the control, select
TableB as the record source. It will then assist you in linking the
[StaffID] and the [PersonsName] fields.
It does work.
Have some more fun.
