Solved

Microsoft Access Problem - Duplication

Posted on 2002-04-12
3
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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