Toukey-
Why do you have two invoices for one policy number? If you have more than one
row per PolSeqNo, ClientNo, PolicyNo, then that's not unique. It's exactly that
2 - 2 relationship that's giving you the 4 rows. I gather the two rows in each
table have different data in them other than the matching key values.
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 3:52 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Assistance needed with report
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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar