Mutipule results from SQL statment

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

I have enter the following statement and the results I receive are correct
except that each result is repeated over 900 times. How do I get it to give
me just one statement and not repeat the same information over and over?
SELECT [Payment Date].Membertype, Membership.Mailingname, [Payment
Date].TotalDonation, [Payment Date].FiscalYear, [Payment Date].Account
FROM MemberType, Membership INNER JOIN [Payment Date] ON
Membership.MembershipID = [Payment Date].MembershipID
WHERE ((([Payment Date].Membertype)=4) AND (([Payment
Date].FiscalYear)="2009") AND (([Payment Date].Account)="5449"))
ORDER BY Membership.Lastname;
 
Linda

Your SQL statement has a JOIN clause. I'll take a guess that you have "over
900" payment dates per member...

"One statement" doesn't give us much to go on ... what would you expect to
see in that "one statement"?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have enter the following statement and the results I receive are correct
except that each result is repeated over 900 times. How do I get it to give
me just one statement and not repeat the same information over and over?
SELECT [Payment Date].Membertype, Membership.Mailingname, [Payment
Date].TotalDonation, [Payment Date].FiscalYear, [Payment Date].Account
FROM MemberType, Membership INNER JOIN [Payment Date] ON
Membership.MembershipID = [Payment Date].MembershipID
WHERE ((([Payment Date].Membertype)=4) AND (([Payment
Date].FiscalYear)="2009") AND (([Payment Date].Account)="5449"))
ORDER BY Membership.Lastname;
Click to expand...

The problem here is that you have a Cartesian Join - no join line - between
Member Type and Membership. The result is that our recordset will contain
every possible combination of records from the two tables; that is, if you
have nine member types and 100 members, you'll get all 900 possible
combinations.

Not knowing anything about your table structure I can't be sure, but since
you're not referencing any fields from the MemberType table, maybe just leave
it out; or if you need it to look up a human-readable value, Join it to the
Membership table.
 
Try dropping the MemberType in the FROM part of SQL statement --
SELECT [Payment Date].Membertype, Membership.Mailingname, [Payment
Date].TotalDonation, [Payment Date].FiscalYear, [Payment Date].Account
FROM Membership INNER JOIN [Payment Date] ON
Membership.MembershipID = [Payment Date].MembershipID
WHERE ((([Payment Date].Membertype)=4) AND (([Payment
Date].FiscalYear)="2009") AND (([Payment Date].Account)="5449"))
ORDER BY Membership.Lastname;
 
Back
Top