Toukey-
How are the two tables related? Is PolSeqNo / ClientNo / PolicyNo the Primary
Key of either table?
How many rows exist for InvNo = 46418 in both tables? I would bet there are
exactly two in each and the combination of PolSeqNo / ClientNo / PolicyNo is not
unique. With two rows in each table, you'll get the Cartesian Product when you
do a JOIN - 2 * 2 = 4 rows returned.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of toukey1
Sent: Friday, July 29, 2011 6:56 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Assistance needed with report
Hi,
I have a report based on the sql query below but unfortunately the instead of
just showing 2 lines it shows 4. The results are doubled. I checked the tables
and they only have 2 lines each which is correct. If I remove tblPolIns.InsComm
and [insprem]*[InsComm]/100 AS CommAmt from the query below, I get 2 lines
(which is what I want). The tables seem to have the correct info.
I would really appreciate it if someone can point me in the correct direction in
order to get this resolved.
SELECT DISTINCT tblAllPols.ClientNo, tblAllPols.FirstName, tblAllPols.LastName,
tblAllPols.PolicyNo, tblAllPols.InvNo, tblAllPols.Issuedate, tblAllPols.InvDate,
tblAllPols.Premium, tblAllPols.Tax, tblAllPols.Total, tblAllPols.Insurer,
tblPolIns.InsShare, tblPolIns.InsComm, [insprem]*[InsComm]/100 AS CommAmt,
tblAllPols.Currenc, tblAllPols.premium*[InsShare]/100 AS insprem,
[insprem]+[instax]-[commamt] AS InsPremium,
IIf([insprem]<>0,IIf(tblallpols.tax<>0,[tblallpols.tax]*[insshare]/100,0),tblall
pols.tax) AS instax
FROM tblAllPols INNER JOIN tblPolIns ON (tblAllPols.PolSeqNo =
tblPolIns.PolicySeqNo) AND (tblAllPols.ClientNo = tblPolIns.ClientNo) AND
(tblAllPols.PolicyNo = tblPolIns.PolicyNo)
WHERE (((tblAllPols.InvNo)=46418));
Regards
Toukey
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar