Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

Microsoft Access Problem - Duplication

I created a Microsoft Access database with two primary tables... [TABLE A Sponsors] and  [TABLE B Athletes].  The tables have a "many to many" relationship...i.e. Each sponsor can have many athletes, and each athlete can have many sponsors. I use a junction table to associate the records. It works just fine. My problem is that I am using [MS Access Reports] to generate letters (Invoices) to the people in [TABLE A Sponsors].  Each letter also includes a list of that particular sponsor's athletes.  I must have MS Access Reports produce ONLY ONE letter to each sponsor.  No matter what I do, I get multiple letters produced for each sponsor (the same number as there are athletes associated with that sponsor). For example: if a sponsor has two athletes, MS Access reports produces two "Dear Sponsor" letters.  How can I fix this?
0
ebtspec
Asked:
ebtspec
1 Solution
 
mgrattanCommented:
You will need two reports; a main report and a sub-report.  Base the main report on Sponsors and the sub-report on a query that includes the junction table and the Athletes table (with appropriate Join conditions).  The sub-report is then added to the main report and linked via the LinkChildFields and LinkMasterFields properties in the subreport.

Let me know if you need further info.

Mike.
0
 
nico5038Commented:
Besides the subreport approach you can also add a grouping level to the main report.
When you define a new report Access will ask you for a grouping level. Just select here the sponsor.
Now you can specify that the sponsor level is to appear on a different page and in the detail-section the athletes will be printed.
Thus you can produce all letters in one run.

Nic;o)
0
 
NosterdamusCommented:
Hi ebtspec,

Another approach is to base your report on a query that will return a list of all the sponsors which are associated with athletes:

SELECT first([sponsor ID]) as SP_Name FROM tjunction

Hope this helps,

Nosterdamus
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now