searching for names - multiple names per record

[zSplash]

Thanks, Doug.

Right. Your Table2 is my 2Names.

Your Table1 is my 1Main. Its fields are many, including general info (like
ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll say to
break dates out once I get it down. I will!).

I'm also wondering about whether I need separate queries for each nameType.
Do I? Otherwise, do I make a query of individual queries (ie. a query for
Originator, etc.)?

So 2Names is what you called what I referred to as Table2?

What have you called the equivalent of Table1 (and what are its fields)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks so much, Doug.
So, my SQL for the query is:
SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First,
[2Names].Last, [2Names].DOB
FROM 2Names;

TIA

What's the SQL for your query?

If you're not familiar with seeing the SQL, open the query in Design
view, and then choose "SQL View" from the View menu. (It's far easier to
deal with SQL than to try & walk through the graphics of the query
builder!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here's yet another question: How can I return the same number of
records in
my form that I have in Table1? That is: Table1 has 1238 unique
records.
Of the queries, the one for Approver has the fewest results -- 629.
When I
create the form, based on separate queries for Developer, Originator,
and
Approver, the form only shows 629 records. What's the deal with that?
I
need to return all 1238 records in my form.

TIA

So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug. You've
been
the model of patience.

Now, my next question: In creating forms, is it better design to use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a
form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a
new
pkID,
First, Last, and DOB, in addition to your Table1 data). How do
I
now
"connect" Table1 with Table2?

TIA

in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[Douglas J. Steele]

Depending on what your aim is, yes, you might require multiple queries.

A basic starting point, given

Table1

Id Desc
1 Project A
2 Project B
3 Project C

and Table2

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

would be something like:

SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person
FROM Table1 LEFT JOIN Table2
ON Table1.Id = Table2.Id


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug.

Right. Your Table2 is my 2Names.

Your Table1 is my 1Main. Its fields are many, including general info
(like ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll
say to break dates out once I get it down. I will!).

I'm also wondering about whether I need separate queries for each
nameType. Do I? Otherwise, do I make a query of individual queries (ie. a
query for Originator, etc.)?

So 2Names is what you called what I referred to as Table2?

What have you called the equivalent of Table1 (and what are its fields)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks so much, Doug.
So, my SQL for the query is:
SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First,
[2Names].Last, [2Names].DOB
FROM 2Names;

TIA

What's the SQL for your query?

If you're not familiar with seeing the SQL, open the query in Design
view, and then choose "SQL View" from the View menu. (It's far easier
to deal with SQL than to try & walk through the graphics of the query
builder!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here's yet another question: How can I return the same number of
records in
my form that I have in Table1? That is: Table1 has 1238 unique
records.
Of the queries, the one for Approver has the fewest results -- 629.
When I
create the form, based on separate queries for Developer, Originator,
and
Approver, the form only shows 629 records. What's the deal with that?
I
need to return all 1238 records in my form.

TIA

So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug. You've
been
the model of patience.

Now, my next question: In creating forms, is it better design to use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a
form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a
new
pkID,
First, Last, and DOB, in addition to your Table1 data). How
do I
now
"connect" Table1 with Table2?

TIA

in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[zSplash]

Okay. I really appreciate the help, Doug. (I was called away from this
project, and am only now getting back to it.)

