combining two Name fields in a Query on a Report

[Kyle]

Sorry for starting a new thread on this, but I was unable to post my
question in the thread from July 5th in which I thought I'd found my
solution.

I have a database in which we are tracking sample freezers for the
primary investigators at a medical school. What I am trying to do is
take separate first and last name fields from a query and have them
print in the report in the format first name, space, last name. I tried
creating a text box and made the Data Control Source the expression
=[qryUpdate Sheet]![Fname] & " " & [qryUpdate Sheet]![Lname]. But when
I switch over to view the report, I get little dialog box asking "Enter
Parameter Value - qryUpdate Sheet".

The kink is that there is a one-to-many relationship between names and
units - some primary investigators are responsible for many as 20-30
units in the database.

So what am I doing wrong? (Obviously it's me that's wrong and not
Access!) What is the "Enter Parameter Value" box looking for?

 

[SusanV]

Hi Kyle,

Try changing the exclamation marks to periods:
=[qryUpdate Sheet].[Fname] & " " & [qryUpdate Sheet].[Lname].

 

[BruceM]

If the form's record source is qryUpdateSheet then all you need in the text
box Control Source is:
= [FName] & " " & [LName]
You could also create a field in the query:
FullName: [FName] & " " & [LName]
and bind the text box to FullName

I'm not quite certain, but I think you would use:
[Queries]![qryUpdateSheet]![FName] to refer to a field in another query. It
would probably work in your case, but is more than you need if the form is
already bound to the query.

 

[Kyle]

Bruce,

Thanks - that was the trick I needed. I guess Access' Expression
Builder doesn't intuit when you are building the expression within the
table or query vs. accessing information in another table or query, so
it automatically sticks the "[queryname]!" before the field name.

Susan, I gave your suggestion a try for the sake of those who will come
after me: I thought it might be helpful to indicate where the field
data is being pulled from. But changing the "!" to "." did nothing - it
still asked for Parameter Values. So I'm just going to have to detail
that information in the Manual.

Thanks for your help, both of you!

 

[SusanV]

Sorry to steer you down the wrong path - re-reading your post and Bruce's
reply I see now that you already had the data source defined in the report.

My bad!

 

[BruceM]

Glad it worked. I don't have much use for Expression Builder myself.

 

[Kyle]

OK, well, since you're both here, let me ask a follow-up question:

I have a main form that I use for surveying and updating the freezer
units in the building. In that form I have a subform that lists the
employees that are emergency contacts for that freezer. That subform
gets its data from a query that pulls from two different tables, one of
which contains the fields [Fname] and [Lname] which I combined to
display as first and last names.

Here's the tricky part: what I want to do is, when a contact is added
to a freezer, I want the user to be able to only choose from the list
of existing names - what I'm envisioning is a combo box with the list
of "combined" names.

The only other option I can think of is to have the last name field the
combo box and have it automatically fill in the other fields such as
first name and phone numbers.

Any thoughts on how to make this happen? Thanks!

 

[BruceM]

An explanation of your table structure and relationships would help. You
say that there is a main form for surveying and updating the freezer units.
Is each unit a separate record, or are all of the freezers together in a
single record? What two tables are being included in the subform record
source query? If I understand the requirement to limit the contacts to a
certain list of people, in the Personnel table you could add a Yes/No field
for FreezerContact, then limit the query to people for whom that box is
checked.
As for populating the subform, selecting last name is not likely to work
well, since two people could have the same last name. The combo box (I will
call it cboFullName) list of combined names should work, but you probably
don't want to store much more than the primary key field for that person's
record. One way you can do that (I don't know if it is the best way, but it
works well enough if you have just a few fields other than the one in the
combo box) is to include phone number in the combo box row source. If the
phone number appears in the third column of the combo box row source, the
text box in which you want the phone number to appear could have as its
Control Source:

Me.cboFullName.Column(2)

Note that the column count starts with 0, so 2 is the third column. Make
sure the column count (combo box Properties, Format tab) includes the phone
number column.

If your subform is to contain a list of names, it should probably be a
continuous form, with each person's name as a separate record. You begin to
see why table structure and other technical information is important to a
targeted answer?

 

[Kyle]

A quick survey of the structure: the parent/primary table contains
information on the several hundred freezers around the School -
specifications, configuration, location information. There is a
separate table for all employees and students of the School (call it
the employee table; a monster that contains several hundred records),
and the primary key is the person's ID number with the School. There is
a third table that links the primary and and employee tables by the
unique ID numbers for both freezers and employees; it contains the
employee ID, the freezer ID, and a priority number which sorts who is
contacted in what order for a given freezer. This is necessary because
an employee can be listed as a contact on many freezers, and each
freezer has a required four contacts listed in the event of an
emergency, so the one-to-many relationships go both ways.

