Solved

Microsoft Access Problem - Duplication

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

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

Suggested Solutions

Title # Comments Views Activity
Password on a button in Access 2013 7 35
Contents of subform based upon combobox value 3 26
Access query with left expression 5 29
Access 2010 Query Syntax 5 22
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

910 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

24 Experts available now in Live!

Get 1:1 Help Now