BeardedSith
Member
- Local time
- Today, 10:41
- 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:
And here is the design for the report:
And just in case it's needed, here are the three queries:
sqryPurchaseMemberHistory:
sqryRewardsMemberHistory:
qryMemberHistory (This is the data the Report uses):
Is there a way to accomplish this without doing subreports? I've worked with subreports before and always hated them.
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:
And here is the design for the report:
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;
Code:
SELECT tblMembers.ID, tblRewards.ID AS RewardsID, tblRewards.IssueDate, tblRewards.IssueAmount, tblRewards.CardNumber
FROM tblMembers INNER JOIN tblRewards ON tblMembers.ID = tblRewards.CustomerID;
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.