A query to show unique records in a table

[nathanstevenson]

Hi!

I am trying to create a query that shows all unique records in a staging table based on "x" no of fields. This is for importing so I need this list of unique fields which is a subset of the staging table. Then I need to compare this list to the destination table and only add unmatched fields.

I am stuck on the "find unique based on x fields" query. Cause I can do a find unmatched on the unique and the destination table, which will tell me which ones to append.

Any ideas?

Nathan

 

[kraxmo]

It sounds like you need to build a dynamic SQL string at run-time based on variable column names. If you know the table and column names at run time, you could write a VBA function that would build the SQL string and create a table (similar to a make table query).

Something like...

Function MakeUnique(dest_tbl As String, src_tbl As String, ParamArray src_cols() As Variant)
    Dim intCtr As Integer
    Dim intMax As Integer
    Dim strSQL As String
    Dim strCols As String

    strSQL = "SELECT "
    strCols = ""
    intMax = UBound(src_cols)
    strCols = CStr(src_cols(0))
    For intCtr = 1 To intMax
         strCols = strCols & ", " & src_cols(intCtr)
    Next intCtr

    strSQL = "SELECT " & strCols
    strSQL = strSQL & " INTO " & dest_tbl
    strSQL = strSQL & " FROM " & src_tbl
    strSQL = strSQL & " GROUP BY " & strCols
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Function

This function will create a new table containing the unique keys. It will work if all of the parameters have values (you will need to add your own error-checking for null/empty values). After running this function, you can use the resulting table to run an unmatched query.

Note: if you need something more sophisticated, you will need to use the ADOX.Catalog.Tables(src_tbl).Keys collection to dynamically read table src_tbl's primary key values. Jim Kraxberger
Developing Access solutions since 1995

 

[kraxmo]

Sorry, forgot how to run it!!!

Type the following in the Immediate window (Ctrl-G):

? MakeUnique("newtbl", "srctbl", "key1", "key2", "keyn&quot

and press the Enter key

where:

newtbl = your new unique table (to be created)
srctbl = existing table with duplicate rows
key1 = 1st column to be copied uniquely into newtbl
key2 = 2nd column to be copied uniquely into newtbl[OPTIONAL]
keyn = same as key2 Jim Kraxberger
Developing Access solutions since 1995