Access Report/Query Design

1
votes

I'm trying to design a report in Access that's taking info from 3 separate tables.

Table layout is as follows:

Main User Table:
UserID (Primary Key)
First Name
Last Name
FullName (Combination of First and Last Name fields)

Friends Table:
FriendID (Primary Key)
UserID (Foreign Key)
First Name
Last Name
Street Address
City
State
Zip
Phone Number CommentID (Foreign Key)

Comment Table: CommentID Comment

For this example let's say there's a record Jane Doe in the Main User table and then she has three friends Mary Doe, Lotta Doe and Pisa Doe. There are also 3 comments for every friend.

With this information I have built a query that has all 3 tables and all the available fields added and a Criteria on the Main User tables FullName column: [Enter Users Full Name:]

What I'm trying to get on the report is the main users FullName as a header that would repeat every page if necessary, then list the friends name and address info once then print all the comments linked to that friend (3 in this case).

So the report would look like:

Jane Doe


Mary Doe

123 Main St
Anywhere, ST, 12345
123-456-7890
Comment:
Had lunch on Friday
Comment:
Birthday on Saturday
Comment:
Has a kid named Jeffery

Lotta Doe

124 Main St
Anywhere, ST, 12345
123-456-7890
Comment:
Having lunch on Tuesday
Comment:
Going to gym on Friday
Comment:
Flying to LA on Monday

Pisa Doe

123 Main St
Anywhere, ST, 12345
123-456-7890
Comment:
Hates Pizza
Comment:
Has blue house
Comment:
Has six kids

When creating the report I group by the Main person then the friend then the comment but I always end up with the first person in the friend list but all the comments. Any suggestions on how I should be creating the query and report to get the desired result? Thanks in advance!

1
ms-access
can you take a screenshot of your report design? Slightly unrelated, what do you do if a friend is also a main user? – Brad

1 Answers

0
votes

Your Comment table doesn't contain enough information to support your reporting need. I suggest the following columns:

  • CommentID
  • ByUserID (foreign key to Users table, UserID)
  • AboutFriendID (foreign key to Friends table, FriendID)
  • Comment

After changing the above, and filling it up with all user, friend and comment data, you must delete the CommentID column from the Friends table.

Now you need to adapt the base query which is the data source of your report to give you the following columns of output (e.g.):

  • UserFullName
  • FriendFullName
  • FriendAddress
  • FriendPhone
  • UserCommentAboutFriend

You can use this data format to get the report you need.