Reort to show totals extracted from Tables

[Brianwarnock]

I am glad that you have it sorted, I must say however that if you had said that you were trying to count True tick boxes at the beginning I think the first reply you got would have sorted it.
Can I also correct you in that Count(fieldname) does not count Null values, but Count(*) does.

Brian

 

[jcbhydro]

Thank you Brian for the further comments.

Originally, I was attempting to count totals for a mixture of columns, not just tick boxes. I removed most, except tick boxes, to simplify my attempts.

I don't want my count to include Null values so Count(*) doesn't seem to be appropriate. However, is there a correct procedure to count the NULL rows in a column? Would 'Where [Fieldname] is Null' in the Criteria row do the trick.

jcbhydro

 

[jdraw]

Glad you have t under control.

When in doubt with SQL and syntax, you can find samples and explanations using "w3schools sql" in a Google search.

Good luck with your project.

 

[Brianwarnock]

However, is there a correct procedure to count the NULL rows in a column? Would 'Where [Fieldname] is Null' in the Criteria row do the trick.

jcbhydro

There is sometimes more than one way to do things but the technique I showed in post 18 is frequently used to count values, in SQL it appears as
Sum(Iif(fieldname = value,1,0))

Value might be 1 or "Brian" for Null you would say is Null of course.

You can also use a more complex Iif if needed for age ranges or multiple values.

The criteria row effects all columns as it is for record selection, if you are only Selecting one column then you could use it with Count(*)

Select count(*)
From mytable
Where flda is null

I think would return a count of the Nulls in flda

Brian

 

[jcbhydro]

Re: Report to show totals extracted from Tables

Good Evening,

With the kind assistance of Forum Members I have built a Query that successfully achieves a 'count' and/or 'sum' of columns from a single Table.
My next move is to carry out similar counts of records from 2 or 3 additional tables.
However, when I add an additional table to the Design view it automatically produces a 'join' statement in the SQL code and changes the previous correct count of records to a count of records of the second table. This happens without nominating any 'count' or 'sum' functions to the total.
I wondered whether one shouldn't use multiple Tables in one Query, but I am sure that I have done so previously.
Any further suggestions would be gratefully received.

jcbhydro

 

[Brianwarnock]

Access will automatically join tables on fields of the same name, however if you delete this join you will have a Cartesian product when all records are joined to all records of each table, the purpose of adding tables to queries is to join them and this will almost certainly effect the records selected.

Brian

 

[jcbhydro]

What a disappointment!
Presumably this means that to produce a report which lists record totals from several separate tables will require a Query for each Table. I was hoping to limit the number of Queries I use.

regards,

jcbhydro