query returning multiple copies of same record

  • Thread starter Thread starter Rosa
  • Start date Start date
R

Rosa

Hi;

I have the following query to track a scholarship golf
tourney:
-------------------------
SELECT tblTeamInfo2004.*,
tblCompanyInfo2004.strCompanyName,
tblTeamMembers2004.strMemberFirstName,
tblTeamMembers2004.strMemberLastName,
tblTeamMembers2004.memMemberNotes,
tblTeamMembers2004.ysnCaptain,
tblPledges2004.strSponsorshipLevel,
tblPledges2004.numPledgeAmount INTO Teams
FROM ((tblCompanyInfo2004 INNER JOIN tblPledges2004 ON
tblCompanyInfo2004.numCompanyID =
tblPledges2004.numCompanyID) INNER JOIN tblTeamInfo2004 ON
tblCompanyInfo2004.numCompanyID =
tblTeamInfo2004.numCompanyID) INNER JOIN
tblTeamMembers2004 ON tblTeamInfo2004.numTeamID =
tblTeamMembers2004.numTeamID;
-------------------------------

My problem is this: When I have more than one team from a
company, then run this query, the results show the data of
everyone on both teams twice. When there is only one team
from a company this doesn't happen.

Thanks for any input.

R.
 
What are the relationships between the tables? Can a company have more than
one pledge? Like this? ...
Pledges <--- Companies ---> Teams ---> Team Members

If so, that's your problem. If a company has 2 pledges and 2 teams, you
will get 4 sets of data - each pledge matched with each team.

If that's not the situation, then I'll need more info to solve your problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John,

Yes, that's exactly the problem. So, how can I have a
company be able to have more than one pledge/team, without
having this problem? I suspected it was a relationship
issue but wasn't sure how to fix it.

Rosa
 
Rosa-

If there's no direct relationship between pledges and teams, why do you need
both sets of information in one query? What is the business problem you're
trying to solve? If you're trying to produce a report, it would seem
logical to display Companies in an outer report and list pledges and
teams/members in two separate related subreports.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top