Hi John,
The two tables are related by PolSeqNo, clientno and policyno which is supposed to make it unique. None of the fields above are primary keys in either table.
Two rows exist in each table for InvNo 46418.
Regards
Toukey
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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