My aim is to have a single form, with labels for nameType, then textboxes
for nameTypeFirst, nameTypeLast, nameTypeTitle, nameTypeAssistant. If I
have to create multiple queries to get this data on the form (which it seems
like I'll have to do), I'm going to have 4 queries for every name type. Is
there a more efficient way to do it rather than creating 4x4 queries? As it
is, my queries are overwhelming -- is that normal?

TIA

Depending on what your aim is, yes, you might require multiple queries.

A basic starting point, given

Table1

Id Desc
1 Project A
2 Project B
3 Project C

and Table2

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

would be something like:

SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person
FROM Table1 LEFT JOIN Table2
ON Table1.Id = Table2.Id


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug.

Right. Your Table2 is my 2Names.

Your Table1 is my 1Main. Its fields are many, including general info
(like ProjectA, ProjectB, ProjectC, dates, and other data (I expect
you'll say to break dates out once I get it down. I will!).

I'm also wondering about whether I need separate queries for each
nameType. Do I? Otherwise, do I make a query of individual queries (ie.
a query for Originator, etc.)?

So 2Names is what you called what I referred to as Table2?

What have you called the equivalent of Table1 (and what are its fields)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks so much, Doug.
So, my SQL for the query is:
SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First,
[2Names].Last, [2Names].DOB
FROM 2Names;

TIA

message What's the SQL for your query?

If you're not familiar with seeing the SQL, open the query in Design
view, and then choose "SQL View" from the View menu. (It's far easier
to deal with SQL than to try & walk through the graphics of the query
builder!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here's yet another question: How can I return the same number of
records in
my form that I have in Table1? That is: Table1 has 1238 unique
records.
Of the queries, the one for Approver has the fewest results -- 629.
When I
create the form, based on separate queries for Developer, Originator,
and
Approver, the form only shows 629 records. What's the deal with
that? I
need to return all 1238 records in my form.

TIA

So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug.
You've been
the model of patience.

Now, my next question: In creating forms, is it better design to
use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the
text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a
form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a
new
pkID,
First, Last, and DOB, in addition to your Table1 data). How
do I
now
"connect" Table1 with Table2?

TIA

in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[Douglas J. Steele]

I'm not sure I understand why you need multiple queries if you're having a
single form.

Seems to me a single query that returns everything is what you want. You can
then apply filters to the form to only show specific records if need be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay. I really appreciate the help, Doug. (I was called away from this
project, and am only now getting back to it.)

My aim is to have a single form, with labels for nameType, then textboxes
for nameTypeFirst, nameTypeLast, nameTypeTitle, nameTypeAssistant. If I
have to create multiple queries to get this data on the form (which it
seems like I'll have to do), I'm going to have 4 queries for every name
type. Is there a more efficient way to do it rather than creating 4x4
queries? As it is, my queries are overwhelming -- is that normal?

TIA

Depending on what your aim is, yes, you might require multiple queries.

A basic starting point, given

Table1

Id Desc
1 Project A
2 Project B
3 Project C

and Table2

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

would be something like:

SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person
FROM Table1 LEFT JOIN Table2
ON Table1.Id = Table2.Id


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug.

Right. Your Table2 is my 2Names.

Your Table1 is my 1Main. Its fields are many, including general info
(like ProjectA, ProjectB, ProjectC, dates, and other data (I expect
you'll say to break dates out once I get it down. I will!).

I'm also wondering about whether I need separate queries for each
nameType. Do I? Otherwise, do I make a query of individual queries (ie.
a query for Originator, etc.)?

So 2Names is what you called what I referred to as Table2?

What have you called the equivalent of Table1 (and what are its
fields)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks so much, Doug.
So, my SQL for the query is:
SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First,
[2Names].Last, [2Names].DOB
FROM 2Names;

TIA

message What's the SQL for your query?

If you're not familiar with seeing the SQL, open the query in Design
view, and then choose "SQL View" from the View menu. (It's far easier
to deal with SQL than to try & walk through the graphics of the query
builder!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here's yet another question: How can I return the same number of
records in
my form that I have in Table1? That is: Table1 has 1238 unique
records.
Of the queries, the one for Approver has the fewest results -- 629.
When I
create the form, based on separate queries for Developer,
Originator, and
Approver, the form only shows 629 records. What's the deal with
that? I
need to return all 1238 records in my form.

TIA

So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug.
You've been
the model of patience.

Now, my next question: In creating forms, is it better design to
use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the
text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created
a form
with
textboxes. So, for example, to pull up the data about the
first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

in
message
You join them in a query, linking the ID field in Table1 to
the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks for the patience for dealing with such a dense
person, Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a
new
pkID,
First, Last, and DOB, in addition to your Table1 data). How
do I
now
"connect" Table1 with Table2?

TIA

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com>
wrote in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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"
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[zSplash]

In my form, then, I want to return, in one place, the originator's first and
last names. I have put the textbox for the query's firstname. How do I
make it so that it pulls the firstname of the originator that fits with the
record, rather than the developer's firstname? I unsuccessfully tried
putting
=first where nametype="originator"
=first if nametype="originator"
as the default value, as well as the control source in a generic textbox.

TIA

I'm not sure I understand why you need multiple queries if you're having a
single form.

Seems to me a single query that returns everything is what you want. You
can then apply filters to the form to only show specific records if need
be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay. I really appreciate the help, Doug. (I was called away from this
project, and am only now getting back to it.)

My aim is to have a single form, with labels for nameType, then textboxes
for nameTypeFirst, nameTypeLast, nameTypeTitle, nameTypeAssistant. If I
have to create multiple queries to get this data on the form (which it
seems like I'll have to do), I'm going to have 4 queries for every name
type. Is there a more efficient way to do it rather than creating 4x4
queries? As it is, my queries are overwhelming -- is that normal?

TIA

Depending on what your aim is, yes, you might require multiple queries.

A basic starting point, given

Table1

Id Desc
1 Project A
2 Project B
3 Project C

and Table2

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

would be something like:

SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person
FROM Table1 LEFT JOIN Table2
ON Table1.Id = Table2.Id


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug.

Right. Your Table2 is my 2Names.

Your Table1 is my 1Main. Its fields are many, including general info
(like ProjectA, ProjectB, ProjectC, dates, and other data (I expect
you'll say to break dates out once I get it down. I will!).

I'm also wondering about whether I need separate queries for each
nameType. Do I? Otherwise, do I make a query of individual queries
(ie. a query for Originator, etc.)?

message So 2Names is what you called what I referred to as Table2?

What have you called the equivalent of Table1 (and what are its
fields)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks so much, Doug.
So, my SQL for the query is:
SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First,
[2Names].Last, [2Names].DOB
FROM 2Names;

TIA

message What's the SQL for your query?

If you're not familiar with seeing the SQL, open the query in Design
view, and then choose "SQL View" from the View menu. (It's far
easier to deal with SQL than to try & walk through the graphics of
the query builder!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here's yet another question: How can I return the same number of
records in
my form that I have in Table1? That is: Table1 has 1238 unique
records.
Of the queries, the one for Approver has the fewest results -- 629.
When I
create the form, based on separate queries for Developer,
Originator, and
Approver, the form only shows 629 records. What's the deal with
that? I
need to return all 1238 records in my form.

TIA

So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug.
You've been
the model of patience.

Now, my next question: In creating forms, is it better design to
use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the
text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Okay. I've done that (and named the qTest). And I've created
a form
with
textboxes. So, for example, to pull up the data about the
first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

in
message
You join them in a query, linking the ID field in Table1 to
the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks for the patience for dealing with such a dense
person, Doug.

So, I have Table1 and Table2 (excepting that my Table2 has
a new
pkID,
First, Last, and DOB, in addition to your Table1 data).
How do I
now
"connect" Table1 with Table2?

TIA

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com>
wrote in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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"
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash"
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 re:
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

 

[zSplash]

If I put
= First if nametype="originator"
for Control Source, I get "invalid syntax".

How does one filter a bound textbox, I guess I need to know?

TIA

In my form, then, I want to return, in one place, the originator's first
and last names. I have put the textbox for the query's firstname. How do
I make it so that it pulls the firstname of the originator that fits with
the record, rather than the developer's firstname? I unsuccessfully tried
putting
=first where nametype="originator"
=first if nametype="originator"
as the default value, as well as the control source in a generic textbox.

TIA

I'm not sure I understand why you need multiple queries if you're having
a single form.

Seems to me a single query that returns everything is what you want. You
can then apply filters to the form to only show specific records if need
be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay. I really appreciate the help, Doug. (I was called away from this
project, and am only now getting back to it.)

My aim is to have a single form, with labels for nameType, then
textboxes for nameTypeFirst, nameTypeLast, nameTypeTitle,
nameTypeAssistant. If I have to create multiple queries to get this
data on the form (which it seems like I'll have to do), I'm going to
have 4 queries for every name type. Is there a more efficient way to do
it rather than creating 4x4 queries? As it is, my queries are
overwhelming -- is that normal?

TIA

Depending on what your aim is, yes, you might require multiple queries.

A basic starting point, given

Table1

Id Desc
1 Project A
2 Project B
3 Project C

and Table2

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

would be something like:

SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person
FROM Table1 LEFT JOIN Table2
ON Table1.Id = Table2.Id


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug.

Right. Your Table2 is my 2Names.

Your Table1 is my 1Main. Its fields are many, including general info
(like ProjectA, ProjectB, ProjectC, dates, and other data (I expect
you'll say to break dates out once I get it down. I will!).

I'm also wondering about whether I need separate queries for each
nameType. Do I? Otherwise, do I make a query of individual queries
(ie. a query for Originator, etc.)?

message So 2Names is what you called what I referred to as Table2?

What have you called the equivalent of Table1 (and what are its
fields)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks so much, Doug.
So, my SQL for the query is:
SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First,
[2Names].Last, [2Names].DOB
FROM 2Names;

TIA

message What's the SQL for your query?

If you're not familiar with seeing the SQL, open the query in
Design view, and then choose "SQL View" from the View menu. (It's
far easier to deal with SQL than to try & walk through the graphics
of the query builder!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here's yet another question: How can I return the same number of
records in
my form that I have in Table1? That is: Table1 has 1238 unique
records.
Of the queries, the one for Approver has the fewest results --
629. When I
create the form, based on separate queries for Developer,
Originator, and
Approver, the form only shows 629 records. What's the deal with
that? I
need to return all 1238 records in my form.

TIA

So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug.
You've been
the model of patience.

Now, my next question: In creating forms, is it better design to
use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the
text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Okay. I've done that (and named the qTest). And I've
created a form
with
textboxes. So, for example, to pull up the data about the
first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

in
message
You join them in a query, linking the ID field in Table1 to
the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks for the patience for dealing with such a dense
person, Doug.

So, I have Table1 and Table2 (excepting that my Table2 has
a new
pkID,
First, Last, and DOB, in addition to your Table1 data).
How do I
now
"connect" Table1 with Table2?

TIA

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com>
wrote in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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"
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash"
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
re:
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

 

[Douglas J. Steele]

You don't apply filters to controls (like textboxes). You apply filters to
the underlying recordset of the form.

To have your form only show the names for those people whose nametype is
originator, you need code like:

Me.Filter = "nametype = 'originator'"
Me.FilterOn = True

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In my form, then, I want to return, in one place, the originator's first
and last names. I have put the textbox for the query's firstname. How do
I make it so that it pulls the firstname of the originator that fits with
the record, rather than the developer's firstname? I unsuccessfully tried
putting
=first where nametype="originator"
=first if nametype="originator"
as the default value, as well as the control source in a generic textbox.

TIA

I'm not sure I understand why you need multiple queries if you're having
a single form.

Seems to me a single query that returns everything is what you want. You
can then apply filters to the form to only show specific records if need
be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay. I really appreciate the help, Doug. (I was called away from this
project, and am only now getting back to it.)

My aim is to have a single form, with labels for nameType, then
textboxes for nameTypeFirst, nameTypeLast, nameTypeTitle,
nameTypeAssistant. If I have to create multiple queries to get this
data on the form (which it seems like I'll have to do), I'm going to
have 4 queries for every name type. Is there a more efficient way to do
it rather than creating 4x4 queries? As it is, my queries are
overwhelming -- is that normal?

TIA

Depending on what your aim is, yes, you might require multiple queries.

A basic starting point, given

Table1

Id Desc
1 Project A
2 Project B
3 Project C

and Table2

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

would be something like:

SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person
FROM Table1 LEFT JOIN Table2
ON Table1.Id = Table2.Id


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug.

Right. Your Table2 is my 2Names.

Your Table1 is my 1Main. Its fields are many, including general info
(like ProjectA, ProjectB, ProjectC, dates, and other data (I expect
you'll say to break dates out once I get it down. I will!).

I'm also wondering about whether I need separate queries for each
nameType. Do I? Otherwise, do I make a query of individual queries
(ie. a query for Originator, etc.)?

message So 2Names is what you called what I referred to as Table2?

What have you called the equivalent of Table1 (and what are its
fields)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks so much, Doug.
So, my SQL for the query is:
SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First,
[2Names].Last, [2Names].DOB
FROM 2Names;

TIA

message What's the SQL for your query?

If you're not familiar with seeing the SQL, open the query in
Design view, and then choose "SQL View" from the View menu. (It's
far easier to deal with SQL than to try & walk through the graphics
of the query builder!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here's yet another question: How can I return the same number of
records in
my form that I have in Table1? That is: Table1 has 1238 unique
records.
Of the queries, the one for Approver has the fewest results --
629. When I
create the form, based on separate queries for Developer,
Originator, and
Approver, the form only shows 629 records. What's the deal with
that? I
need to return all 1238 records in my form.

TIA

So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug.
You've been
the model of patience.

Now, my next question: In creating forms, is it better design to
use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the
text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Okay. I've done that (and named the qTest). And I've
created a form
with
textboxes. So, for example, to pull up the data about the
first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

in
message
You join them in a query, linking the ID field in Table1 to
the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks for the patience for dealing with such a dense
person, Doug.

So, I have Table1 and Table2 (excepting that my Table2 has
a new
pkID,
First, Last, and DOB, in addition to your Table1 data).
How do I
now
"connect" Table1 with Table2?

TIA

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com>
wrote in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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"
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash"
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
re:
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

 

[zSplash]

Okay, Doug. Thanks for that, which makes sense, but where do I put that
code?

TIA

You don't apply filters to controls (like textboxes). You apply

filters to

the underlying recordset of the form.

To have your form only show the names for those people whose nametype

originator, you need code like:

Me.Filter = "nametype = 'originator'"

Me.FilterOn = True


Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I always use a query, even if the query doesn't do anything more than
return the table as-is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug. You've
been
the model of patience.

Now, my next question: In creating forms, is it better design to use
queries or tables? In this case, is it better to use Table1 or a query
based on Table1 when I re-design my form?

TIA

You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a form with
textboxes. So, for example, to pull up the data about the first record's
originator's lastname, I've put the following in the Control Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the patience for dealing with such a dense person, Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a new pkID,
First, Last, and DOB, in addition to your Table1 data). How do I
now
"connect" Table1 with Table2?

TIA

message
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

(no e-mails, please!)


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

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

(no private e-mails, please)


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?

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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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?

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

(no private e-mails, please)


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 re:
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

 

[Douglas J. Steele]

At the risk of seeming trite, wherever you need it to be.

Sorry, I don't know how you're building your application. Do you want a
button that they push to limit the data, do you want to let them select from
a combo box or option group, or what?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay, Doug. Thanks for that, which makes sense, but where do I put that
code?

TIA

You don't apply filters to controls (like textboxes). You apply

filters to

the underlying recordset of the form.

To have your form only show the names for those people whose nametype

originator, you need code like:

Me.Filter = "nametype = 'originator'"

Me.FilterOn = True


Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I always use a query, even if the query doesn't do anything more than
return the table as-is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug. You've
been
the model of patience.

Now, my next question: In creating forms, is it better design to use
queries or tables? In this case, is it better to use Table1 or a query
based on Table1 when I re-design my form?

TIA

You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a new
pkID,
First, Last, and DOB, in addition to your Table1 data). How do I
now
"connect" Table1 with Table2?

TIA

message
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

(no e-mails, please!)


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

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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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 re:
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

 

[zSplash]

Trite, after all this help you've given me? LOL. Evidently, I don't yet
have a grip on how I'm building my application, either!

Anyway, in my form I have a label that says "Originator:". Then, I have a
bound textbox for Firstname, then a bound textbox for Lastname. I want to
put your code (Me.Filter = "nametype = 'originator'") somewhere so that each
of these textboxes will show the firstname and last name for the originator.

TIA

At the risk of seeming trite, wherever you need it to be.

Sorry, I don't know how you're building your application. Do you want a
button that they push to limit the data, do you want to let them select
from a combo box or option group, or what?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay, Doug. Thanks for that, which makes sense, but where do I put that
code?

TIA

You don't apply filters to controls (like textboxes). You apply

filters to

the underlying recordset of the form.

To have your form only show the names for those people whose nametype

originator, you need code like:

Me.Filter = "nametype = 'originator'"

Me.FilterOn = True


Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I always use a query, even if the query doesn't do anything more than
return the table as-is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug. You've
been
the model of patience.

Now, my next question: In creating forms, is it better design to use
queries or tables? In this case, is it better to use Table1 or a query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a
form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a new
pkID,
First, Last, and DOB, in addition to your Table1 data). How do I
now
"connect" Table1 with Table2?

TIA

message
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

(no e-mails, please!)


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

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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[Douglas J. Steele]

Describe your form a bit more. Are you trying to show only the originator
for a particular project, are you trying to show all originators, regardless
of project, or something else? How do you invoke the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Trite, after all this help you've given me? LOL. Evidently, I don't yet
have a grip on how I'm building my application, either!

Anyway, in my form I have a label that says "Originator:". Then, I have a
bound textbox for Firstname, then a bound textbox for Lastname. I want to
put your code (Me.Filter = "nametype = 'originator'") somewhere so that
each of these textboxes will show the firstname and last name for the
originator.

TIA

At the risk of seeming trite, wherever you need it to be.

Sorry, I don't know how you're building your application. Do you want a
button that they push to limit the data, do you want to let them select
from a combo box or option group, or what?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay, Doug. Thanks for that, which makes sense, but where do I put that
code?

TIA


You don't apply filters to controls (like textboxes). You apply

filters to

the underlying recordset of the form.



To have your form only show the names for those people whose nametype

is

originator, you need code like:



Me.Filter = "nametype = 'originator'"

Me.FilterOn = True



--

Doug Steele, Microsoft Access MVP



(no e-mails, please!)





I always use a query, even if the query doesn't do anything more than
return the table as-is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug. You've
been
the model of patience.

Now, my next question: In creating forms, is it better design to use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a
form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a new
pkID,
First, Last, and DOB, in addition to your Table1 data). How do
I now
"connect" Table1 with Table2?

TIA

message
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

(no e-mails, please!)


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

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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[zSplash]

Hi, Doug. Thanks again. I'm trying to show all projects, with originators,
developers, etc., for each project. (There are 1500 projects.) I have a
search form that opens on startup. They then search for a name, and can
open the mainForm from the results list. In the mainForm, they can
see/enter the originator, developer, etc, and other data about the project.

TIA

Describe your form a bit more. Are you trying to show only the originator
for a particular project, are you trying to show all originators,
regardless of project, or something else? How do you invoke the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Trite, after all this help you've given me? LOL. Evidently, I don't yet
have a grip on how I'm building my application, either!

Anyway, in my form I have a label that says "Originator:". Then, I have
a bound textbox for Firstname, then a bound textbox for Lastname. I want
to put your code (Me.Filter = "nametype = 'originator'") somewhere so
that each of these textboxes will show the firstname and last name for
the originator.

TIA

At the risk of seeming trite, wherever you need it to be.

Sorry, I don't know how you're building your application. Do you want a
button that they push to limit the data, do you want to let them select
from a combo box or option group, or what?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay, Doug. Thanks for that, which makes sense, but where do I put
that code?

TIA

message
You don't apply filters to controls (like textboxes). You apply

filters to

the underlying recordset of the form.



To have your form only show the names for those people whose nametype

is

originator, you need code like:



Me.Filter = "nametype = 'originator'"

Me.FilterOn = True



--

Doug Steele, Microsoft Access MVP



(no e-mails, please!)





message I always use a query, even if the query doesn't do anything more than
return the table as-is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug. You've
been
the model of patience.

Now, my next question: In creating forms, is it better design to use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a
form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a
new
pkID,
First, Last, and DOB, in addition to your Table1 data). How do
I now
"connect" Table1 with Table2?

TIA

in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[Douglas J. Steele]

Base the form on the query that returns everything (i.e.: all names, all
name types, etc.)

Once you know what name they're looking for, have a button they click on.

Assuming the button is on the same form as where you're displaying the
results, in the Click event for that button, set

Me.Filter = "Person = '" & Me.txtSearch & "'"
Me.FilterOn = True

or, if you want to let them only enter a part of the name,

Me.Filter = "Person LIKE '*" & Me.txtSearch & "*'"
Me.FilterOn = True

Alternatively, you can have them key in the name on a special Search form,
and then have the button open the other form for you, using something like

DoCmd.OpenForm "MyForm", acNormal, , "Person LIKE '*" & Me.txtSearch & "*'"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, Doug. Thanks again. I'm trying to show all projects, with
originators, developers, etc., for each project. (There are 1500
projects.) I have a search form that opens on startup. They then search
for a name, and can open the mainForm from the results list. In the
mainForm, they can see/enter the originator, developer, etc, and other
data about the project.

TIA

Describe your form a bit more. Are you trying to show only the originator
for a particular project, are you trying to show all originators,
regardless of project, or something else? How do you invoke the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Trite, after all this help you've given me? LOL. Evidently, I don't
yet have a grip on how I'm building my application, either!

Anyway, in my form I have a label that says "Originator:". Then, I have
a bound textbox for Firstname, then a bound textbox for Lastname. I
want to put your code (Me.Filter = "nametype = 'originator'") somewhere
so that each of these textboxes will show the firstname and last name
for the originator.

TIA

At the risk of seeming trite, wherever you need it to be.

Sorry, I don't know how you're building your application. Do you want a
button that they push to limit the data, do you want to let them select
from a combo box or option group, or what?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay, Doug. Thanks for that, which makes sense, but where do I put
that code?

TIA

message
You don't apply filters to controls (like textboxes). You apply

filters to

the underlying recordset of the form.



To have your form only show the names for those people whose nametype

is

originator, you need code like:



Me.Filter = "nametype = 'originator'"

Me.FilterOn = True



--

Doug Steele, Microsoft Access MVP



(no e-mails, please!)





message I always use a query, even if the query doesn't do anything more than
return the table as-is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug.
You've been
the model of patience.

Now, my next question: In creating forms, is it better design to
use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the text
box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a
form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a
new
pkID,
First, Last, and DOB, in addition to your Table1 data). How
do I now
"connect" Table1 with Table2?

TIA

in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


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"
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[zSplash]

Thanks, Doug. I see what you're saying. But, I guess my "form" is really
more of a "report". Say in my report I always want to see any Originator,
Developer, Instigator, Procrastinator data for the project. So, I have 4
"lines" in my report:
labelOriginator | OriginatorFirst | OriginatorLast | Originator Assistant
| Originator DOB

How would I make it so that the Originator data is ever-present in the
report, and the Developer data is ever-present, and the Instigator data,
usw? I don't want anything to be "chosen", but I want to be able to always
"see" the data. How do I code for that, and where do I put the code?

TIA

Base the form on the query that returns everything (i.e.: all names, all
name types, etc.)

Once you know what name they're looking for, have a button they click on.

Assuming the button is on the same form as where you're displaying the
results, in the Click event for that button, set

Me.Filter = "Person = '" & Me.txtSearch & "'"
Me.FilterOn = True

or, if you want to let them only enter a part of the name,

Me.Filter = "Person LIKE '*" & Me.txtSearch & "*'"
Me.FilterOn = True

Alternatively, you can have them key in the name on a special Search form,
and then have the button open the other form for you, using something like

DoCmd.OpenForm "MyForm", acNormal, , "Person LIKE '*" & Me.txtSearch &
"*'"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, Doug. Thanks again. I'm trying to show all projects, with
originators, developers, etc., for each project. (There are 1500
projects.) I have a search form that opens on startup. They then search
for a name, and can open the mainForm from the results list. In the
mainForm, they can see/enter the originator, developer, etc, and other
data about the project.

TIA

Describe your form a bit more. Are you trying to show only the
originator for a particular project, are you trying to show all
originators, regardless of project, or something else? How do you invoke
the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Trite, after all this help you've given me? LOL. Evidently, I don't
yet have a grip on how I'm building my application, either!

Anyway, in my form I have a label that says "Originator:". Then, I
have a bound textbox for Firstname, then a bound textbox for Lastname.
I want to put your code (Me.Filter = "nametype = 'originator'")
somewhere so that each of these textboxes will show the firstname and
last name for the originator.

TIA

message At the risk of seeming trite, wherever you need it to be.

Sorry, I don't know how you're building your application. Do you want
a button that they push to limit the data, do you want to let them
select from a combo box or option group, or what?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay, Doug. Thanks for that, which makes sense, but where do I put
that code?

TIA

message
You don't apply filters to controls (like textboxes). You apply

filters to

the underlying recordset of the form.



To have your form only show the names for those people whose
nametype

is

originator, you need code like:



Me.Filter = "nametype = 'originator'"

Me.FilterOn = True



--

Doug Steele, Microsoft Access MVP



(no e-mails, please!)





message I always use a query, even if the query doesn't do anything more than
return the table as-is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So, it's slowly becoming clear! The light at the end of a
l-o-o-n-n-n-g
tunnel (probably excruciatingly long for you). Thanks, Doug.
You've been
the model of patience.

Now, my next question: In creating forms, is it better design to
use
queries or tables? In this case, is it better to use Table1 or a
query
based on Table1 when I re-design my form?

TIA

message
You don't set the ControlSource property for text boxes to SQL
statements.

You set the RecordSource of the form to the query, and set the
text box's
ControlSource to the name of a field in that query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay. I've done that (and named the qTest). And I've created a
form
with
textboxes. So, for example, to pull up the data about the first
record's
originator's lastname, I've put the following in the Control
Source
property
of the textbox:
SELECT [qTest]![Last] from [qTest] where
[qTest]![nameType]="Originator"
But, when I open the form, that textbox shows an error:
Name#?
What's wrong?

TIA

message
You join them in a query, linking the ID field in Table1 to the
corresponding ID field in Table2.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks for the patience for dealing with such a dense person,
Doug.

So, I have Table1 and Table2 (excepting that my Table2 has a
new
pkID,
First, Last, and DOB, in addition to your Table1 data). How
do I now
"connect" Table1 with Table2?

TIA

in
message
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

(no e-mails, please!)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


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" <NOSPAM_djsteele@NOSPAM_canada.com>
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
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

(no e-mails, please!)


"zSplash" <[email protected]>
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"
<NOSPAM_djsteele@NOSPAM_canada.com>
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

(no private e-mails, please)


"zSplash" <[email protected]>
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 re:
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

 

[Douglas J. Steele]

I'm sorry, I'm really having problems envisioning what you're trying to do,
and given how long this thread has gone on, it's unlikely anyone else is
reading it.

It might be worth starting a new thread, explaining what your table
structure is and what it is you're trying to accomplish with your form.

 

[zSplash]

Okay. Thanks for all your help, Doug.

I'm sorry, I'm really having problems envisioning what you're trying to
do, and given how long this thread has gone on, it's unlikely anyone else
is reading it.

It might be worth starting a new thread, explaining what your table
structure is and what it is you're trying to accomplish with your form.