Report sorting/grouping questions (1 Viewer)

BeardedSith

Member
Local time
Today, 08:14
Joined
Feb 5, 2020
Messages
73
Hi there!
I have a report that uses three separate tables for data. I have the data separated into three queries.

Goal: For the MemberID selected, I want a section for Purchases and a separate section for Rewards. I can do it with subreports, but I'm not sure if it's possible with one, single report.

Three tables:
tblMembers, tblPurchases, tblRewards.
Form: frmRewards
Queries: sqryPurchaseMemberHistory, sqryRewardsMemberHistory, and qryMemberHistory. The two subqueries feed into the main query.

For each row where MemberID=ID!tblMembers in tblPurchases & tblRewards I want the information. However, I would like to section out each of the tables' information. So I have a header for Member Name, which is pulled from tblMembers. I would like a header for tblPurchases and one for tblRewards. Each header would have the corresponding records listed under the header. The issue I'm having is every entry for each of these two tables is posting the "header" along with it.

Example:
Capture.PNG

And here is the design for the report:
Capture.PNG


And just in case it's needed, here are the three queries:

sqryPurchaseMemberHistory:
Code:
SELECT tblMembers.ID, [First Name] & " " & [Last Name] AS FullName, tbPurchases.ID AS PurchaseID, tbPurchases.PurchaseDate, tbPurchases.PurchaseAmount
FROM tblMembers INNER JOIN tbPurchases ON tblMembers.ID = tbPurchases.MemberID;
sqryRewardsMemberHistory:
Code:
SELECT tblMembers.ID, tblRewards.ID AS RewardsID, tblRewards.IssueDate, tblRewards.IssueAmount, tblRewards.CardNumber
FROM tblMembers INNER JOIN tblRewards ON tblMembers.ID = tblRewards.CustomerID;
qryMemberHistory (This is the data the Report uses):
Code:
SELECT sqryPurchaseMemberHistory.ID, sqryPurchaseMemberHistory.FullName, sqryPurchaseMemberHistory.PurchaseDate, sqryPurchaseMemberHistory.PurchaseAmount, sqryRewardsMemberHistory.IssueDate, sqryRewardsMemberHistory.IssueAmount, sqryRewardsMemberHistory.CardNumber, sqryRewardsMemberHistory.RewardsID, sqryPurchaseMemberHistory.PurchaseID
FROM sqryPurchaseMemberHistory INNER JOIN sqryRewardsMemberHistory ON sqryPurchaseMemberHistory.ID = sqryRewardsMemberHistory.ID
WHERE (((sqryPurchaseMemberHistory.ID)=[Forms]![frmRewards]![txtID]));

Is there a way to accomplish this without doing subreports? I've worked with subreports before and always hated them.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:14
Joined
Oct 29, 2018
Messages
21,357
Hi. Are you able to post a demo version of your db with test data? It might help...
 

BeardedSith

Member
Local time
Today, 08:14
Joined
Feb 5, 2020
Messages
73

Attachments

  • RewardsDB2019.accdb
    4 MB · Views: 538
  • Rewards_BE.accdb
    4 MB · Views: 409
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:14
Joined
Jan 14, 2017
Messages
18,186
Many forum members won't download files from external sites.
Suggest you upload a copy of your database as one file with just the relevant database objects.
Include any required BE tables as local tables. Compact & zip then upload here
 

BeardedSith

Member
Local time
Today, 08:14
Joined
Feb 5, 2020
Messages
73
Many forum members won't download files from external sites.
Suggest you upload a copy of your database as one file with just the relevant database objects.
Include any required BE tables as local tables. Compact & zip then upload here
I edited my last post to put both files in there. In order to get everything back to one file, it'll take a substantial amount of work, but I removed several records from the backend so I could upload it here.
 

isladogs

MVP / VIP
Local time
Today, 12:14
Joined
Jan 14, 2017
Messages
18,186
I've downloaded both and compacted them to around half the size.
See zip file attached.

I've relinked the tables and then found that you have locked it down using code so the navigation pane is hidden.
Whilst I could spend time removing your restrictions, that is something you should do if you want us to look at it.
Please modify and upload again

However, I have looked at your relationships and due to the one to many joins I would suggest 2 subreports are probably the best solution anyway.
 

Attachments

  • Rewards.zip
    2 MB · Views: 482

theDBguy

I’m here to help
Staff member
Local time
Today, 05:14
Joined
Oct 29, 2018
Messages
21,357
I edited my last post to put both files in there. In order to get everything back to one file, it'll take a substantial amount of work, but I removed several records from the backend so I could upload it here.
Hi. Is this close to what you wanted?
report.PNG
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:14
Joined
Oct 29, 2018
Messages
21,357
That's pretty close, however I just went ahead and designed the report using sub-reports. It's not as neat-and-tidy as I prefer, but it works. Thanks for the help!!
Hi. Glad to hear you found a solution. Good luck with your project.
 

Users who are viewing this thread

Top Bottom