Forum Discussion
Àccess Reports
Hi George, thank you for trying to help.
I have a Residents Table and a Unit Table
Resident Table fields: ResidentID, UnitID, Initials, Surname
Unit Table fields: UnitID and other fields relating to the Unit Building, i.e. PricePaid etc.
I hope you will be able to assist.
The original request was "How do I create a Report displaying Initials and Surnames per Unit? i.e. Unit ID, ResidentID, Initials, Surname (one line per unit)"
The key there is, I think, "one line per unit". That's possible, but it is not "out of the box" functionality.
You can use your two tables to create a query that joins Residents to Units.
Given the table description provided, you could start with the following query:
SELECT Unit.UnitID, Resident.ResidentID, Resident.Initials, Resident.Surname
FROM Unit INNER JOIN Resident on Unit.UnitID = Resident.ResidentID
ORDER BY Unit.UnitID
This query, of course, returns one line per resident, which isn't what you require, so to concatenate Residents into a string for each unit, you adopt a different approach.
One such approach is to use a VBA function to concatenate the residents. Here is one good example. This function has been around for at least 10 years and is well tested. Implement it like this:
SELECT Unit.UnitID, SimpleCSV("SELECT Resident.ResidentID FROM Resident WHERE UnitID= " & [Unit].[UnitID],";") AS Residents
FROM Unit INNER JOIN Resident ON Unit.UnitID= Resident.ResidentID;
I believe I didn't actually provide a full example of the query. You wanted multiple fields in the list, not just the ResidentID.
SELECT Unit.UnitID, SimpleCSV("SELECT Resident.Initials & " " & Resident.Surname AS Resident FROM Resident WHERE UnitID= " & [Unit].[UnitID],";") AS Residents
FROM Unit INNER JOIN Resident ON Unit.UnitID= Resident.ResidentID;- Thanks a million.
