Àccess Reports

Occasional Contributor

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)

10 Replies

@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?

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.

@George Hepworth 

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.

Thank you

@Ansiep1 

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.

@Ansiep1 

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;

 

 

 

@George Hepworth 

 

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;

 

 

@arnel_gp Thank you for your trouble.  It is really appreciated.

Thanks a million.