Query to define which fields should be used in a calculation

[geekette]

Hi,

I have a table with three columns, 1) Team Name 2) AUX Code 3)True/False. I have set up a query that filters the list by team name and if column 3 = true. The only column viewable when run is the Aux Code.

Is it possible to use that filtered Aux Code list in another query to define which fields to sum together, there fore keeping the calucation flexible dependent on the selection made in the first query?

 

[SydneyGeek]

If I understand correctly, create the next query and add your first query to the grid. Join the first query to its table on the AUX field, and you will have the records filtered and ready to analyse.

Denis

 

[geekette]

Hi Denis - that won't work.

The list that the first query produces identifies which Fields need to be included in the calculation. So if I pull through this query into the second, how would i identify the fields, without selecting manually?

 

[CT Witter]

Can you post some examples of your first query, the results of the first query, the second query and the results of the second query?

CT

 

[geekette]

Query 1 SQL:

SELECT AuxCodes.[Aux Code]
FROM AuxCodes
WHERE (((AuxCodes.[Team Name])="Team A") AND ((AuxCodes.[Productive?])=Yes));

The results of this query are the following list:

Aux Code
Break
Manager
Training

Query 2 SQL:

UPDATE Agent_Master SET Agent_Master.[Team A Prod Aux] = [Agent_Master]![Break]+[Agent_Master]![Manager]+[Agent_Master]![Training];

The table linked to Query 1 has been set up so that an adminstrator can update new AuxCodes and check a box to indicate whether or not they are productive. So when Query 1 is run it reflects any updates/new codes for a team.

I then want the list that Query 1 produces to be used as the formula in Query 2 (the second part of the SQL) i.e it indicates which fields need to be included in the sum.

 

[CT Witter]

You can try using something like the code below, Place the following code in a new module.

Function CreateQuery(strSrcName As String, strQryName As String)
'************************************************************
' (c) 2007 CT WITTER                                        *
' All rights reserved.                                      *
'                                                           *
' You may only use this code as part of an application      *
' that requires its use. You must including this            *
' notice intact. You may not distribute the code            *
' as your own work, nor can you distribute the              *
' code on its own.                                          *
'************************************************************
Dim catDB   As ADOX.Catalog
Dim cmd     As ADODB.Command
Dim rst     As ADODB.Recordset
Dim strSQL  As String
   
'Set up and Open the Source Data
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic

rst.Open "Select * from " & strSrcName


'Beginning SQL String
strSQL = "UPDATE Agent_Master SET Agent_Master.[Team A Prod Aux] = "

Do Until rst.EOF
    'Build query string
    strSQL = strSQL & "[Agent_Master]![" & rst.Fields(0) & "]+"
    rst.MoveNext
   Loop

rst.Close

'Clean up SQL
'Remove Last "+" and add the ";"
strSQL = Left(strSQL, Len(strSQL) - 1) & ";"

    'Create the Destination Query
   Set catDB = New ADOX.Catalog
      ' Open the Catalog object.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & "\" & CurrentProject.Name

   Set cmd = New ADODB.Command
   ' Define SQL statement for query and set provider-specific
   ' properties for query type and ODBC connection string.
   With cmd
      .ActiveConnection = catDB.ActiveConnection
      .CommandText = strSQL
   End With
   
   'Test to See if Destination Query Exists, Delete if necessary
   If ExistsQuery(strQryName) Then catDB.Procedures.Delete strQryName
   
   ' Name and save query to Procedures collection.
   catDB.Procedures.Append strQryName, cmd
   Application.RefreshDatabaseWindow

   Set rst = Nothing
   Set catDB = Nothing

End Function

Function ExistsQuery(strQueryName As String)
'************************************************************
' (c) 2007 CT WITTER                                        *
' All rights reserved.                                      *
'                                                           *
' You may only use this code as part of an application      *
' that requires its use. You must including this            *
' notice intact. You may not distribute the code            *
' as your own work, nor can you distribute the              *
' code on its own.                                          *
'************************************************************
    Dim strTemp As String
    
    On Error Resume Next
    strTemp = CurrentDb.QueryDefs(strQueryName).Name
    
    If Err.Number = 0 Then
       ExistsQuery = True
    Else
       ExistsQuery = False
    End If
End Function

You can then run like CreateQuery("qryAuxCode","qryTest")

You must reference Microsoft ActiveX Data Objects and Microsoft ADO Ext. X.X for DDL and Security

Make sure to refresh the database windo (F5) before viewing the newly created query.

HTH,
CT

 

[geekette]

Thanks I will try that....