Forum Discussion
Àccess Reports
Ansiep1 The cursory description of the tables raises at least one red flag about table design problems.
So, before we start trying to offer specific suggestions that may or may not be useful, let's get a clear picture of the tables involved. How many tables are there in this relational database applications?
What are the exact fields (name them, please) in each table?
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.
