We help IT Professionals succeed at work.

Append unique records into tables with identical structure.

MCaliebe
MCaliebe asked
on
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?

Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Commented:
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

Author

Commented:
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

Commented:
Write a select query on tbl1, include all fields, add tb2 to the query add all fields to the query.  Link each field in tbl1 to the corresponding field in tbl2.  The relationship should be all records from tb1 and those in tbl2 that match.  Filter for null values in all of the tbl2 fileds and you will be left with records from tbl1 that have no presence in tbl2.  Then, change it to an append query and append the appropriate tbl1 fields to the corresponding tbl2 fields.

Commented:
This works. Screen shot of query

Author

Commented:
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.

Commented:
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 FyeOwner, Dev-Soln LLC
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.