Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511

    Unanswered: How Not To Show A Calculated Field In Query Results

    Hi all,

    Should be a simple one but I can't seem to find the answer by searching the forum/google.

    I want to calculate the average of five fields and return the average in a query, so I have set up my query as follows based on a thread I found on google:
    Code:
    SELECT Data1,Data2,Data3,Data4,Data5, 
        (Data1+Data2+Data3+Data4+Data5) as Total, 
        (IIf(Data1=0,0,1)+IIf(Data2=0,0,1)+IIf(Data3=0,0,1)+IIf(Data4=0,0,1)+IIf(Data5=0,0,1))  As Avg, 
        IIF (Avg=0,0,Total/Avg) as Average) 
    FROM tablename
    The Total and Avg calculated fields are only intermediary fields to make the query easier to read. How do I not display them in the query results? I only want to display Data1,Data2,Data3,Data4,Data5 and Average. I'm sure this is a simple flag somewhere but I can't seem to find it!

    Thanks,

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    In the query grid, there is a row labeled 'Show' with a checkbox.....uncheck the box.

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi rogue,

    Thanks for the quick reply.

    I should have mentioned in my first post that I'd tried that, but when I untick them and run the query I then get prompted to input values for the two calculated fields which are unticked?

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    It sounds like if you're going to use them in the calculation, they have to be visible. You could use (Data1+Data2+Data3+Data4+Data5) /((IIf(Data1=0,0,1)+IIf(Data2=0,0,1)+IIf(Data3=0,0, 1)+IIf(Data4=0,0,1)+IIf(Data5=0,0,1))) AS Average rather than creating two fields that you don't seem to actually need. As a side note...I'm not sure how your data populated, but you may want to make use of the NZ function to ensure proper handling of any null values.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless you are using the raw query to display data then you are stuffed
    just don't use the column in a form or report
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Thanks peeps. At least now that I know that it can't be done directly, I can work around it.

    Cheers
    Last edited by Colin Legg; 09-28-10 at 05:28.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT Data1,Data2,Data3,Data4,Data5, Average
    FROM (SELECT Data1,Data2,Data3,Data4,Data5,      (Data1+Data2+Data3+Data4+Data5) as Total,      (IIf(Data1=0,0,1)+IIf(Data2=0,0,1)+IIf(Data3=0,0,1)+IIf(Data4=0,0,1)+IIf(Data5=0,0,1))  As Avg,      IIF (Avg=0,0,Total/Avg) as Average)  FROM tablename) AS derived_table
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    I've been itching to try this out but until now I haven't had time. Using Pootle's suggestion and incorporating NZ() to handle null values works like a charm in Access, so thank you both.

    Follow up question.

    If I call this query from Excel using ADO, the derivedtable fields are not being returned - only the fields from the tablename table are. Do I need to check my code or is this not possible to do via ADO?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmm, ah dinnaw.

    Is the SQL inline in Excel or saved as a query in Access? TBH I don't understand why it would not return the same via ADO as in Access. Can you post the code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    I'm calling the query which is saved in Access. I think that if I were to build the query inline within Excel VBA then I'd have to re-write it because the NZ() function wouldn't be recognised. There's a fair bit of code - I have a class wrapper to do all the fancy ADO stuff - so, given that it's theoretically possible to return a recordset from the derived table, I'll try to do some debugging myself before taking up too much more of your valuable time. If I get really stuck then I'll post back!

    Thanks Pootle.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Calling a stored query containing a derived table with renamed columns via ADO works fine for me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Been working on this a bit more... if you include the NZ() function in the saved query, do you get an undefined function VBA runtime error?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not tried. NZ() is only shorthand for IIF() though so you could just replace them.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Thanks, that's resolved it.
    I replaced expressions like this:
    Code:
    NZ(Data1,0)+NZ(Data2,0)
    with
    Code:
    IIF(ISNULL(Data1),0,Data1)+IIF(ISNULL(Data2),0,Data2)
    and it's running fine now.

    Thanks again!


    PS. Next time you're in London give me a shout and I'll buy you a beer.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Minor, but this is more efficient:
    Code:
    IIF(Data1 IS NULL,0,Data1)+IIF(Data2 IS NULL,0,Data2)
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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