Toukey-
If the Insurer is what makes each row unique, then you need to add that to your
JOIN.
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 4:19 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Assistance needed with report
John,
The two rows is a result of the policy having 2 different insurers (which is
possible) so in fact are the same except for the insurer.
Toukey
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar