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 » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

searching for names - multiple names per record



 
 
Thread Tools Display Modes
  #1  
Old February 1st, 2007, 11:01 PM posted to microsoft.public.access.gettingstarted
zSplash
external usenet poster
 
Posts: 20
Default searching for names - multiple names per record

My database needs to track several names (first and last) for each record.
My initial database had all the names (first/last) in the main table. In an
earlier request for help searching for lastnames, someone suggested that
I put all the names in a separate Names table with a nameType comboBox. Now
that I've modified my database to do that, I see that with that design I can
only have one name per record. (To enter names, I select nameType, and then
enter first/last names for that nameType. I have no way of entering/adding
the first/lastnames for the other nameTypes. Each record has 5 or 6
first/last names to track. If they are all in one Names table, as
suggested, I can only hold one nameType in each record.

Can someone please suggest another way to solve my problem?

TIA


  #2  
Old February 1st, 2007, 11:20 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default searching for names - multiple names per record

You'd better explain your precise need, but in general, you wouldn't put
multiple names on a single record.

Typically when you have multiple names on a single record, it means you've
got field names like "Originator", "Approver", "Developer" etc. That's not a
good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table, and create
a second table linked to that first table with one row for each name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each record.
My initial database had all the names (first/last) in the main table. In
an earlier request for help searching for lastnames, someone suggested
that I put all the names in a separate Names table with a nameType
comboBox. Now that I've modified my database to do that, I see that with
that design I can only have one name per record. (To enter names, I
select nameType, and then enter first/last names for that nameType. I
have no way of entering/adding the first/lastnames for the other
nameTypes. Each record has 5 or 6 first/last names to track. If they are
all in one Names table, as suggested, I can only hold one nameType in each
record.

Can someone please suggest another way to solve my problem?

TIA




  #3  
Old February 2nd, 2007, 12:11 AM posted to microsoft.public.access.gettingstarted
zSplash
external usenet poster
 
Posts: 20
Default searching for names - multiple names per record

Thanks, Doug, for responding.
By "create a second table linked to that first table with one row for each
name" that I should have individual tables for each nameType? That is leave
the mainTable with the common information, and then create a table for
nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with
nameData for each of those nameTypes?

"Douglas J. Steele" wrote in message
...
You'd better explain your precise need, but in general, you wouldn't put
multiple names on a single record.

Typically when you have multiple names on a single record, it means you've
got field names like "Originator", "Approver", "Developer" etc. That's not
a good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table, and
create a second table linked to that first table with one row for each
name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each
record. My initial database had all the names (first/last) in the main
table. In an earlier request for help searching for lastnames,
someone suggested that I put all the names in a separate Names table with
a nameType comboBox. Now that I've modified my database to do that, I
see that with that design I can only have one name per record. (To enter
names, I select nameType, and then enter first/last names for that
nameType. I have no way of entering/adding the first/lastnames for the
other nameTypes. Each record has 5 or 6 first/last names to track. If
they are all in one Names table, as suggested, I can only hold one
nameType in each record.

Can someone please suggest another way to solve my problem?

TIA






  #4  
Old February 2nd, 2007, 01:36 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default searching for names - multiple names per record

No, I don't think you should have a separate table for each name type. Have
a single name table with an additional column of NameType.

That'll make queries like "Let me know all records that John Brown is
involved with", "Let me know those records for which Mary Smith was the
Approver" and "Let me know all records where the same person was the
Developer and the Originator" much, much simpler.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, for responding.
By "create a second table linked to that first table with one row for each
name" that I should have individual tables for each nameType? That is
leave the mainTable with the common information, and then create a table
for nameOriginator, a table for nameApprover, a table nameDeveloper, etc,
with nameData for each of those nameTypes?

"Douglas J. Steele" wrote in message
...
You'd better explain your precise need, but in general, you wouldn't put
multiple names on a single record.

Typically when you have multiple names on a single record, it means
you've got field names like "Originator", "Approver", "Developer" etc.
That's not a good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table, and
create a second table linked to that first table with one row for each
name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each
record. My initial database had all the names (first/last) in the main
table. In an earlier request for help searching for lastnames,
someone suggested that I put all the names in a separate Names table
with a nameType comboBox. Now that I've modified my database to do
that, I see that with that design I can only have one name per record.
(To enter names, I select nameType, and then enter first/last names for
that nameType. I have no way of entering/adding the first/lastnames for
the other nameTypes. Each record has 5 or 6 first/last names to track.
If they are all in one Names table, as suggested, I can only hold one
nameType in each record.

Can someone please suggest another way to solve my problem?

TIA








  #5  
Old February 2nd, 2007, 06:49 PM posted to microsoft.public.access.gettingstarted
zSplash
external usenet poster
 
Posts: 20
Default searching for names - multiple names per record

Thanks, Doug, but I just don't get it. If I have a single table, with a col
for Nametype, a col for First, and a col for Last, how can I ever have more
than one name per record?

st.

"Douglas J. Steele" wrote in message
...
No, I don't think you should have a separate table for each name type.
Have a single name table with an additional column of NameType.

That'll make queries like "Let me know all records that John Brown is
involved with", "Let me know those records for which Mary Smith was the
Approver" and "Let me know all records where the same person was the
Developer and the Originator" much, much simpler.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, for responding.
By "create a second table linked to that first table with one row for
each name" that I should have individual tables for each nameType? That
is leave the mainTable with the common information, and then create a
table for nameOriginator, a table for nameApprover, a table
nameDeveloper, etc, with nameData for each of those nameTypes?

"Douglas J. Steele" wrote in message
...
You'd better explain your precise need, but in general, you wouldn't put
multiple names on a single record.

Typically when you have multiple names on a single record, it means
you've got field names like "Originator", "Approver", "Developer" etc.
That's not a good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table, and
create a second table linked to that first table with one row for each
name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each
record. My initial database had all the names (first/last) in the main
table. In an earlier request for help searching for lastnames,
someone suggested that I put all the names in a separate Names table
with a nameType comboBox. Now that I've modified my database to do
that, I see that with that design I can only have one name per record.
(To enter names, I select nameType, and then enter first/last names for
that nameType. I have no way of entering/adding the first/lastnames
for the other nameTypes. Each record has 5 or 6 first/last names to
track. If they are all in one Names table, as suggested, I can only
hold one nameType in each record.

Can someone please suggest another way to solve my problem?

TIA










  #6  
Old February 2nd, 2007, 07:22 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default searching for names - multiple names per record

Actually, you need at least one additional field in the second table: the
link back to the first table.

Let's assume you currently have Table1:

Id Desc Originator Developer Approver
1 Project A Tom Jones Mary Brown John Doe
2 Project B Mary Brown Jill Roe Mary Smith
3 Project C John Brown John Brown Mary Smith

with Id as the Primary Key.

You'd change Table1 to:

Id Desc
1 Project A
2 Project B
3 Project C

still with Id as the Primary Key.

and Table2 would be:

Id NameType Person
1 Originator Tom Jones
1 Developer Mary Brown
1 Approver John Doe
2 Originator Mary Brown
2 Developer Jill Roe
2 Approver Mary Smith
3 Originator John Brown
3 Developer John Brown
3 Approver Mary Smith

with the combination of Id and NameType as the Primary Key. (If you can have
more than NameType for a particular item, you'd need more for the PK)

Depending on your actual requirements, you could have a Person table, so
that all you store in Table2 is the PersonId.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, but I just don't get it. If I have a single table, with a
col for Nametype, a col for First, and a col for Last, how can I ever have
more than one name per record?

st.

"Douglas J. Steele" wrote in message
...
No, I don't think you should have a separate table for each name type.
Have a single name table with an additional column of NameType.

That'll make queries like "Let me know all records that John Brown is
involved with", "Let me know those records for which Mary Smith was the
Approver" and "Let me know all records where the same person was the
Developer and the Originator" much, much simpler.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, for responding.
By "create a second table linked to that first table with one row for
each name" that I should have individual tables for each nameType? That
is leave the mainTable with the common information, and then create a
table for nameOriginator, a table for nameApprover, a table
nameDeveloper, etc, with nameData for each of those nameTypes?

"Douglas J. Steele" wrote in message
...
You'd better explain your precise need, but in general, you wouldn't
put multiple names on a single record.

Typically when you have multiple names on a single record, it means
you've got field names like "Originator", "Approver", "Developer" etc.
That's not a good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table, and
create a second table linked to that first table with one row for each
name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each
record. My initial database had all the names (first/last) in the main
table. In an earlier request for help searching for lastnames,
someone suggested that I put all the names in a separate Names table
with a nameType comboBox. Now that I've modified my database to do
that, I see that with that design I can only have one name per record.
(To enter names, I select nameType, and then enter first/last names
for that nameType. I have no way of entering/adding the
first/lastnames for the other nameTypes. Each record has 5 or 6
first/last names to track. If they are all in one Names table, as
suggested, I can only hold one nameType in each record.

Can someone please suggest another way to solve my problem?

TIA












  #7  
Old February 2nd, 2007, 10:38 PM posted to microsoft.public.access.gettingstarted
zSplash
external usenet poster
 
Posts: 47
Default searching for names - multiple names per record

Thanks, Doug. I didn't know a primary key could be a combination of several
fields -- I thought it had to be a number field? And if I use your design,
do I need a foreign key in Names table to connect (somehow) to the pk in the
Projects table?

"Douglas J. Steele" wrote in message
...
Actually, you need at least one additional field in the second table: the
link back to the first table.

Let's assume you currently have Table1:

Id Desc Originator Developer Approver
1 Project A Tom Jones Mary Brown John Doe
2 Project B Mary Brown Jill Roe Mary Smith
3 Project C John Brown John Brown Mary Smith

with Id as the Primary Key.

You'd change Table1 to:

Id Desc
1 Project A
2 Project B
3 Project C

still with Id as the Primary Key.

and Table2 would be:

Id NameType Person
1 Originator Tom Jones
1 Developer Mary Brown
1 Approver John Doe
2 Originator Mary Brown
2 Developer Jill Roe
2 Approver Mary Smith
3 Originator John Brown
3 Developer John Brown
3 Approver Mary Smith

with the combination of Id and NameType as the Primary Key. (If you can
have more than NameType for a particular item, you'd need more for the PK)

Depending on your actual requirements, you could have a Person table, so
that all you store in Table2 is the PersonId.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, but I just don't get it. If I have a single table, with a
col for Nametype, a col for First, and a col for Last, how can I ever
have more than one name per record?

st.

"Douglas J. Steele" wrote in message
...
No, I don't think you should have a separate table for each name type.
Have a single name table with an additional column of NameType.

That'll make queries like "Let me know all records that John Brown is
involved with", "Let me know those records for which Mary Smith was the
Approver" and "Let me know all records where the same person was the
Developer and the Originator" much, much simpler.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, for responding.
By "create a second table linked to that first table with one row for
each name" that I should have individual tables for each nameType?
That is leave the mainTable with the common information, and then
create a table for nameOriginator, a table for nameApprover, a table
nameDeveloper, etc, with nameData for each of those nameTypes?

"Douglas J. Steele" wrote in
message ...
You'd better explain your precise need, but in general, you wouldn't
put multiple names on a single record.

Typically when you have multiple names on a single record, it means
you've got field names like "Originator", "Approver", "Developer" etc.
That's not a good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table, and
create a second table linked to that first table with one row for each
name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each
record. My initial database had all the names (first/last) in the
main table. In an earlier request for help searching for
lastnames, someone suggested that I put all the names in a separate
Names table with a nameType comboBox. Now that I've modified my
database to do that, I see that with that design I can only have one
name per record. (To enter names, I select nameType, and then enter
first/last names for that nameType. I have no way of entering/adding
the first/lastnames for the other nameTypes. Each record has 5 or 6
first/last names to track. If they are all in one Names table, as
suggested, I can only hold one nameType in each record.

Can someone please suggest another way to solve my problem?

TIA














  #8  
Old February 3rd, 2007, 01:02 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default searching for names - multiple names per record

Realistically, a primary key is just an index, and any index can have up to
10 separate fields in it.

Which design are you asking about "do I need a foreign key in Names table"?
Are you talking about my comment at the end ("Depending on your actual
requirements, you could have a Person table, so that all you store in Table2
is the PersonId."), or are you talking about Table2 in the example?

Table2 must point to Table1, so yes, it must have a foreign key in it. If
you're using "Names table" to refer to what I called "a Person table", then
no: that table wouldn't have a foreign key in it. In essence you've got a
many-to-many relationship between Table1 and the Names table. You create a
third table that resolves the intersection of the two tables, and that
intersection table consists of foreign keys pointing back to the other 2
tables.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
Thanks, Doug. I didn't know a primary key could be a combination of
several fields -- I thought it had to be a number field? And if I use
your design, do I need a foreign key in Names table to connect (somehow)
to the pk in the Projects table?

"Douglas J. Steele" wrote in message
...
Actually, you need at least one additional field in the second table: the
link back to the first table.

Let's assume you currently have Table1:

Id Desc Originator Developer Approver
1 Project A Tom Jones Mary Brown John Doe
2 Project B Mary Brown Jill Roe Mary Smith
3 Project C John Brown John Brown Mary Smith

with Id as the Primary Key.

You'd change Table1 to:

Id Desc
1 Project A
2 Project B
3 Project C

still with Id as the Primary Key.

and Table2 would be:

Id NameType Person
1 Originator Tom Jones
1 Developer Mary Brown
1 Approver John Doe
2 Originator Mary Brown
2 Developer Jill Roe
2 Approver Mary Smith
3 Originator John Brown
3 Developer John Brown
3 Approver Mary Smith

with the combination of Id and NameType as the Primary Key. (If you can
have more than NameType for a particular item, you'd need more for the
PK)

Depending on your actual requirements, you could have a Person table, so
that all you store in Table2 is the PersonId.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, but I just don't get it. If I have a single table, with a
col for Nametype, a col for First, and a col for Last, how can I ever
have more than one name per record?

st.

"Douglas J. Steele" wrote in message
...
No, I don't think you should have a separate table for each name type.
Have a single name table with an additional column of NameType.

That'll make queries like "Let me know all records that John Brown is
involved with", "Let me know those records for which Mary Smith was the
Approver" and "Let me know all records where the same person was the
Developer and the Originator" much, much simpler.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, for responding.
By "create a second table linked to that first table with one row for
each name" that I should have individual tables for each nameType?
That is leave the mainTable with the common information, and then
create a table for nameOriginator, a table for nameApprover, a table
nameDeveloper, etc, with nameData for each of those nameTypes?

"Douglas J. Steele" wrote in
message ...
You'd better explain your precise need, but in general, you wouldn't
put multiple names on a single record.

Typically when you have multiple names on a single record, it means
you've got field names like "Originator", "Approver", "Developer"
etc. That's not a good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table, and
create a second table linked to that first table with one row for
each name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each
record. My initial database had all the names (first/last) in the
main table. In an earlier request for help searching for
lastnames, someone suggested that I put all the names in a separate
Names table with a nameType comboBox. Now that I've modified my
database to do that, I see that with that design I can only have one
name per record. (To enter names, I select nameType, and then enter
first/last names for that nameType. I have no way of
entering/adding the first/lastnames for the other nameTypes. Each
record has 5 or 6 first/last names to track. If they are all in one
Names table, as suggested, I can only hold one nameType in each
record.

Can someone please suggest another way to solve my problem?

TIA
















  #9  
Old February 5th, 2007, 07:57 AM posted to microsoft.public.access.gettingstarted
zSplash
external usenet poster
 
Posts: 13
Default searching for names - multiple names per record

Thanks so much, Doug. I have tried to re-do my database to meet your
suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need
direction on how to make a third table "that resolves the intersection of
the two tables". I just don't quite get the foreign key deal.

TIA

"Douglas J. Steele" wrote in message
...
Realistically, a primary key is just an index, and any index can have up

to
10 separate fields in it.

Which design are you asking about "do I need a foreign key in Names

table"?
Are you talking about my comment at the end ("Depending on your actual
requirements, you could have a Person table, so that all you store in

Table2
is the PersonId."), or are you talking about Table2 in the example?

Table2 must point to Table1, so yes, it must have a foreign key in it. If
you're using "Names table" to refer to what I called "a Person table",

then
no: that table wouldn't have a foreign key in it. In essence you've got a
many-to-many relationship between Table1 and the Names table. You create a
third table that resolves the intersection of the two tables, and that
intersection table consists of foreign keys pointing back to the other 2
tables.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
Thanks, Doug. I didn't know a primary key could be a combination of
several fields -- I thought it had to be a number field? And if I use
your design, do I need a foreign key in Names table to connect (somehow)
to the pk in the Projects table?

"Douglas J. Steele" wrote in message
...
Actually, you need at least one additional field in the second table:

the
link back to the first table.

Let's assume you currently have Table1:

Id Desc Originator Developer Approver
1 Project A Tom Jones Mary Brown John Doe
2 Project B Mary Brown Jill Roe Mary Smith
3 Project C John Brown John Brown Mary Smith

with Id as the Primary Key.

You'd change Table1 to:

Id Desc
1 Project A
2 Project B
3 Project C

still with Id as the Primary Key.

and Table2 would be:

Id NameType Person
1 Originator Tom Jones
1 Developer Mary Brown
1 Approver John Doe
2 Originator Mary Brown
2 Developer Jill Roe
2 Approver Mary Smith
3 Originator John Brown
3 Developer John Brown
3 Approver Mary Smith

with the combination of Id and NameType as the Primary Key. (If you can
have more than NameType for a particular item, you'd need more for the
PK)

Depending on your actual requirements, you could have a Person table,

so
that all you store in Table2 is the PersonId.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, but I just don't get it. If I have a single table, with

a
col for Nametype, a col for First, and a col for Last, how can I ever
have more than one name per record?

st.

"Douglas J. Steele" wrote in

message
...
No, I don't think you should have a separate table for each name

type.
Have a single name table with an additional column of NameType.

That'll make queries like "Let me know all records that John Brown is
involved with", "Let me know those records for which Mary Smith was

the
Approver" and "Let me know all records where the same person was the
Developer and the Originator" much, much simpler.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, for responding.
By "create a second table linked to that first table with one row

for
each name" that I should have individual tables for each nameType?
That is leave the mainTable with the common information, and then
create a table for nameOriginator, a table for nameApprover, a table
nameDeveloper, etc, with nameData for each of those nameTypes?

"Douglas J. Steele" wrote in
message ...
You'd better explain your precise need, but in general, you

wouldn't
put multiple names on a single record.

Typically when you have multiple names on a single record, it means
you've got field names like "Originator", "Approver", "Developer"
etc. That's not a good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table,

and
create a second table linked to that first table with one row for
each name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each
record. My initial database had all the names (first/last) in the
main table. In an earlier request for help searching for
lastnames, someone suggested that I put all the names in a

separate
Names table with a nameType comboBox. Now that I've modified my
database to do that, I see that with that design I can only have

one
name per record. (To enter names, I select nameType, and then

enter
first/last names for that nameType. I have no way of
entering/adding the first/lastnames for the other nameTypes. Each
record has 5 or 6 first/last names to track. If they are all in

one
Names table, as suggested, I can only hold one nameType in each
record.

Can someone please suggest another way to solve my problem?

TIA


















  #10  
Old February 5th, 2007, 12:56 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default searching for names - multiple names per record

I may have confused you.

If you've got Table1 and Table2 as I described them, that's essentially all
you need.

I'd suggested an extension of that if you had a Person table:

Id Person
1 Tom Jones
2 Mary Brown
3 John Doe
4 Jill Roe
5 Mary Smith
6 John Brown

Then, instead of Table2 being

Id NameType Person
1 Originator Tom Jones
1 Developer Mary Brown
1 Approver John Doe
2 Originator Mary Brown
2 Developer Jill Roe
2 Approver Mary Smith
3 Originator John Brown
3 Developer John Brown
3 Approver Mary Smith

it would be

Id NameType Person
1 Originator 1
1 Developer 2
1 Approver 3
2 Originator 2
2 Developer 4
2 Approver 5
3 Originator 6
3 Developer 6
3 Approver 5

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks so much, Doug. I have tried to re-do my database to meet your
suggestions. I have a Table1 and Table 2, as you've outlined. Now, I
need
direction on how to make a third table "that resolves the intersection of
the two tables". I just don't quite get the foreign key deal.

TIA

"Douglas J. Steele" wrote in message
...
Realistically, a primary key is just an index, and any index can have up

to
10 separate fields in it.

Which design are you asking about "do I need a foreign key in Names

table"?
Are you talking about my comment at the end ("Depending on your actual
requirements, you could have a Person table, so that all you store in

Table2
is the PersonId."), or are you talking about Table2 in the example?

Table2 must point to Table1, so yes, it must have a foreign key in it. If
you're using "Names table" to refer to what I called "a Person table",

then
no: that table wouldn't have a foreign key in it. In essence you've got a
many-to-many relationship between Table1 and the Names table. You create
a
third table that resolves the intersection of the two tables, and that
intersection table consists of foreign keys pointing back to the other 2
tables.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
Thanks, Doug. I didn't know a primary key could be a combination of
several fields -- I thought it had to be a number field? And if I use
your design, do I need a foreign key in Names table to connect
(somehow)
to the pk in the Projects table?

"Douglas J. Steele" wrote in
message
...
Actually, you need at least one additional field in the second table:

the
link back to the first table.

Let's assume you currently have Table1:

Id Desc Originator Developer Approver
1 Project A Tom Jones Mary Brown John Doe
2 Project B Mary Brown Jill Roe Mary Smith
3 Project C John Brown John Brown Mary Smith

with Id as the Primary Key.

You'd change Table1 to:

Id Desc
1 Project A
2 Project B
3 Project C

still with Id as the Primary Key.

and Table2 would be:

Id NameType Person
1 Originator Tom Jones
1 Developer Mary Brown
1 Approver John Doe
2 Originator Mary Brown
2 Developer Jill Roe
2 Approver Mary Smith
3 Originator John Brown
3 Developer John Brown
3 Approver Mary Smith

with the combination of Id and NameType as the Primary Key. (If you
can
have more than NameType for a particular item, you'd need more for the
PK)

Depending on your actual requirements, you could have a Person table,

so
that all you store in Table2 is the PersonId.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, but I just don't get it. If I have a single table,
with

a
col for Nametype, a col for First, and a col for Last, how can I ever
have more than one name per record?

st.

"Douglas J. Steele" wrote in

message
...
No, I don't think you should have a separate table for each name

type.
Have a single name table with an additional column of NameType.

That'll make queries like "Let me know all records that John Brown
is
involved with", "Let me know those records for which Mary Smith was

the
Approver" and "Let me know all records where the same person was the
Developer and the Originator" much, much simpler.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, for responding.
By "create a second table linked to that first table with one row

for
each name" that I should have individual tables for each nameType?
That is leave the mainTable with the common information, and then
create a table for nameOriginator, a table for nameApprover, a
table
nameDeveloper, etc, with nameData for each of those nameTypes?

"Douglas J. Steele" wrote in
message ...
You'd better explain your precise need, but in general, you

wouldn't
put multiple names on a single record.

Typically when you have multiple names on a single record, it
means
you've got field names like "Originator", "Approver", "Developer"
etc. That's not a good idea: you're hiding data in the field
names.

Instead, you should keep the common information in the one table,

and
create a second table linked to that first table with one row for
each name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for
each
record. My initial database had all the names (first/last) in the
main table. In an earlier request for help searching for
lastnames, someone suggested that I put all the names in a

separate
Names table with a nameType comboBox. Now that I've modified my
database to do that, I see that with that design I can only have

one
name per record. (To enter names, I select nameType, and then

enter
first/last names for that nameType. I have no way of
entering/adding the first/lastnames for the other nameTypes.
Each
record has 5 or 6 first/last names to track. If they are all in

one
Names table, as suggested, I can only hold one nameType in each
record.

Can someone please suggest another way to solve my problem?

TIA




















 




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 02:52 PM.


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