Reports - How to print only one when using a one-to-many relationship

[Modify_inc]

I am designing a report that has notes that are linked to a table in Access 2013. The note table and the client table are using a one-to-many relationship, as I can have multiple notes for a client. The problem is the report wants to print every note that is linked to the client table. I just want it to print the most recent note linked to the client's table.

Example of print out of what it is doing:

John Doe - 123 Easy St - Notes 1 (Most Recent Note)

John Doe - 123 Easy St - Notes 2 (Previous Note)

John Doe - 123 Easy St - Notes 3 (and so on...)

See, it is printing duplicates of the same client, and then just adding the additional notes for that same client.

How can I tell it just to print the client one time in the report, and most importantly, to only use the most recent note that is linked to the client?

Thanks
Mike

[ranman256]

Have the report's query grab the MAX(entryDate) to pull the lastest record.
(or max(index) or whatever yousave in the NOTES table)

[June7]

Tricky.

Can try the TOP N qualifier in query. Presume there a date field in the Notes table.

Review http://allenbrowne.com/subquery-01.html#TopN

[Modify_inc]

This section of the link you provided looks like it should work for what I need, if I could just figure out how to implement it into my own SQL:

TOP n records per group

You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:


SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
(SELECT TOP 3 OrderID
FROM Orders AS Dupe
WHERE Dupe.CustomerID = Orders.CustomerID
ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

So I just need one record, and it needs to be the last one entered, not three.

So I added this to my SQL code (didn't work though, keep getting a syntax error):

WHERE tblNotes.NotedID IN
(SELECT TOP 1 NotedID
FROM tblNotes AS Dupe
WHERE Dupe.ClientID = tblNotes.ClientID
ORDER BY Dupe.NoteDate DESC, Dupe.NotedID DESC);

I am still very much a novice with Access, so editing the SQL directly for me is rather pointless without some guidance.

If it helps, this is my original SQL code before adding the above code:

SELECT tblDateLoss.[Job Number], tblClientInfo.[Type of Loss], tblDateLoss.[Insured's Name], tblDateLoss.[Address of Loss], tblDateLoss.City, tblDateLoss.[Home Phone], tblClientInfo.[Insurance Company], tblClientInfo.Agency, tblClientInfo.[Insurance Agent], tblDateLoss.[Date Received], tblClientInfo.ProjMgr, tblClientInfo.[Marketed By], tblDateLoss.[Mitigation Amount], tblDateLoss.[Repair Amount], tblNotes.Notes, tblNotes.User, tblNotes.NoteDate
FROM (tblClientInfo LEFT JOIN tblDateLoss ON tblClientInfo.ID = tblDateLoss.ID) LEFT JOIN tblNotes ON tblDateLoss.ID = tblNotes.ClientID;


Thanks
Mike

[June7]

I would need sample raw data to figure out the TOP N approach, if it's even doable with your data.

Another approach uses domain aggregate function but not quite the way ranman describes. Unfortunately domain aggs can be slow.

Build a query that joins the tables then build another query:

SELECT * FROM queryname WHERE NoteDate = DMax("NoteDate", "Notes", "ClientID=" & [ClientID]);

Maybe another approach:

Query1
SELECT ClientID, Max(NoteDate) AS MaxDate FROM Notes GROUP BY ClientID;
or maybe
SELECT ClientID, Max(NoteID) AS MaxNote FROM Notes GROUP BY ClientID;

Query2
build query that joins ClientInfo and Notes tables

Query3
join query 1 and query 2 on the ClientID and date (or NoteID) fields (compound join)

[Modify_inc]

I would need sample raw data to figure out the TOP N approach, if it's even doable with your data.

Another approach uses domain aggregate function but not quite the way ranman describes. Unfortunately domain aggs can be slow.

Build a query that joins the tables then build another query:

SELECT * FROM queryname WHERE NoteDate = DMax("NoteDate", "Notes", "ClientID=" & [ClientID]);

Maybe another approach:

Query1
SELECT ClientID, Max(NoteDate) AS MaxDate FROM Notes GROUP BY ClientID;
or maybe
SELECT ClientID, Max(NoteID) AS MaxNote FROM Notes GROUP BY ClientID;

Query2
build query that joins ClientInfo and Notes tables

Query3
join query 1 and query 2 on the ClientID and date (or NoteID) fields (compound join)

Thanks for the suggestions!

I am attaching a clean version of my database with two fictitious clients with sample notes for each client. You will see in the report the duplicates that I am trying to remove. The report should only show the two clients, and the last note entered for each one.

Let me know if you need anything else from me, and thanks again for your help!

BTW the database is split, so I will be including the frontend and backend files in the zip.

Thanks
Mike

Database.zip

[June7]

Normally a form can be used to enter/edit data to only one table. The main form should not be set up to allow entry/edit of both tblClientInfo and tblDateLoss.

You have these tables linking on autonumber fields. A conventional arrangement for PK/FK is the PK field can be autonumber but then FK field would be a number field, not autonumber.

EDIT: Just did a test of entering records and it actually seems to work. I would not have expected this. However, can each client have more than one loss claim? This arrangement will not allow that.

[Modify_inc]

Normally a form can be used to enter/edit data to only one table. The main form should not be set up to allow entry/edit of both tblClientInfo and tblDateLoss.

You have these tables linking on autonumber fields. A conventional arrangement for PK/FK is the PK field can be autonumber but then FK field would be a number field, not autonumber.

EDIT: Just did a test of entering records and it actually seems to work. I would not have expected this. However, can each client have more than one loss claim? This arrangement will not allow that.

Sorry for the late reply, didn't realize you made an edit to the post.

I have reworked the database and now have merged the two tables into one. I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: http://www.databasedev.co.uk/access_max_function.html.

It works, but the problem is it only shows the latest date for each note, not the actual note/comment.

The example given relates to orders made per customer, while in my database, it is notes made per client. But what good is the order date without the actual order details, as so, the note's date without the actually notes?

[June7]

This gets tricky because each record must consider value in other records of same table as filter criteria.

As noted in posts 3 and 5, possible methods are TOP N nested query and domain aggregate function.

SELECT Clients.*, Notes.* FROM Clients INNER JOIN Notes ON Clients.ClientID=Notes.ClientID WHERE Notes.NoteDate = DMax("NoteDate", "Notes", "ClientID=" & [ClientID]);

[Modify_inc]

Can I please upload a sample of my new database and you look at it? I have been messing with this since you posted, and for the life of me, I cannot get it to work. I have a feeling I am entering the SQL wrong in my query. I keep making changes and altering the SQL structure, but I continue to get errors, and the worst, stuck in a loop requiring me to force close Access.

Thanks again
Mike

EDIT: I will go ahead and upload it, and you can decide if you want to bother with it or not. I feel I have a really basic database, and everything else works as I need it, but this whole Notes thing is driving me bonkers to say the least. I truly do not understand why I can't just select an option in the query to only display the last or most recent record for the Notes. I slightly understand the concept that it requires two queries or a subquery, but I do not understand why I have to know SQL programming to accomplish such a mundane task. I say mundane because this is a database program and I would think it should be able to make sense of this simple request. Such as an option to allow the user to select a subquery when creating a query, and thus allowing the user to select the parameters from a list to execute the query as intended. Instead we have to do it manually, which requires knowing SQL logic/programming.

Sorry for my rant, I'm just getting extremely frustrated with trying to solve this.

[Modify_inc]

My sample Database is attached with all data removed.

I have added two fictitious clients with accompanying notes/comments.

At the top-left of the form are buttons for displaying a variety of reports.

If you click the Report by Date, it will ask you to enter the start and end dates. Enter 7/10/14 for start date and 7/11/14 for the end date. If you click the Report by Job#, enter 100 for start and 101 for last.

You will notice the report will display seven rows of data when there is only two clients.
This is because it wants to display all the notes associated with each client, instead of just one note (preferably the last, most recent note).

Under Queries you will see two queries named qryNotesDate and qryLastNoteByClient. These are test queries, basically my attempt at getting the last note records. It works, but it will only provide the dates of the last note records, not the actual contents of the notes.

Please tell me you or someone knows how to accomplish this task.

Thank you
Mike

[Modify_inc]

Tricky.

Can try the TOP N qualifier in query. Presume there a date field in the Notes table.

Review http://allenbrowne.com/subquery-01.html#TopN

I want to thank you for offering this suggestion! I was finally able to rework the TOP N records approach successfully for my desired results.

Thanks again!