[AccessD] complex query!!

Pedro,

So we're talking about something like a 7-dimensional matrix here, right? Something like:

Tables A, B, C, D, E, F, G
One column in each table
Rows in each table vary, up to (for this situation) R1 through R17
So the number of results that you'll have will be, lets see...

A*B + A*C + A*D + A*E + A*F + A*G +
B*C + B*D + B*E + B*F + B*G +
C*D + C*E + C*F + C*G +
D*E + D*F + D*G +
E*F + E*G +
F*G

Looks to me as if you should create an empty Results table with the structure:

RESULTS
-------
(Key) AvgUsing, Text, 50
      AverageVal, Number, Single

Then a second table:

MATCH
-----
(Key) TblNumber, Number, Integer
      TblName, Text, 15

And fill this table with the names of your Sample tables:

1 TableA
2 TableB
3 TableC
...etc

Just make sure the numbers are sequential from 1 to 7.

Then design a form with a CommandButton (named cmdFill) and a Label (named lblTables). The OnClick method of the cmdFill button looks like this (replace the field names 'KeyFld' and 'Value' with your own field names...they ARE consistently named, I hope?):

Private Sub cmdFill_Click()
   Dim ctr1 As Integer
   Dim ctr2 As Integer
   Dim i As Integer
   Dim j As Integer
   Dim rst1 As DAO.Recordset
   Dim strSQL As String
   Dim strTblName1 As String
   Dim strTblName2 As String
   
   Set rst1 = CurrentDb.OpenRecordset("Match", dbOpenDynaset)
   rst1.MoveLast
   ctr1 = rst1.RecordCount
   For i = 1 To ctr1 - 1
      ' We will process all the tables listed in Match except for the
      ' last one, since it will have been queried against all the others in
      ' the j loop.
      rst1.FindFirst "[tblNumber]=" & i
      If rst1.NoMatch Then
         Exit For
      Else
         strTblName1 = rst1("tblName")
         ctr2 = i + 1
         rst1.FindFirst "[tblNumber]=" & ctr2
         If rst1.NoMatch Then
            Exit For
         Else
            strTblName2 = rst1("tblName")
            For j = ctr2 To ctr1
               lblTables.Caption = strTblName1 & ":" & strTblName2
               DoEvents
               strSQL = ""
               strSQL = strSQL & "INSERT INTO Results "
               strSQL = strSQL & "  ( AvgUsing, AverageVal ) "
               strSQL = strSQL & "SELECT '" & strTblName1 & "-' & [" & strTblName1 & _
                    "].[KeyFld] & ':" & strTblName2 & "-' & [" & _
                    strTblName2 & "].[KeyFld] AS AvgUsing, "
               strSQL = strSQL & "  ([" & strTblName1 & "].[Value]+[" & _
                    strTblName2 & "].[Value])/2 AS AverageVal "
               strSQL = strSQL & " FROM [" & strTblName1 & "], [" & strTblName2 & "];"
               Debug.Print strSQL
               DoCmd.RunSQL strSQL
               
               rst1.MoveNext
               If Not rst1.EOF Then
                  ' The tblNumber for this table should be equal to ctr2 + 1
                  strTblName2 = rst1("tblName")
               Else
                  ' At the end of the table and the j loop.
                  rst1.MoveFirst
               End If
            Next j
         End If
      End If
   Next i
   rst1.Close
   Set rst1 = Nothing
End Sub

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI

> ------------Original Message------------
> From: Pedro Janssen <pedro at plex.nl>
> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
> Date: Wed, Jan-12-2005 9:55 AM
> Subject: Re: [AccessD] complex query!!
> 
> Hello Steve,
> 
> yes i have 7 tables, one for each group.
> 
> - Pedro -