Solved

Microsoft Access Problem - Duplication

Posted on 2002-04-12
3
159 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

685 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