RE: How do i stop duplicate values in report
- From: Ken Sheridan <KenSheridan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 12 May 2008 10:03:00 -0700
I'd recommend using a main report for the client's personal details with two
subreports, one for addresses and one for policies. In each case the
LinkMasterFields and LinkChildFields properties of the subreports will be the
ccode fields.
Only the main reports underlying query needs a parameter to restrict the
output to the selected client, so you'll only be prompted once for the Client
Ref. The subreports will be automatically restricted to the addresses and
policies for the selected client.
The RecordSource for the main report would be a query on the personaldetails
table; that for the addresses subreport a query on the personaladdlink and
personaladdress tables joined on ID; and that for the policies subreport a
query on the personallink and personalpolicy tables joined on ID.
Ken Sheridan
Stafford,
"Cuchulain64" wrote:
database design is a follows
tbl.personaldetails - stores clients details indexed on unique field ccode
tbl.personaladdress - stores addresses each address has unique field ID
tbl.personaladdlink - stores ID and Ccode and whether current or previous
address
tbl.personalpolicy - stores details of clients policies indexed on unique
field ID
tbl.personallink - stores ID from personal policy and CCode from
personaldetails to link policy to client
as more than one person can live at an address and more than one person can
own a policy some relationships are one to many.
I have created 2 separate queries one lists all policies for a client,
another current address for a client, and a report tries to combine these.
however if a client has previous and current address all policies appear
twice or three times if 3 addresses are listed. also the queries run by
asking the user to input ccode when i run the report it asks this twice can
i take one ccode and pass it through the various queries until the report is
output?
the report is created from the query client policies& address by ref code
listed below
SELECT [Policies for individual client by ref].ccode, [Client By Ref Current
Address].title, [Client By Ref Current Address].Forname, [Client By Ref
Current Address].Surname, [Client By Ref Current Address].address1, [Client
By Ref Current Address].address2, [Client By Ref Current Address].address3,
[Client By Ref Current Address].address4, [Client By Ref Current
Address].address5, [Client By Ref Current Address].postcode, [Client By Ref
Current Address].tele, [Policies for individual client by ref].contract,
[Policies for individual client by ref].conttype, [Policies for individual
client by ref].polstatus, [Policies for individual client by ref].[Net
Premium], [Policies for individual client by ref].[Life Cover Amt],
[Policies for individual client by ref].value, [Client By Ref Current
Address].tele2, [Client By Ref Current Address].tele3, [Policies for
individual client by ref].matdate, [Policies for individual client by
ref].onrisk, [Policies for individual client by ref].accepdate, [Policies
for individual client by ref].propdate, [Policies for individual client by
ref].company, [Policies for individual client by ref].[CI Cover Amt],
[Policies for individual client by ref].[PHI Benefit], [Policies for
individual client by ref].[Deferred Period (WKS)], [Policies for individual
client by ref].[Gross Premium], [Policies for individual client by
ref].lifeass, [Policies for individual client by ref].val_date
FROM [Client By Ref Current Address] RIGHT JOIN [Policies for individual
client by ref] ON [Client By Ref Current Address].ccode = [Policies for
individual client by ref].ccode
WHERE ((([Policies for individual client by ref].ccode)=[enter Client Ref]))
ORDER BY [Policies for individual client by ref].conttype, [Policies for
individual client by ref].polstatus;
If someone code point out the error it would be great as i only want a
policy to appear on the report ionce.
.
- References:
- How do i stop duplicate values in report
- From: Cuchulain64
- How do i stop duplicate values in report
- Prev by Date: Re: Copy data from one column to another
- Next by Date: Cartier Santos Demoiselle 18kt Yellow Gold and Steel Midsize Watch W25067Z6
- Previous by thread: RE: How do i stop duplicate values in report
- Next by thread: Copy data from one column to another
- Index(es):
Relevant Pages
|
