Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    Trying to do query with running sum on length field in query, getting #Error

    TotalLength: DSum("Length","zTableFields","TableName<=" & <table> & "")

    See attached db zqry_Table_List

    What am I doing wrong?

    Thanks, John
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    You forgot to put single quotes around the concatenated table name. Try this:

    <pre>TotalLength: DSum("Length","zTableFields","TableName<='" & <!t>[Table] & "'")</pre>

    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    Looking at db you attd, shouldn't the DSum Where criteria be the same as query's?? I think it should read like this:

    <pre>TotalLength: DSum("Length","zTableFields","TableName<='" & <!t>[Table]<!/t> & "'
    AND (TypeObject='T') AND (LocalRemote='R')")</pre>


    Also Length is defined as Text field, the DSum still worked, but shouldn't Length be a number?

    HTH

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    Hi Charlotte & Mark

    Your were both right

    While trying to figure this out on my own I came up with zqry_Table_List_TR_SQL that uses sql rather than dsum

    Howwever I get different results starting on the 11th record of each query

    Query zqry_Table_List_TR_SQL, 11th record tbl_BP_OtherLookup, running sum 1896s 225

    verse

    Query zqry_Table_List_TR_D, 11th record tbl_BP_OtherLookup, running sums 1896 224

    See attached, why are results different?

    John
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    The query with the DCount is the one producing bogus results. To get correct results you need to modify the TotalFields calculated field, replace "Length" as first item in DCount with an asterisk (similar to the SQL version which uses Select Count(*):

    <pre>TotalFields: DCount("*","zTableFields","TableName<='" & <!t>[Table]<!/t> & "'
    AND (TypeObject='T') AND (LocalRemote='R')")</pre>


    This produced same results as the SQL query (I exported results of both queries to an Excel spreadsheet to verify which was producing correct results). Also, SQL version is a lot quicker than the DSum/DCount query so I'd recommend using that one.

    HTH

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    Hi Mark

    Great stuff?

    Thanks for your help

    John

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    BTW that's a good trick for getting a running sum in a query, instead of having to create a report with running sum textboxes, etc. I may have seen it before but had forgotten how it works....

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    Hi Mark

    I am now trying to generate a consecutive number for each query output row with following expression, however its giving me the count of fields + 1 for each row

    RowNum: (Select Count(*) FROM [zTableFields] as Temp WHERE (((Temp.[TypeObject] = "T") and (Temp.[LocalRemote] = "R")) and (Temp.<tableName> < [zTableFields].<tableName>)))+1

    See zqry_Table_List_TR_SQL

    What am I doing wrong?

    John
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    I looked at this & tried various permutations but none of them produced desired results. I think someone asked similar question a while back but cannot locate that post at this time....

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    <P ID="edit" class=small>(Edited by MarkD on 06-Jan-03 11:41. Additional comment.)</P>The problem is you are counting all the records in table where TableName is <= TableName in query, but the query is a totals (group by) query so TableName is listed only once (44 records). Table has 1585 records that meet the query criteria.

    Here is one way to do this, though not very efficient. Create another group by query with same WHERE criteria. SQL looks like this:

    SELECT zTableFields.TableName
    FROM zTableFields
    WHERE (((zTableFields.TypeObject)="T") AND ((zTableFields.LocalRemote)="R"))
    GROUP BY zTableFields.TableName;

    In original query, use DCount referencing 2nd query. Example:

    <pre>RCount: DCount("*","Zqry1","<!t>[TableName]<!/t><='" & [TBLNAME] & "'")</pre>

    "Zqry1" is name of 2nd query. "TBLNAME" is alias for TableName in original query. Note: To be able to use alias I had to modify zTableFields table to get rid of all those captions for the fields. If you assign a caption to a field in Table Design, you cannot use an alias for that field in a query (which I do not find very useful). I'd recommend getting rid of the table captions & use aliases in queries using "AS" keyword as necessary.

    Note that the DCount used here really slowed down the query. Maybe someone knows a better (more efficient) way to do this. I tried using SQL statement in place of DCount but could not get syntax that produced desired results.

    PS: Hint: Using GROUP BY with single field can be faster than SELECT DISTINCT when you need to repeatedly return a list of unique records from a table.

    HTH

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)

    Hi Mark

    You solved my problem.

    This is just a sys admin query so I can quickly see any new fields or field size table changes I have been making in devl db vrs customers prod db so I can make adj to cust prod db

    Maybe someone else can get it done in one query.

    Thanks for your persistence

    John

Posting Permissions

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