asked on

Append unique records into tables with identical structure.

Hey everyone,

I believe this is a simple quetion, but I can't wrap my head around it.

I have two tables with identical structure. TBL1 and TBL2...Each table has 4 fields. . I want to use an append query to insert the contents of TB2 into TB1, however I only want to insert those records that are unique based on all 4 fields. The fields wil all have duplicate data, however the combination of all 4 fileds makes them unique.

I know I can combine all four fiields into a unique "key", however I think there is a means of inserting only distinct rows...however I can't seem to construct such a query in the MS Access query builder.

Thoughts?

Dale Fye

INSERT INTO tbl1 (Field1, Field2, Field3, Field4)
SELECT DISTINCT Field1, Field2, Field3, Field4
FROM tbl2

If any of these fields are memo fields, there will be issues.

upsfa

Write a query on TB2 that contains all the fields, group the query, then change it to an append query and append the records into TB1

MCaliebe

ASKER

I think I may have not been clear. It looks like these solutions are picking the unique records from within TBL1 and inserting only those into TBL2.

What I want is to Select all records from TBL1 and insert only those records into TBL2 that do not have matching records in TBL2 on all 4 fields

So, for instance, if I run the APPEND query attached, it wil load all the records into the receiving table, and if I fire the same query a second time, it will load the same records again creating duplicates.

INSERT INTO [Copy Of Comb_FC_Data] ( PartNo, [Date], ForecastValue, Forecast )
SELECT Distinct JLGDATA_6_20_2011.PartNo, JLGDATA_6_20_2011.Date, JLGDATA_6_20_2011.ForecastValue, JLGDATA_6_20_2011.Forecast
FROM JLGDATA_6_20_2011;

Open in new window

ASKER CERTIFIED SOLUTION

upsfa

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

upsfa

This works.

MCaliebe

ASKER

This did work, however I thought there was something that could be done selecting distinct records rather then values which might eliminate some of the overhead in the code.

upsfa

Just write a query on tbl1, using all the fields, grouping the query so you only have one record in the resulting query for each combination, save that query and use it inplace of tb1 in the query above.

Michael

Dale Fye

Or you could have just used the first response, and replaced the fields listed in that query with the fields in your table (which you didn't provide)!

Using a Group By clause in your SELECT statement will produce the same results as using a DISTINCT clause.

INSERT INTO tbl1 (Field1, Field2, Field3, Field4)
SELECT DISTINCT Field1, Field2, Field3, Field4
FROM tbl2

is essentially the same as

INSERT INTO tbl1 (Field1, Field2, Field3, Field4)
SELECT Field1, Field2, Field3, Field4
FROM tbl2
GROUP BY Field1, Field2, Field3, Field4