Somewhat Duplicate Records

  • Thread starter Thread starter Jason Silva
  • Start date Start date
J

Jason Silva

I have a query that contains a Share (Savings Account) table and a certificate table. People who have a share account can have multiple certificates. My query lists the current share balance in it. The problem is that for every certificate that is found the share balance is obviously listed. This causes a problem on my report built from this query because the report totals every record in the share balance field, even though it only appears to be showing the first record found.

Thanks
 
I have a query that contains a Share (Savings Account) table and a certificate table. People who have a share account can have multiple certificates. My query lists the current share balance in it. The problem is that for every certificate that is found the share balance is obviously listed. This causes a problem on my report built from this query because the report totals every record in the share balance field, even though it only appears to be showing the first record found.

Thanks
Click to expand...

Please post the SQL view of your query. You may need to use a subquery
or a DSum() function call to add up the share balance, since in a
simple select query it will indeed be shown multiple times.
 
SELECT tblShares.acct, tblShares.ssn, tblShares.sname, tblShares.dateopen,
tblClubs.trlr AS [Club Trailer], tblDrafts.trlr AS [Draft Trailer],
tblLoans.trlr AS [Loan Trailer], tblCertificates.trlr AS [Certificate
Trailer]
FROM (((tblShares LEFT JOIN tblClubs ON tblShares.acct = tblClubs.acct) LEFT
JOIN tblDrafts ON tblShares.acct = tblDrafts.acct) LEFT JOIN tblLoans ON
tblShares.acct = tblLoans.acct) LEFT JOIN tblCertificates ON tblShares.acct
= tblCertificates.acct
WHERE (((tblShares.dateopen)>#10/1/2003#));


--
Jason Silva
IT Manager
BS-IT Student - UOP
certificate table. People who have a share account can have multiple
certificates. My query lists the current share balance in it. The problem
is that for every certificate that is found the share balance is obviously
listed. This causes a problem on my report built from this query because
the report totals every record in the share balance field, even though it
only appears to be showing the first record found.
 
Back
Top