Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query to add fields (ACCESS 2000)

    I am trying to use a query to add fields in a table. But the query simply returns blanks. How do I make the query give me totals for each row, treating null values as zeros.

    The sql syntax of the query I am using is as follows:

    SELECT [Irrigation on landholding].[Household number], [Irrigation on landholding].Village, [Irrigation on landholding].[Location of land], [Irrigation on landholding].STW+[Ponds]+[Canal]+[RLI]+[DTW] AS Total
    FROM [Irrigation on landholding]
    GROUP BY [Irrigation on landholding].[Household number], [Irrigation on landholding].Village, [Irrigation on landholding].[Location of land], [Irrigation on landholding].STW+[Ponds]+[Canal]+[RLI]+[DTW];

    The table I use is being attached as an excel file. It will be possible to import this file into access and run the above query to see the problem I face.

    I am not attaching the ACCESS database with the post because the forum does not allow it.

    Vikas

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: query to add fields (ACCESS 2000)

    Unfortunately your Excel file didn't get attached - attaching has to be the last thing you do before you post. If you preview, it wipes out the attachment.

    However you can attach a small Access database if you Zip it - the limit on file size is 100K, and you may want to compact and repair before you ZIP it to get maximum shrink.

    As to getting empty values from your Group By query, you can use the Nz function to replace Null fields with zero, and then do a sum on given fields. However I suspect you are trying to add the value of several fields in each record to get a total for each record. if that's the case you can create an expression in the query to add the fields together. Hope this helps, and welcome to the Access forum in Woody's Lounge.
    Wendell

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query to add fields (ACCESS 2000)

    I'm so sorry!! Here is the ACCESS database as a zipped file. I am indeed trying to add several fields in each record to get a total for each record. I did write the expression, but it simply gives me blanks.
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: query to add fields (ACCESS 2000)

    Wendell is quite about the NZ function, here is your query:

    SELECT [Irrigation on landholding].[Household number], [Irrigation on landholding].Village, [Irrigation on landholding].[Location of land], Sum(nz(STW,0)+nz([Ponds],0)+nz([Canal],0)+nz([RLI],0)+nz([DTW],0)) AS Total
    FROM [Irrigation on landholding]
    GROUP BY [Irrigation on landholding].[Household number], [Irrigation on landholding].Village, [Irrigation on landholding].[Location of land];

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Lounger
    Join Date
    Mar 2003
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query to add fields (ACCESS 2000)

    Perfect. Thanks so much!!

Posting Permissions

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