Jul 21 2022 03:21 AM
I have a Residents Table which includes Units (with their ID) with two or three Residents (Each with their separate IDs). How do I create a Report displaying Initials and Surnames per Unit? i.e. Unit ID, ResidentID, Initials, Surname (one line per unit)
Jul 21 2022 04:50 AM
@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?
Jul 21 2022 05:57 AM
Jul 21 2022 06:36 AM
Jul 22 2022 12:57 AM
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.
Jul 22 2022 01:28 AM
i made a sample for you to understand.
there are 2 tables, one is "normalized" called proper table.
the other is, i am guessing, what you have, non-normalized, improper table.
then i made reports for each table..
the improper report calls a Public function to separate each unit, resident to each row.
Jul 22 2022 07:06 AM
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;
Jul 22 2022 12:40 PM
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;
Jul 25 2022 06:14 AM
@arnel_gp Thank you for your trouble. It is really appreciated.