Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15

    Unanswered: Sum data across fields based on a query or table

    I have a table with almost two hundred fields in it. The first field has the date (month) and each subsequent field contains data (numeric values). I want to be able to generate a query result that is the sum of several fields that are selected based on the results of a query.

    I start out with a table that has the field names as one field and other criteria in the remaining fields. The user selects the criteria and the result is a two field table with an index and the field names representing the columns that I want to add up.

    I figure that I should be able to run a loop in VBA that sequentially grabs each field name from my first results table and runs a select query using that field name. The first time a recordset is created containing the values from the first field and each subsequent time it adds the values from the select query to that recordset until it reaches the end of the file (the field names in my first query result).

    I am able to get it to loop through the records and print the field names sequentially but am unable to get it to add the values from the recordsets together.

    The second query (the one based on the field name pulled from the first query) does not generate an error but I get a "type mismatch" error when I try to add the recordsets together.

    I tried changing the types to variant, recordset, etc. (all different combinations) and still get the same error.

    Is this even possible using this method? I could always write a seperate query for each possible combination based on the different criteria but it would be a lot neater to have it run based on dynamic (variable) parameters.

    I also tried looping through the records to generate the field names using the DLookup function but could not get the DLookup function to generate any values (got errors).

    Any suggestions would be appreciated.


    I have added my existing code in the hopes that it might better explain what I am trying to do. I know just enough VBA to get me in trouble so it is probably something very simple that I am just not aware of (I hope).


    Dim db As Database
    Dim rst As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    Dim rsHSProd As Recordset
    Dim rsTemp As Recordset
    Dim strSql As String
    Set cnn = CurrentProject.Connection

    rst.Open "GetProds", cnn
    Do Until rst.EOF


    strSql = "SELECT " & rst!Product & " FROM [CutImpConsUnits];"
    Set db = CurrentDb()
    Set rsHSProd = db.OpenRecordset(strSql, dbOpenSnapshot)



    rsTemp = rsTemp + rsHSProd



    rst.MoveNext


    Loop

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Sum data across fields based on a query or table

    Originally posted by fkolbe
    I have a table with almost two hundred fields in it. The first field has the date (month) and each subsequent field contains data (numeric values). I want to be able to generate a query result that is the sum of several fields that are selected based on the results of a query.

    I start out with a table that has the field names as one field and other criteria in the remaining fields. The user selects the criteria and the result is a two field table with an index and the field names representing the columns that I want to add up.

    I figure that I should be able to run a loop in VBA that sequentially grabs each field name from my first results table and runs a select query using that field name. The first time a recordset is created containing the values from the first field and each subsequent time it adds the values from the select query to that recordset until it reaches the end of the file (the field names in my first query result).

    I am able to get it to loop through the records and print the field names sequentially but am unable to get it to add the values from the recordsets together.

    The second query (the one based on the field name pulled from the first query) does not generate an error but I get a "type mismatch" error when I try to add the recordsets together.

    I tried changing the types to variant, recordset, etc. (all different combinations) and still get the same error.

    Is this even possible using this method? I could always write a seperate query for each possible combination based on the different criteria but it would be a lot neater to have it run based on dynamic (variable) parameters.

    I also tried looping through the records to generate the field names using the DLookup function but could not get the DLookup function to generate any values (got errors).

    Any suggestions would be appreciated.


    I have added my existing code in the hopes that it might better explain what I am trying to do. I know just enough VBA to get me in trouble so it is probably something very simple that I am just not aware of (I hope).


    Dim db As Database
    Dim rst As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    Dim rsHSProd As Recordset
    Dim rsTemp As Recordset
    Dim strSql As String
    Set cnn = CurrentProject.Connection

    rst.Open "GetProds", cnn
    Do Until rst.EOF


    strSql = "SELECT " & rst!Product & " FROM [CutImpConsUnits];"
    Set db = CurrentDb()
    Set rsHSProd = db.OpenRecordset(strSql, dbOpenSnapshot)



    rsTemp = rsTemp + rsHSProd



    rst.MoveNext


    Loop
    Once you have your table or query that contains the two fields (index and value) that you need for calculations can't you just sum the value field with a query or in code without looping and adding?

    I know this doesn't answer the code question and I may not be comprehending your intention at this point in the morning so forgive me if I'm off base.

    I'll check later to see where this goes.

    Gregg

  3. #3
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    I think that this may be of some help.

    http://support.microsoft.com/default...&Product=acc97

    David

  4. #4
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15

    Re: Sum data across fields based on a query or table

    Originally posted by basicmek
    Once you have your table or query that contains the two fields (index and value) that you need for calculations can't you just sum the value field with a query or in code without looping and adding?

    I know this doesn't answer the code question and I may not be comprehending your intention at this point in the morning so forgive me if I'm off base.

    I'll check later to see where this goes.

    Gregg

    Perhaps I didn't explain it very well.

    The original dataset is a table with over 200 fields, the first simply being the month. The data begins in January 1990 and is updated every month. I need to be able to create an aggregate based on a subset of all of the fields. Unfortunately I have many subsets (30+) so writing a set query for every subset would be pretty tedious and the fields change over time.

    The table with the two fields resulting from the first query contains an index field and a field containing all of the names of the fields that I wish to select and add up. My final result would be monthly data representing the aggregate sum for the subset.

    Sorry for any confusion and I hope that my added explanation didn't further muddy the waters.

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Sum data across fields based on a query or table

    Originally posted by fkolbe
    Perhaps I didn't explain it very well.

    The original dataset is a table with over 200 fields, the first simply being the month. The data begins in January 1990 and is updated every month. I need to be able to create an aggregate based on a subset of all of the fields. Unfortunately I have many subsets (30+) so writing a set query for every subset would be pretty tedious and the fields change over time.

    The table with the two fields resulting from the first query contains an index field and a field containing all of the names of the fields that I wish to select and add up. My final result would be monthly data representing the aggregate sum for the subset.

    Sorry for any confusion and I hope that my added explanation didn't further muddy the waters.
    Do each of your subsets have the same index value?

    Index1/value1
    Index1/value2

    Index2/value1
    Index2/value2

    etc.

    Just trying to get the picture.

    Gregg

  6. #6
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15

    Re: Sum data across fields based on a query or table

    Originally posted by basicmek
    Do each of your subsets have the same index value?

    Index1/value1
    Index1/value2

    Index2/value1
    Index2/value2

    etc.

    Just trying to get the picture.

    Gregg
    The month (Date) is the index so they are the same (They all come from the same table). The values in the data fields are all the same data type (double).

    A simple example of what I am trying to do is, say I have a large table with monthly car sales broken out by every model - each field representing a different model. I want to be able to get a monthly total (for every month) for all Mercury cars which would include Sables, Marquis, etc. I would also like to be able to do the same for all Merury SUVs, all Mercury vehicles of any type and all vehicles, cars, SUVs, or trucks made by Ford (of which Mercury would be a part). I would also like to be able to to total all sedans, coupes, etc. across different manufacturers. (My data is very similar but not cars).

    I would have a seperate table with all the model names in one field and make in another, which OEM makes it in another, whether it is a car truck or SUV in another, coupe sedan in another. The user would query this table using a form to generate a list of all models that fit their criteria. These field names would then be used in a query or looping routine to create the sum.

  7. #7
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    Originally posted by DJN
    I think that this may be of some help.

    http://support.microsoft.com/default...&Product=acc97

    David
    Thanks - I might be able to get this to work for me. I would still need to create a string with the field names dynamically. Maybe if I use the results from the first step to create the string instead of using them in a query I can get it to work. I just need to create a loop that writes the string using the

    rst.Open "GetProds", cnn
    Do Until rst.EOF

    so that it is something like:

    strSum = "(["& strSum & "[" & rst!Product & "], "

    rst.MoveNext

    Loop

    strFinalSum = "Field: FldSum: RSum(" & strSum & ")"




    Does that seem reasonable (or possible)?

  8. #8
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    15
    Originally posted by DJN
    I think that this may be of some help.

    http://support.microsoft.com/default...&Product=acc97

    David

    Thanks for the pointer on this but am having some problems getting it to work.

    OK, I was able to generate the sting to run the query but I am getting errors. At first it appears that my problem is the comma after the final field name - I will have to find a way to get rid of that but am not sure of how.

    Anyway, I copied the query generated in the module into Access to see if it will run (after deleting the final comma) and it is prompting me for a parameter value for each field. I am not sure why. If I go ahead and enter values into the paramater dialog box it generates the sum of the values I entered in each box (every record in the field will have the same value - the sum of the values I entered)

    I went ahead and created the sample table from MS's example and created the query using the RSum function and it works as described.

    I then went ahead and manipulated the query in Access so it was simply selecting the fields instead of adding them up and it still prompted me for a parameter value. It is recognizing the proper table name but not the field names. I added some fields from the same table using the GUI in the query builder and then looked at it in SQL view and the syntax is identical for the fields generated by the query builder and from my string.

    My guess is that there are some hidden characters or something in the records with the field names in them. The field names are all 10 digit numeric codes and I have the field type set as text with length of 10.

    Do you have any ideas on how to correct that?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •