ms access create report with two tables

Question

I am needing to create a report between two tables. One table has ID, FirstName, LastName. The seconded table has NoteID, ID and Notes. There is a one to many relationship. Each person can have many notes. What I would like to do is fill a listbox or textbox with the notes that belong to that person.

On that note, how can I create a report that will display somewhat as the following:

First Name: Jeff    Last Name: Smith
Notes:
 This is note 1 for Jeff Smith
 This is note 2 for Jeff Smith
 This is note 3 for Jeff Smith
 This is note 4 for Jeff Smith

First Name: Kim    Last Name: Yung
Notes:
  This is note 1 for Kim Yung
  This is note 2 for Kim Yung

Thank you for any thing on this to lead me in the right direction.

Mark C. Mark C. · Accepted Answer · 2014-12-23T14:13:24

Okay - so I made a query to use as the Record Source for the Report.

SELECT People.ID, 
       People.FirstName, 
       People.LastName, 
       Notes.NOTES
FROM People INNER JOIN Notes 
ON People.ID = Notes.ID;

I clicked report design, set this query as the Record Source for the report. I added a GROUPING on ID (this is to separate each person's Notes).

This created an ID HEADER, which is where you put FirstName, LastName, and your Notes label.

In the Detail section, you put the NOTES field from your query.

enter image description here

Output

enter image description here

If you have any other questions, let me know.

ms access create report with two tables

2 Answers

Output