[AccessD] complex query!!

Hi Steve,

thanks for this code.
Its will save me a lot of time.
Time that i can spent extra on the interpertation of the data
that will be provided with the help of your code.

Pedro Janssen


----- Original Message -----
From: "Steven W. Erbach" <serbach at new.rr.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Thursday, January 13, 2005 12:09 AM
Subject: Re: [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 -
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>