Displaying all the entries in one field

[canfish]

Hello friends,



I am making a report that includes mostly specific information (opening date, address, etc.) about facilities. I want it to include all of that information, but I also want the report to show, for example, all of the employees that work in that facility. Like this:

FacilityName: Scranton
OpeningDate: 2010/03/05
AddressLine1: 303 Ternatelli St.
...
Employees: Derek Fisher, Angela Flew, Stanley Morgan, Erik Redman...

But I can't seem to find a way to include a list like that. I have all of my employees listed on a separate table, and I can only get them in under the detail section (where I can't keep them in line with my other information) or as single entries in the header column. Is there a way to get a list in there using queries or otherwise? I can't tell.

I have been using the wizard starting off, but do you think I should just start in design view if I want to do this kind of thing?

Any and all help is appreciated. Thanks!

[jzwp11]

You will need a custom function that concatenates the names of all employees related to a facility. Allen Browne has example code on his site

[canfish]

Thanks jzwp! However, I have no idea how to implement this into a form. How should I go about putting the field into the database and using the module on it?

Thanks for your help!!!

[jzwp11]

Allen's site gives the directions on how to use the code. In Access 2007, look for the code window icon on the ribbon and create a new module from there. Then copy & paste the code from Allen's site into that new module. (starting with Public Function ConcatRelated(strField As String... and ending with End Function). Allen's code is very general and can be applied to any field within a table or query you specify. So in your report, add a control to the facilities section of your report (not the detail section). For the control source call the function:

This is the example Allen provided:

=ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])

You just need to modify the call for your database. Based on your earlier post, you have a junction table that relates facilities and employees and you have your employee table also

tblFacilityEmployees
-pkFacEmpID primary key, autonumber
-fkFacilityID foreign key to tblFacilities (long number data type field)
-fkEmployeeID foreign key to tblEmployees (long number data type field)


tblEmployees
-pkEmployeeID primary key, autonumber field
-txtFName
-txtLName
-txtSSN

I would create a query and then use that in the function:

query name: qryFacilityEmployees
SELECT txtFName & " " & txtLname as EmployeeName, fkFacilityID
FROM tblFacilityEmployees INNER JOIN tblEmployees on tblFacilityEmployees.fkEmployeeID=tblEmployees.pkE mployeeID

So, substituting the query info into the function call yields the following:

=ConcatRelated("EmployeeName", "qryFacilityEmployees", "fkFacilityID = " & [CompanyID])

The only thing left to take care of is the [companyID], this refers to another control on the report (or form or a field in a query from wherever you are calling the function). In your case, you want to reference the control on your report that holds the facilityID.

[canfish]

Jzwp,

Thanks again for the help!

I tried very hard to get the function working. I couldn't. I'm not really sure what you mean by "reference the control" in your last paragraph. What exactly am I doing there?

What is a "call"?

Also, what exactly is the code you wrote after telling me to create a query and use that in the function? Is that SQL language? If so, what exactly does it do and where do I put it?

[jzwp11]

I'm not really sure what you mean by "reference the control" in your last paragraph.

Text boxes, combo boxes and list boxes on either a form or a report are "controls". Controls can be bound to a field in the underlying table or query or they can be unbound. I assume that on your report you have a control that displays the pkFacilityID value, you need get the name of that control (check out the properties tab for the control) and put that in the function.

When you do this: ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID]) you are calling the function ConcatRelated() to do its thing. The things within the ( ) need to be supplied by you in order for the function to run & return your data. Since code was used to create the ConcatRelated() function, it is considered a custom function. There are several built-in functions already in Access, you can do a search in Access to find these functions.

The following text is the SQL (Structure Query Language) version of a query. If you create a simple query using the design view and then switch to SQL view, you will see something that looks like this:

SELECT txtFName & " " & txtLname as EmployeeName, fkFacilityID
FROM tblFacilityEmployees INNER JOIN tblEmployees on tblFacilityEmployees.fkEmployeeID=tblEmployees.pkE mployeeID

If you post your database (with any sensitive data removed), I can copy the code in. Additionally, if you provide the name of the report where you want to use the function, I can add a control there to provide an example. If you are using Access 2007, I will work on it tonight since I don't have Access 2007 here at work.