Solved

Microsoft Access Problem - Duplication

Posted on 2002-04-12
3
153 Views
Last Modified: 2013-11-28
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
Comment
Question by:ebtspec
3 Comments
 
LVL 14

Expert Comment

by:mgrattan
ID: 6937641
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 300 total points
ID: 6937728
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6940025
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now