Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Unanswered: Microsoft Access Query record duplication

    Hey guys

    I wrote a very simple query in Access, that gets information from a few tables, and I added a few fields that obtain values (mostly iif statements) based on the values in the different tables. For example, I compare policy numbers in one table with policy numbers in another table with a simple iif statement, and obtain a TRUE/FALSE value.

    Problem is, as soon as I run the query it does everything that I want it to, but keeps duplicating the records. I entered two test records and ran the query, and it keeps duplicating those records ad infinitum as I scroll down. How do I make the query only add the two records and stop?

    Here is the code (sorry for formatting):

    SELECT [Policy Data].[Policy Number], [Policy Data].[Inception Date], [Policy Data].[Expiry Date], [Policy Data].[Business Segmentation], [Policy Data].[Gross Sum Insured], [Policy Data].[Add-ons Amount], [Policy Data].[Add-ons VAT], [Policy Data].[Premium Amount], [Policy Data].[VAT Premium], [Policy Data].[VAT Commission], [Policy Data].[Commission Amount], IIf([Policy Data].[Premium Frequency]>1,"FALSE","TRUE") AS Monthly, IIf(DateAdd("m",[Policy Data].[Renewal Frequency],[Policy Data].[Inception Date])<Date(),"TRUE","FALSE") AS Renewal, IIf([Policy Data].[Exposure Country]<>"South Africa","FOREIGN","DOMESTIC") AS [Domestic/Foreign], [Policy Data].[Premium Amount]+[Policy Data].[Commission Amount] AS [Total Cost to Client], IIf([Policy Data].[Business Segmentation]=[Reinsurance QS].[Business Segmentation],"TRUE","FALSE") AS QS, IIf([Policy Data].[Policy Number]=[Reinsurance Surplus].[Policy Number],"TRUE","FALSE") AS Surplus, IIf([Reinsurance aXoL].[Business Segmentation]=[Policy Data].[Business Segmentation],"TRUE","FALSE") AS aXoL, IIf([Policy Data].[Policy Number]=[Reinsurance iXoL].[Policy Number],"TRUE","FALSE") AS iXoL, IIf([Policy Data].[Business Segmentation]=[Reinsurance CAT].[Business Segmentation],"TRUE","FALSE") AS CAT
    FROM [Policy Data], [Reinsurance aXoL], [Reinsurance CAT], [Reinsurance iXoL], [Reinsurance QS], [Reinsurance Surplus]
    WHERE ((([Policy Data].[Policy Number])<>"") AND (([Policy Data].[Business Segmentation])<>""));

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looking at that you are missing the relationships (and the JOINS) between various tables

    you need to define a join between your tables otherwise effectively you get a UNION ALL query which will return every possible combination of records
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    Sorted it out, thanks!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by James112 View Post
    Sorted it out, thanks!
    any chance you can tell others what you did in case they find a similar problem?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •