 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2008How do i stop duplicate values in report
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. Hi Cuchulain, In the query that matches clients to addresses, I assume you have three tables (tblPersonalDetails, tblPersonalAddress, tblPersonalAddLink) visible in query design view? Assuming you do, then you need to make sure that field in the tbl.personaladdlink that indicates whether or not an address is 'current' is dragged down to the grid below. Then, in the criteria box below that field in the query, limit the records to just those where the the addresses ARE current (according to that field value). Since I don't even know what kind of field that is (the one that indicates whether an address is current or not), I can't even hazard a guess as to what the criteria should be. Note: If you've used this query elsewhere in your application, making this change will be problematic. You could copy the existing query, paste/rename it, and then make changes. You would then need to go into the query you are using for your report recordsource and rework it using the 'new' query with the criteria instead of the old one. HTH, CW > database design is a follows > tbl.personaldetails - stores clients details indexed on unique field ccode [quoted text clipped - 48 lines] > If someone code point out the error it would be great as i only want a > policy to appear on the report ionce. 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, > database design is a follows > tbl.personaldetails - stores clients details indexed on unique field ccode [quoted text clipped - 48 lines] > If someone code point out the error it would be great as i only want a > policy to appear on the report ionce.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.