CODEWITHSUNDEEP
sqlms-accessjoin
user2478684
user2478684

Reputation: 33

Access query returning duplicate records

I have a query that I'm working with in Access that is supposed to take records specified in another query and alter them. Unfortunately, if I had multiple records selected before, it creates duplicates in this new query.

For example, if I had 2 records selected, it creates two of the same entry for each. If I had 3 selected, it creates 3 records for each, with a total of 9 records when I only wanted 3. If i had only one record initially it works perfectly.

I read that it might be a problem with the join, but I'm not sure how to fix it.

Below is my code, I hope I explained myself well enough :/

   SELECT 
      GV_transfer3.[Dept ID], 
      GV_transfer3.[Existing Account], 
      GV_transfer3.Class,     
      GV_transfer3.Fund, 
      GV_transfer3.Program, 
      GV_transfer3.Project, 
      GV_transfer3.ID, 
      GV_transfer3.[project Number], 
      GV_transfer3.[Account Number], 
      GV_transfer3.Code, 
      GV_transfer3.Date, 
      GV_transfer3.Vendor, 
      'transferred from ' & Right([GV_transfer3].[Project Number],Len([GV_transfer3].[Project Number])-8) & ' to ' & Right([New Project Number],Len([New Project Number])-8) & '; ' & [GV_transfer3].[Description] AS Description1, 
      GV_transfer3.[Req By], 
      GV_transfer3.[Approved By], 
      GV_transfer3.[Proj# Number], 
      GV_transfer3.[Transferred out], 
      GV_transfer.Action, 
      -[Amount to transfer] AS Amount, 
      0 AS Reconciled, 
      'done ' & (Date()) & '; ' & [amount to transfer] & ' from ' & Right([GV_transfer3].[Project Number],Len([GV_transfer3].[Project Number])-8) & ' to ' & Right([New Project Number],Len([New Project Number])-8) & '; ' & [GV_transfer3].[Comment] AS Comment1, 
      GV_transfer3.Transfer, 
      GV_transfer3.Match, 
      IIf((Date())<=#6/30/2010#,'FY10',IIf((Date()) Between #7/1/2010# And #6/30/2011#,'FY11',IIf((Date()) Between #7/1/2011# And #6/30/2012#,'FY12','FY13'))) AS [Fiscal Year], 
      GV_transfer3.EquipGroupID, 
      GV_transfer3.EquipNumber, 
      GV_transfer3.Rep_Maint_Purchase, 
      Null AS Budget, GV_transfer.[Rel Project], 
      GV_transfer.MEIF, 
      GV_transfer.Released, 
      GV_transfer3.Proposed, GV_transfer3.Funded, 
      GV_transfer3.Declined, 
      GV_transfer3.Indirect, 
      GV_transfer3.DIC, 
      GV_transfer3.Forecast, 
      GV_transfer3.IntFunded, 
      GV_transfer3.Invoice, 
      GV_transfer3.VContract, 
      GV_transfer3.Category, 
      GV_transfer3.Activity
    FROM GV_transfer3 
    INNER JOIN GV_transfer 
    ON GV_transfer3.ID = GV_transfer.ID;

Upvotes: 0

Views: 3217

Answers (3)

Leonard Wilson
Leonard Wilson

Reputation: 82

I'd try using a LEFT JOIN instead of an INNER JOIN.

Upvotes: 0

Yawar
Yawar

Reputation: 11607

This is just a guess--but it looks like you have duplicate rows with the same ID in either GV_transfer or GV_transfer3 or both. You will need to ensure that both tables use unique IDs. One way to check is to run the following query on the tables, one by one:

select ID, count(ID) as num_times
from GV_transfer
group by ID

... and the same query for GV_transfer3, with the table name modified.

Upvotes: 0

Francisco
Francisco

Reputation: 113

You could try using the DISTINCT command. using SQL in access. It will eliminate duplication in a column/columns. Make a new query and select the current query you made that gave you the duplication.

SELECT DISTINCT("name of column") FROM "name of table/query";

That will make a new query eliminating all duplication. Hope that helps.

Upvotes: 1

Related Questions