Hi John,
Just wanted to let you know that adding the insurer to the join worked.
Thanks a million
Toukey
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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