Help needed writing the 'ON' expression of a query 2

[teach314]

hi to all

I need help with the following:

Let's say tblData looks like this...

[b]
ID       A   D   G   H   K   L   R [/b]
1000    34   5  22   9  12   4   4
1001    23  17   8   9  97  11   6
1002    23  17   8   9  97  11   6
1003     3  44  87  20   5   5  12
1004    34   5  22   9  12   4   4
1005 ... etc

I want a query that EXCLUDES any records that DUPLICATE ALL FIELDS to the RIGHT of the ID field. So, the
output would be...

[b]
ID       A   D   G   H   K   L   R [/b]
1000    34   5  22   9  12   4   4
1001    23  17   8   9  97  11   6
1003     3  44  87  20   5   5  12
 ... etc

Now, normally I could do this by joining the tblData to a copy of itself ON ...
tblData_1.A = tblData_2.A, tblData_1.D = tblData_2.D, tblData_1.G = tblData_2.G, ... (to field R).

Here is my problem. tblData is derived from a MAKE-TABLE query based on a CROSS TAB query.
I never know how many columns I'll have to the right of the ID column, nor do I know the
names of those fields. (for example there could be 20 fields A, B, C, E, G, ...) So I don't know how
to write the 'ON' expression.

I need the ON expression to be like...
ON each column in tblData_1 to the right of ID equals the corresponding column in tblData_2.

Hope this is stated clearly! Any help is appreciated.
Teach314

 

[lameid]

Why not just add all the columns to your query, group by everything and change ID to MIN instead of group?

 

[Andrzejek]

If you like your 'old' way of doing this, you can always get the list of all fields in your Access table this way and build your ON expression 'on-the-fly'

Have fun.

---- Andy

 

[teach314]

Thanks, lameid, for responding.

This approach (grouping on all non-ID fields) seems to have the same difficulty as the method I had suggested.

It works great on any specific table, but I would need to write a new query for each table I want to process. Because the tblData is based on a CrossTab query, I don't know in advance how many non-ID fields there will be or what their field names are. (these will vary from table to table).

Using the method you suggested, I would still need to enumerate the non-ID fields in the GROUP BY statement. (GROUP BY T.A, T.D, T.G...). But the next table I process might have to GROUP BY, for example, 20 fields like T.B, T.C, T.E, T.G, ...)

I'm hoping to find a query that will accomplish the goal (removing records that duplicate all non-ID fields) for any table.

Any hints welcome
Teach314

 

[teach314]

hi Andrzejek. My VBA coding skills are a bit rough, but this sounds like a good approach. I'll try to put some DAO code together like in your reference.

 

[lameid]

If you need a dynamic SQL you need code which I assume is what Andrzejek's link does. Should be looping through the fields of the table and concatenating a SQL string together and likely setting the SQL property of a Querydef object (although SQL can be used in other ways).

 

[PeteJohnston]

If you never know the number of fields you could try creating a function to return the field names

Public Function FieldList(TableName As String) As String

    Dim sSQL As String, sReturn As String
    Dim rst As Recordset, i As Integer
    
    sSQL = "Select * From " & TableName
    Set rst = CurrentDb.OpenRecordset(sSQL)
    With rst
        [COLOR=green]' If the field name is always ID do it this way[/color]
        For i = 0 To .Fields.Count - 1
            If .Fields(i).Name <> "ID" Then sReturn = sReturn & "," & .Fields(i).Name
        Next
        [COLOR=green]' If the field is always the first you can do it this way[/color]
        For i = 1 To .Fields.Count - 1
            sReturn = sReturn & "," & .Fields(i).Name
        Next
    End With
    Set rst = Nothing
    If Len(sReturn) > 0 Then
        sReturn = Mid(sReturn, 2)
    End If
    FieldList = sReturn
        
End Function

Then you can use that to generate an SQL Select on the fly as suggested by lameid like

Dim sSelect as String, rst as Recordset

sSelect = "Select Min(ID), " & FieldList("[i]YourTableName[/i]") & " From [i]YourTableName[/i] " & _
          "GROUP BY " & FieldList("[i]YourTableName[/i]")
Set rst = CurrentDb.Openrecordset(sSelect)
.....
[i]Do your stuff[/i]

If you need it to be a query you could always use

CurrentDb.CreateQueryDef "qryYourQueryName", sSelect

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[teach314]

Thanks to lameid, Andrzejek and PeteJohnston for taking the time to come up with a nice solution.