Output all fields in a query returns an error

Banana

split with a cherry atop.
Local time
Today, 00:53
Joined
Sep 1, 2005
Messages
6,318
I looked thru the forums and didn't seems to get what I needed;

I want a count query to give me all fields in "group by" column, and assign 0 to where there are no count for certain fields.

I tried to set Outputallfield to "yes" but got an error message saying that there is a data type mismatch. The query ran just fine when outputallfield was set "no" and gave right results.

Can anyone help, please?
 
Setting Outputallfield to "yes" is equivalent to Select *. You can use it in a Select query, but not in a query with Group By (i.e. a Totals query.)

You can build a second query to link the Totals query with the table to select all the fields from the table.
.
 
Last edited:
Just so I understand what you mean by linking, I'd build a new select query with the total query and the table that the total query referenced to, and make sure the latter will output all fields?

Thanks so much!
 
In query design in the new select query, you can link the Totals query with the table by dragging each groupped-by field in the Totals query to the corresponding field in the table to draw a linking line.

When all the linking lines between the fields are drawn, you can double-click the * in the table and the Count field in the Totals query. This will output all the fields from the table as well as the Count from the Totals query.
.
 
Last edited:
Hmm, I did what you said, and didn't get the result. Here's the screenshots of the query and the result.

(the total records are 9 records in this test).

What did I do wrong?

Thanks.
 

Attachments

  • screenshot1.JPG
    screenshot1.JPG
    92.8 KB · Views: 143
  • screenshot2.JPG
    screenshot2.JPG
    86.5 KB · Views: 139
I might not have understood your original question correctly. Could you please attach a sample database with the 9 records (with any sensitive data removed or changed) and your original count query, and tell us how you want the final query results to be displayed.
.
 
Last edited:
Here's my stripped-down database.

The queries are for a report, (it's the only one in the database, named Test).

In its present form, it displays all counts of relevant info correctly and nicely. (see my other thread about making this particular report for more info- http://www.access-programmers.co.uk/forums/showthread.php?t=97175

However my uppers want me to make a reports that lists all fields so the format will be always same month after month, hence me needing to output all fields for the report.

My stripping down database made the report return some errors, but seems to works anyway. I hope that everything makes sense now.

Thanks again.
 

Attachments

What you need is add a complete list in each count query, joining the list and the counts with an outer join.

See the VisitCount query in the database. It will return 0 for the Email visit type. You can double-click on the joining line in query Design View to see how the join was set up.
.
 

Attachments

Last edited:
It works like charm for the most of queries! Thanks!

Now there's just one more thing. There's one queries that's similar to TypeVisit you did, but need to add criteria so TypeVisit counts only and only if Information and Referral is checked.

The query will only return where there is actually counts, regardless whether I have a group-by list.

How do I fix that one?

And for queries where it's simply a count of single variable (how many of a X), how do I get it to return a 0 instead of blank field?

Edit= Here's the sql to the first problem. Currently this query returns only values where IR is true, but does not give 0 for the rest.

SELECT TypeVisit.[Type of visit], Count(Client.[Type of visit]) AS [CountOfType of visit1]
FROM TypeVisit LEFT JOIN Client ON TypeVisit.[Type of Visit] = Client.[Type of visit]
WHERE (((Client.[Information & Referral])=True))
GROUP BY TypeVisit.[Type of visit];
 
Last edited:
To resolve the first problem, you can use a series of two queries. Put the criteria in the first query and use Nz(...)+0 in the second query to convert blank values to zeros. Run the second query.

Query "IRVisitCountA":-
SELECT [Type of visit], Count([Type of visit]) AS IRVisit
FROM Client
WHERE Client.[Information & Referral]=True
GROUP BY Client.[Type of visit];

Query "IRVisitCountB":-
SELECT TypeVisit.[Type Of Visit], Nz([IRVisitCountA].[IRVisit])+0 AS [CountOf IR Visit]
FROM TypeVisit LEFT JOIN [IRVisitCountA] ON TypeVisit.[Type Of Visit] = [IRVisitCountA].[Type of visit]
ORDER BY TypeVisit.[Type Of Visit];


Incidentally, I think, Nz(...)+0 is also a solution to the other problem that you described.
.
 
The IR Visit works now great.

I couldn't fix the second problem though. I'm just not sure where to insert the Nz().

I tried to have the list box query the single variable queries and inserted in fieldname as thus:

NewFieldName:IIf(IsNull([FieldName]),0,[FieldName])

as suggested somewhere on this forums.

Nz([FieldName],0) either returns a error in syntax or returns nothing at all. I'm afraid I just don't understand how to use Nz properly in SQL.
 
There is a subtle difference between Nz([FieldName],0) and Nz([FieldName])+0.

The former returns a character string whereas the latter returns a numeric zero. But they will not return an error.

Does the query return an error when you remove the Nz(...,0)?


EDITED: corrected a typo - character
 
Last edited:
Gotcha.

I will try that.

Without Nz() at all, the query returns nothing at all. And to be sure you got that, I'm querying from the report's list box's recordsource.
 
I tried it against with this in fieldname for the query of report with only the TACount query-

Expr1: Nz([CountOfTechnical Assistance])+0

and that returned nothing at all. At that point, CountofTechnical Assistance is currently null, as there's no checks for that field.
 
I'm about to tackle the Access once again. I hope someone has an answer for the above question?

Thanks!

Banana
 

Users who are viewing this thread

Back
Top Bottom