# Thread: How Not To Show A Calculated Field In Query Results

1. Registered User
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. Registered User
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. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi rogue,

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. Registered User
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

6. Registered User
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. King of Understatement
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```

8. Registered User
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.

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. King of Understatement
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?

10. Registered User
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. King of Understatement
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.

12. Registered User
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. King of Understatement
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.

14. Registered User
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. King of Understatement
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)`

#### Posting Permissions

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