Forum Discussion
Àccess Reports
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)
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;
- you need a "routine" that will save each "unit", "resident" into a separate record on a temp table.
then use this table as recordsource of your report.- As is too often the case, a work-around that doesn't address the inappropriate table design is at best a work around that perpetuates the problems inherent in trying to work with inappropriate, non-relaxational table designs. Reliance on code "routines" to compensate for inappropriate table design may appeal to the desire for a short-term solution, but it is almost never a useful long-term response.
In other words, the OP does not "need" to implement this workaround. The OP could avoid the hard work of designing a proper relational database application, but that is not satisfying the underlying need. - Thank you
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.
