Glenn,
I think this will do what you want.
First, my example Tables look like these. In the first one, the key
value is a text string and not a number. (This distinction will be
important later, in the function that matches its value with another Table.)
[Customer] Table Datasheet View:
Customer_ID Customer
----------- ----------------
001 Joe Bloggs Ltd
007 Bond, James
123 Acme Explosives
In the following Table, the primary key, [Employee_ID], is an Autonumber
field.
[Employee] Table Datasheet View:
Employee_ID Employee
----------- --------
-1911156243 Jim
-1516216861 Sarah
-560162532 Glenn
1748767914 Harry
I usually apply Lookup properties to all foreign keys, but please
remember that even though they look different using Lookups, the values
actually stored in the Tables are the key values.
[Cust-Empl] Table Datasheet View (without Lookup properties):
Customer_ID Employee_ID
----------- -----------
001 -1911156243
001 -1516216861
123 -1516216861
001 -560162532
001 1748767914
123 1748767914
This is not very pretty, so after applying Lookup properties to the two
foreign keys, the table looks like this:
[Cust-Empl] Table Datasheet View:
Customer_ID Employee_ID
-------------- -----------
Joe Bloggs Ltd Jim
Joe Bloggs Ltd Sarah
Acme Explosives Sarah
Joe Bloggs Ltd Glenn
Joe Bloggs Ltd Harry
Acme Explosives Harry
The Query that I think you have already defined in your database looks
something like this.
[Q01_EmployeesByCustomer] SQL:
SELECT Customer.Customer_ID,
Customer.Customer, Employee.Employee
FROM Employee
INNER JOIN (Customer INNER JOIN [Cust-Empl]
ON Customer.Customer_ID = [Cust-Empl].Customer_ID)
ON Employee.Employee_ID = [Cust-Empl].Employee_ID
ORDER BY Customer.Customer, Employee.Employee;
.... and it produces a list with repeating customer names.
[Q01_EmployeesByCustomer] Query Datasheet View:
Customer_ID Customer Employee
----------- ---------------- --------
123 Acme Explosives Harry
123 Acme Explosives Sarah
001 Joe Bloggs Ltd Glenn
001 Joe Bloggs Ltd Harry
001 Joe Bloggs Ltd Jim
001 Joe Bloggs Ltd Sarah
The following function reads this Query and produces a list of employee
names similar to what you asked for. Instead of having it call the
named Query, [Q01_EmployeesByCustomer], as it does, you might choose to
put the entire SQL of [Q01_EmployeesByCustomer] into this code.
However, depending on how you prefer to maintain your database, doing it
as I suggest here allows you to maintain the Query in Query Design View,
and the only SQL you have to look at is the filter, and the filter is
pretty short.
'List all employees for the specified customer
Public Function EmpByCust( _
CustomerID As String) As String
'Unfiltered Query
Dim rsEmployeesByCustomer As DAO.Recordset
'Filtered to return only those matching the argument
Dim rsFiltered As DAO.Recordset
'Name of unfiltered Query
Const strQ01 As String = "Q01_EmployeesByCustomer"
'Value to be returned, such as
' "Glenn; Harry; Jim; Sarah"
Dim strResult As String
Set rsEmployeesByCustomer = _
CurrentDb.OpenRecordset( _
strQ01, dbOpenSnapshot)
With rsEmployeesByCustomer
.Filter = "Customer_ID = '" _
& CustomerID & "'"
.MoveLast
Set rsFiltered = .OpenRecordset(dbOpenSnapshot)
End With 'rsEmployeesByCustomer
With rsFiltered
If .BOF Then
EmpByCust = "<Nobody>"
Exit Function
End If '.BOF ...
strResult = .Fields("Employee")
.MoveNext
While Not .EOF
strResult = strResult & "; " _
& .Fields("Employee")
.MoveNext
Wend 'Not .EOF ...
End With 'rsEmployees
EmpByCust = strResult
End Function 'EmpByCust()
And the following Query invokes this function to produce the list you
wanted.
[Q02_EmployeeLists] SQL:
SELECT Customer.Customer_ID, Customer.Customer,
EmpByCust([Customer]![Customer_ID]) AS Employees
FROM Customer
ORDER BY Customer.Customer_ID;
[Q02_EmployeeLists] Query Datasheet View:
Customer_ID Customer Employees
----------- ---------------- ------------------------
001 Joe Bloggs Ltd Glenn; Harry; Jim; Sarah
007 Bond, James <Nobody>
123 Acme Explosives Harry; Sarah
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Glenn said:
Thanks for your reply. I should have explained myself a little better. I've
already done what you suggested. That part is working fine.
I want a query showing all the Customers with one calculated field that
shows all the related Employee's. So Joe Bloggs Ltd (say customer 1) will
have a calculated field called Employees with say "Glenn; Jim; Harry; Sarah"
in it. These are obviously four separate Employees that are linked to this
customer. Some code needs to be written to get this I think? The code bit is
what I need.
I know I can create a inner join between the two tables to get a list of
related Employee's. The problem with this is - if the Customer is linked to
two Employee's, the Customer will be listed twice in the Query. This isn't
want I want.
Regards,
Glenn
:
[...]