So here's what I managed to do: in the third, linking table, I went
into the Lookup properties of the Employee ID field, I changed the
Display Control to Combo Box, the Row Source Type to Table/Query, and
the Row Source is "SELECT tblEmployees.EmployeeID, [lname] & ", " &
[fname] AS [Full Name] FROM tblEmployees ORDER BY [lname] & ", "
[fname];" (without the quotation marks, obviously), Bound Column is 1,
Column Count is 2.

I can't claim originality for this, but copied and modified it from
something an IS person had done on one of the other campuses of the
university. It seems to work though, for the life of me, I can't figure
out how [Full Name] plays into it because I can't find that as a field
or anything else anywhere else in the database...

 

[SusanV]

Kyle,

Hi Kyle,

Take a close look at the SQL - "Full Name" is an Alias of the 2 fields lname
and fname concatenated:
[lname] & ", " & [fname] AS [Full Name]

 

[BruceM]

It looks as if you are using a lookup field in the linking table. I have
never tried that, but I have read enough postings here to know that lookup
fields often lead to grief. Here is something you should check out:
http://www.mvps.org/access/tencommandments.htm
Pay particular attention to the second item.

Each freezer can have many (more than one) contacts, and each contact can be
responsible for many freezers. That is a many-to-many relationship, which
is resolved through a third table, as you have done. You expressed it by
saying the one-to-many goes both ways. This third table is often called a
junction table.
A common way of handling this would be to have a main form (frmFreezers)
based on the freezers table (tblFreezers) and a subform (fsubCallList) based
on the junction table (tblCallList). Set the default view of fsubCallList
to Continuous. A combo box on fsubCallList is bound to EmployeeID in
tblCallList. Its row source would be the employee table (tblEmployee), with
EmployeeID the bound column and FullName the visible column. I often make a
named query and use that as the Row Source, but there are a number of
options. One of the things you will want to do, as I understand the
situation, is to arrange the records in tblCallList according to the
priority number. One way of doing that is to make a query (qryCallList)
based on tblCallList. Sort the query by the priority field (i.e. by who
gets called first), and use the query as the record source for fsubCallList.
With frmFreezers open in design view, drag the icon for fsubCallList onto
It. Select the subform control (which you can do by clicking the very outer
edge of the subform), and verify that the linking fields are set to
EmployeeID. This design should let you view a freezer record and see a
prioritized list of who to call in an emergency.
You can use a report based on tblEmployees, with a subreport based on
tblCallList, to view the list in the other order: to which freezers is a
particular person assigned.

A quick survey of the structure: the parent/primary table contains
information on the several hundred freezers around the School -
specifications, configuration, location information. There is a
separate table for all employees and students of the School (call it
the employee table; a monster that contains several hundred records),
and the primary key is the person's ID number with the School. There is
a third table that links the primary and and employee tables by the
unique ID numbers for both freezers and employees; it contains the
employee ID, the freezer ID, and a priority number which sorts who is
contacted in what order for a given freezer. This is necessary because
an employee can be listed as a contact on many freezers, and each
freezer has a required four contacts listed in the event of an
emergency, so the one-to-many relationships go both ways.

So here's what I managed to do: in the third, linking table, I went
into the Lookup properties of the Employee ID field, I changed the
Display Control to Combo Box, the Row Source Type to Table/Query, and
the Row Source is "SELECT tblEmployees.EmployeeID, [lname] & ", " &
[fname] AS [Full Name] FROM tblEmployees ORDER BY [lname] & ", "
[fname];" (without the quotation marks, obviously), Bound Column is 1,
Column Count is 2.

I can't claim originality for this, but copied and modified it from
something an IS person had done on one of the other campuses of the
university. It seems to work though, for the life of me, I can't figure
out how [Full Name] plays into it because I can't find that as a field
or anything else anywhere else in the database...

 

[Kyle]

That is a great deal of information to process, and I thank you for
your detailed help!

I read the link to the Ten Commandments, and its link to why not to use
Lookup values, and while I don't quite understand it all, I know they
are evil and must be avoided. At some point I will have to take the
time to do some redesign (if the contract allows) and eliminate those.

As to the rest - to quote the kids on the message boards, "my head
asplode!" I'm going to take some non-work hours to figure that out and
practice the builds. Thanks again!