Query to define which fields should be used in a calculation

geekette

Board Regular
Joined
Sep 12, 2006
Messages
120
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
Sep 12, 2006
Messages
120
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

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
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

Board Regular
Joined
Sep 12, 2006
Messages
120
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

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
You can try using something like the code below, Place the following code in a new module.
Code:
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
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top