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
>
Tidak ada komentar:
Posting Komentar