subreport duplicate data

[jnh127]

In a subreport, I have a column of data on left from a table, and a column of data from different field and table on right. If there is 6 entries of data on left and 2 on right then data on right will print duplicates until there are 6 entries. The 6 and 2 entries can be any amount and the same will happen. Also it will do the same if there are 2 on left and 6 on right.
data left 1 data right 1
data left 2 data right 2
data left 3 data right 1 duplicate
data left 4 data right 2 duplicate
data left 5 data right 1 duplicate
data left 6 data right 2 duplicate

Please help. Last thing to finish DB.

 

[jnh127]

dataleft1 dataright1
dataleft2 dataright2
dataleft3 dataright1 duplicate
dataleft4 dataright2 duplicate
dataleft5 dataright1 duplicate
dataleft6 dataright2 duplicate

There should only be 2 entries on the right. The last four are duplicates. For some reason either side prints enough duplicates to match the other side. On the report design it is written as..

dataleft dataright

but there are 6 entries in the field on the left and 2 in the field on the right. Sorry if I'm not explaining this very well.

 

[jnh127]

This is the sql code for the recordsource.

SELECT tblmember.[Member #], tblmember.Name, tblmember.Address, tblmember.City, tblmember.State, tblmember.[Zip Code], tblmember.[Vol Contribution], tblmember.[Monthly Dues], tblmember.[Cart Stall Rental], tblmember.[Handicap Fees], tblmember.[Locker Rental], tblmember.[Rest & Bar Credit], tblmember.[Vol Contribution], tblmember.Current, tblmember.[1 Months], tblmember.[2 Months], tblmember.[3+ Months], tblmember.[Unposted Charges], tblmember.[Unposted Payments], tblmember.UnpostedBRCharges, tblmember.UnpostedPSCharges, tblmember.Balance1, tblmember.Balance2, tblmember.Dues, tblmember.NewBalance, Charges.[Ticket Date] AS [Charges_Ticket Date], Charges.[Ticket #] AS [Charges_Ticket #], Charges.Gratuity, Charges.[Liquor Sales], Charges.[Food Sales], Charges.[Charges from another Club], Charges.[Sales Tax] AS [Charges_Sales Tax], ProShop.[Ticket Date] AS [ProShop_Ticket Date], ProShop.[Ticket #] AS [ProShop_Ticket #], ProShop.[Cart Rentals], ProShop.[Green Fees], ProShop.Beer, ProShop.[Food & Drink], ProShop.[Range Balls], ProShop.Merchandise, ProShop.[Sales Tax] AS [ProShop_Sales Tax], tblmember.[Vol Contribution]
FROM (tblmember LEFT JOIN Charges ON tblmember.[Member #] = Charges.[Member #]) LEFT JOIN ProShop ON tblmember.[Member #] = ProShop.[Member #];

 

[jnh127]

This is a sample of the report. The spacing was changed when I posted it, but I think you can decypher it.

Statement Statement Date - 4/6/2005
Member # 1 Silsbee Country Club, Inc.
P.O.Box 982
a Silsbee,Tx 77656
abcd
Silsbee TX 77656 Balance Brought $280.00
NOTICE - Donations to this Payments Applied $0.00
Club are not deductible Balance $280.00
charitable contributions for Dues: $95.00
Federal Income Tax purposes.
Rest.&&Bar $0.00
Rest.&&Bar $10.00
ProShop $0.00
Voluntary Debt Retirement Donation $0.00
New Balance - Please Pay This $375.00
Tkt Date Ticket # Amount Tkt Date Ticket # Amount
04/05/05 $80.00 04/05/05 $72.00
04/05/05 $80.00 04/05/05 $90.00
04/05/05 $55.00 04/05/05 $72.00
04/05/05 $55.00 04/05/05 $90.00

 

[jnh127]

This is the best I could do on table data.I deleted all the data that was null or zero to save space in the post.

tblmember
Member # Name Address City State Zip Code Monthly Dues Rest & Bar Credit Current 1 Months Balance1 Balance2 Dues NewBalance
1 a abcd Silsbee TX 77656 $85.00 $10.00 $95.00 $185.00 $280.00 $280.00 $95.00 $375.00
2 b abcd Silsbee TX 77656 $85.00 $10.00 $95.00 $285.00 $380.00 $380.00 $95.00 $475.00
Charges
Ticket Date Member # Gratuity Liquor Sales Food Sales
04/05/05 1 $55.00 $0.00 $0.00
04/05/05 1 $0.00 $0.00 $80.00
04/05/05 2 $0.00 $0.00 $75.00
04/05/05 2 $0.00 $100.00 $0.00
ProShop
Ticket Date Member # Green Fees Food & Drink Range Balls
04/05/05 1 $90.00 $0.00 $0.00
04/05/05 1 $0.00 $0.00 $72.00
04/05/05 2 $0.00 $35.00 $0.00
04/05/05 2 $0.00 $0.00 $120.00