search results - several fields for LastName

  • Thread starter Thread starter zSplash
  • Start date Start date
Z

zSplash

I have a search form that searches for, among other things, lastname. There
are several fields in the underlyling table that contain lastnames (call the
fields xLast, yLast, and zLast). My searchResults lists found lastname from
xLast field. How can I list found lastnames, no matter which lastname
field (xLast, yLast, or zLast) holds that last name?

Realizing this is not clear, here's the deal: My resultsDetail of the
searchForm returns a number of fields -- one line per result. So, if the
lastname is found in the tables xLast, yLast, or zLast field, I want to
print that lastname. How do I code to return the fields' value (xLast,
yLast, or zLast)?

TIA
 
Why do you have several last name fields for a record? Your layout appears
to violate relational database normalization rules. In a properly
normalized database, each person should only have one last name. If your
records allow you to enter more than one person, then you should store the
people in a separate related table. Your structure, for example, might be:

tblCompany
CompanyNumber
CompanyName
CompanyAddress1
CompanyAddress2
CompanyCity
etc.

tblContacts
CompanyNumber
ContactFirst
ContactLast
ContactPhone
ContactBirthdate
etc.

In the above example, a company would have one record in the company table.
That company would have one or more related records in the contact table.
 
Each lastname represents a different person who's member of the group.
Can't I list all members of the group in a single table, with MLast, MFirst,
FLast, FFirst, C1Last, C1First, etc.?
 
As Rick pointed out, you have a database design that needs improvement. If a
person can be in one and only one group, then you need a field in the table
that identifies which group the person belongs to. If a person can be in
more than one group, you need a junction table that would have two fields,
the primary key of the group and the primary key of the person's record.
 
So, you're saying I break M, F, C1-Cx into a separate table (call it
NameTable) that has a field for First Last for M/F/C and nameGroup) and then
populate the M,F,C1-Cx fields from the NameTable? (??)
 
No, I'm sorry. I'll try again.

In my "maldesigned" db, I have, like, 6x2 fields for Lastname and Firstname
(FLast/FFirst, MLast/MFirst, C1Last/C1First, C2Last/C2First, etc.)

Are you saying I should create a namesTable that has a pk field, a Last
field, a First field, and a nameType field? Then, I relate the namesTable
to the originalTable, with those fields removed from the original table?
Then, all the names of the db will be contained in the namesTable, with the
nameType field to indicate what type of name it is?
 
Thanks so much. Okay. Now, I've got a Names table with:
pk
OLDpk (relates back to old form -- will delete if necessary)
nameType
First
Last
DOI

How do I replace the fields in the oldForm with the namesTable? I used to
have fields in the form that captured the Fname (First, Last, DOI), the
Mname (First, Last, DOI), the C1name (First, Last, DOI), etc. How do I
"connect" the names now in the NamesTable with the fields in the form?

The only way this feeble brain can think of is that I'd have a drop-down for
the nameType, and then 3 fields that have to be manually filled-in for
First,Last,DOI. I guess I'd need, then, 4 fields (the drop-down and the 3)
for each party I hope to track in the database. Is that right, or is there
a better/more favored method?

TIA, A and A
 
I would use cascading combo boxes to select a record. That is, the second
combo would be filtered based on the value in the first combo.

Your first combo (cboNameType) should be based on the NameTypes Table:
SELECT NameType FROM NameTypesTable

In the After Update event of the first combo, you requery the second combo
so it only shows names with the selected name type:

Me.cboPerson.Requery

Your second combo (cboPerson) needs to be a two column combo. That is so
you can use the pk of the table to navigate to the record you want:
SELECT pk, First & Last AS FullName FROM NamesTable Where NameType =
Me.cboNameType

Set the combo's column count property to 2
Set the bound column property to 1
Set the Column Widths property to 0";3.0"
This will hide the pk and the user will only see the name.

In the After Update event of the second combo, you will use something like
the following to make the selected person's record the form's current record:

With Me.RecordsetClone
.FindFirst "[pk] = " & Me.cboPerson
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
 
I moved this to a new discussion thread -- "retrieving namesTable data to
Form".

Klatuu said:
I would use cascading combo boxes to select a record. That is, the second
combo would be filtered based on the value in the first combo.

Your first combo (cboNameType) should be based on the NameTypes Table:
SELECT NameType FROM NameTypesTable

In the After Update event of the first combo, you requery the second combo
so it only shows names with the selected name type:

Me.cboPerson.Requery

Your second combo (cboPerson) needs to be a two column combo. That is so
you can use the pk of the table to navigate to the record you want:
SELECT pk, First & Last AS FullName FROM NamesTable Where NameType =
Me.cboNameType

Set the combo's column count property to 2
Set the bound column property to 1
Set the Column Widths property to 0";3.0"
This will hide the pk and the user will only see the name.

In the After Update event of the second combo, you will use something like
the following to make the selected person's record the form's current
record:

With Me.RecordsetClone
.FindFirst "[pk] = " & Me.cboPerson
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


--
Dave Hargis, Microsoft Access MVP


zSplash said:
Thanks so much. Okay. Now, I've got a Names table with:
pk
OLDpk (relates back to old form -- will delete if necessary)
nameType
First
Last
DOI

How do I replace the fields in the oldForm with the namesTable? I used
to
have fields in the form that captured the Fname (First, Last, DOI), the
Mname (First, Last, DOI), the C1name (First, Last, DOI), etc. How do I
"connect" the names now in the NamesTable with the fields in the form?

The only way this feeble brain can think of is that I'd have a drop-down
for
the nameType, and then 3 fields that have to be manually filled-in for
First,Last,DOI. I guess I'd need, then, 4 fields (the drop-down and the
3)
for each party I hope to track in the database. Is that right, or is
there
a better/more favored method?

TIA, A and A
Click to expand...
Click to expand...
 
Back
Top