Reort to show totals extracted from Tables (1 Viewer)

jcbhydro · Oct 9, 2014

Good Evening,

I routinely produce a report for regular Committee Meetings which includes statistical date derived from the total rows for various fields in 1 or more Tables.
It should be possible to extract such data automatically, probably using a query. So far, my endeavors in this direction have been unsuccessful. Any suggestions would be gratefully received.

regards,

jcbhydro

Mihail · Oct 9, 2014

jcbhydro said:
It should be possible to extract such data automatically, probably using a query. So far, my endeavors in this direction have been unsuccessful. Any suggestions would be gratefully received.
regards

Indeed, should be possible. But, without any information about your DB, any suggestion seems to be impossible.

jdraw · Oct 9, 2014

As Mihail says, we need more info. A copy of your database without any confidential info would be helpful. Also, could you please tell readers in plain English what exactly you are trying to total?

jcbhydro · Oct 13, 2014

Neither of these responses are in any way helpful!

The structure or contents of the database are irrelevant to the question.

My query, in perfectly plain English, requested advice in extracting totals data from a Table, probably using a Query. The totals data is a standard feature available for all Access Database. I merely wish to extract this data using a query for use in producing a Report.

jcbhydro

jcbhydro · Oct 17, 2014

Good Afternoon,

In continuation of my earlier question, I have built a Query which I confidently expected to extract the relevant data. The Query uses the inbuilt Count feature of the Totals facility but does not produce the expected result. Instead of producing the actual 'Totals' for each field, with one exception it merely repeats the Total calculated for the first of the selected fields.

I detail below the SQL code which was automatically generated by the Query Design process.

Quote
SELECT Count([Mail List].[Member Name]) AS [CountOfMember Name1], Count([Mail List].[E-Mail]) AS [CountOfE-Mail], Count([Mail List].SO) AS CountOfSO, Count([Mail List].[Gift Aid]) AS [CountOfGift Aid], Count([Mail List].[e-Mail List]) AS [CountOfe-Mail List], Count([Mail List].[e-News List]) AS [CountOfe-News List], Count([Mail List].[Third Age Matters]) AS [CountOfThird Age Matters], Count([Mail List].[Leader of Groups]) AS [CountOfLeader of Groups], Count([Mail List].OnlineApplic) AS CountOfOnlineApplic, Count([Mail List].[O/L Transfer]) AS [CountOfO/L Transfer]
FROM [Mail List];
Unquote

Any practical suggestions as to a reason for this unexpected result would be appreciated.

regards,

jcbhydro

Uncle Gizmo · Oct 20, 2014

As a mod, I don't see anything that requires any intervention.

Jcbhydro, the answers you have received are spot on.

Why not provide a sample dB containing test data? Would be more constructive than complaining....

Brianwarnock · Oct 20, 2014

I am amazed at this thread, sure jcb should have responded more positively but after his SQL was shown and he talked of Totals surely somebody could have told him that Count merely returns the number of records returned by a query, ok there is the Null situation to complicate that, but at a basic level it would appear that perhaps he needs to use sum, and it is here that he needs to provide a clearer picture of his data and his required results.

I see no need for a philosophy lesson.

Brian

Brianwarnock · Oct 21, 2014

If my post seems a little odd it is because a moderator has removed posts that the op complained about.
The op has not been back to this thread apparently, or has ignored my attempt to help.

Brian

jcbhydro · Oct 21, 2014

Brian,

I wasn't ignoring you. I was merely waiting to see whether there would be any further action about my complaint of foul language & personal insults posted by a previous responder. Higher authority obviously shares my view and has removed the offending post.

Returning to my original query, you will see from the SQL code I posted, that I had written a Query to extract Totals, from a series of columns in my main Table [Mail List]. My understanding is that the function 'Count' does just that, it counts the number of entries in the column, whereas 'Sum' is presumably used to calculate the aggregate of any numerical entries in a column. I need the former of these 2 options.

My problem is that, with one exception, the routine returns the same 'count' for each column; ie the [Member Name] column, instead of the totals registered in the Table. The counts returned are 28, 26, 28, 28, 28, 28,28........, instead of 28, 26, 7,9,15,20.......

Any suggestions as to the cause of this anomaly would be gratefully received.

jcbhydro

jdraw · Oct 21, 2014

As jcb said initially (date mistype changed to data

I routinely produce a report for regular Committee Meetings which includes statistical data derived from the total rows for various fields in 1 or more Tables.

If he/she were to show us how they did this routinely, with some sample data, the associated SQL and the output, then focused responses could be given. Now, the type of data may be irrelevant, but any response would have been a guess at best.

It's unfortunate that jcb found earlier responses "not in any way helpful!". Perhaps he/she doesn't realize we are all volunteers trying our best to respond to well communicated problems/issues/opportunities. Many posts, in my view, go through iterations of attempts to clarify an issue -until you describe it such that we both understand, no one can solve it - sort of thing.
We only know what the poster writes, so miscommunication occurs often.

Based on the subsequent posts, I'm going to point him/her to w3schools SQL tutorials. These define the function/clause, show examples and provide a means to test/play with the structures.
Count()
Sum()
Having clause

Brianwarnock · Oct 21, 2014

Jcb you need to read and understand what Count does, as I said it counts the number of records returned in a query with a complication regarding Null values.

Do I have an answer for your results? No, but I have a theory that as I no longer have Access I cannot test.
You have 28 records selected by the query, 1 column has 2 Nulls, the rest have many blanks or zero length strings.

Why are you so reluctant to supply data for those who may be able to help?

Brian

jdraw · Oct 21, 2014

jcb,
I agree with Brian's synopsis. I'd like to see your data and specific records where you get your Count to 7, 9 , 15.
We're here to help - you seem reluctant to participate.

Good luck with your project.

jcbhydro · Oct 22, 2014

I am very much aware of the generosity of Forum members who provide assistance to others and I have expressed my sincere gratitude on very many past occasions.

I have not been reluctant to provide data to those wanting to assist. I merely didn't want to confuse the issue by providing information which I thought might be irrelevant.

I am attaching the Test database drastically cut back to reduce size. The main Table shows a series of columns with totals calculated for many of them. The SQL code which i quoted earlier was an attempt on my part to write a Query which would extract those column totals into a datasheet. I had assumed that this could be achieved using the'Count' function, but I was obviously wrong.

I hope that I have now submitted an adequate amount of information to make my problem clear.

Regards,

jcbhydro

jdraw · Oct 22, 2014

jcb,

I think your structure could be adjusted to simplify your analysis. As it stands, and as Brian suggested, you do not have criteria in your Count /Totals query, so you get a Count of the number of records. If you add criteria---such as Where [e-News List] = True, you'll get a new set of counts.

When you Count things, you must be explicit in what is included. Do you want the count of Members who have [e-News List] = True, or False, or it doesn't matter? You have done this in your Post Code query.

How do you identify the Leaders (Of Group)? What Group?
I would recommend you consider additional Tables for Groups and other identifible "Entities" related to your area of study.

Normally, you would not store Totals in a table; you run a query to Calculate a Total when required.

Just some quick thoughts for you consideration.

jcbhydro · Oct 22, 2014

jdraw,

Thank you for your comments which will require some careful consideration.

As mentioned, I had to delete two thirds of the database to fit the 2 Mb upload embargo. Hence you will be unaware of the real structure of the database. It has 5 Tables which includes one called Groups and another called Group Members which establishes relationships between Groups, Group Leaders and Members.
I realise that the misunderstanding would not have occurred if I had deleted most of the records instead of deleting major components of the structure.

I therefore return to my original premise which is that Access has an inbuilt facility to Total records in any column in a Table. It doesn't count the rows in a column. It totals the records which exist. However, the query I built does not correctly extract those totals to a datasheet as I expected.

I will experiment further taking account of the comments you have just made. I will let you know if I have any success.

Many thanks,

jcbhydro

jdraw · Oct 22, 2014

Have you done a compact and repair on your database? This will reduce any used space that Access has reserved for its purposes. Unavailable space results when you delete records/queries/forms etc. You should do compact and repair from time to time to free up that space that Access has used, but does not require on an ongoing basis.

It is quite rare that a database such as yours will be so large.

jcbhydro · Oct 22, 2014

jdraw,

I do a 'Compress & Repair' at least once a week.

I think that the size is a function of having 5 Tables, about 15 Queries and about 10 Reports, plus 4 Forms and a couple of Macros.

The membership is only about 400, but each of them belong to 5 or 6 of the 52 Interest Groups.

Regards,

jcbhydro

Brianwarnock · Oct 22, 2014

You keep talking of Totalling a column , Count count records, quote from Microsoft.

You can use Count to count the number of records in an underlying query. For example, you could use Count to count the number of orders shipped to a particular country.

Although expr can perform a calculation on a field, Count simply tallies the number of records. It does not matter what values are stored in the records.

The Count function does not count records that have Null fields unless expr is the asterisk (*) wildcard character . If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Count(*) is considerably faster than Count([Column Name]). Do not enclose the asterisk in quotation marks (' '). The following example calculates the number of records in the Orders table:

As I said earlier I no longer have Access so cannot look at your DB but as I also speculated some of your records have Null values in some fields but what about others that have no value, what do they contain? If it's 0 a blank or a zero length field it will be counted. If you know what it is you can still do what you want by using a combination of Sum and Iif, effectively putting the criteria in the field row.
Let's assume flda has a blank if no real value

Countflda: iif(flda=" ",0,1)
And select Sum in Total row.

Brian

jcbhydro · Oct 23, 2014

Hi jdraw/Brian and thank you for further comments on my query.

Taking account of what you have each said, I have several attemots at modifying my query.

My efforts have been directed to 'counting' the records in each column of the Table, not a specific value as in the PostCode Query mentioned.

I have tried the Where[Fieldname] = True suggestion without success, so I have now tried 'is not null', but equally unsuccessfully.

The SQL code for this effort is;

SELECT Count([Mail List].Surname) AS CountOfSurname, Count([Mail List].SO) AS CountOfSO, Count([Mail List].[Gift Aid]) AS [CountOfGift Aid], Count([Mail List].[e-Mail List]) AS [CountOfe-Mail List], Count([Mail List].[e-News List]) AS [CountOfe-News List], Count([Mail List].[Third Age Matters]) AS [CountOfThird Age Matters]
FROM [Mail List]
HAVING ((("Where [Mail List].[Surname]") Is Not Null) AND (("Where [Mail List].[SO]") Is Not Null) AND (("Where [Mail List].[Gift Aid]") Is Not Null) AND (("Where [Mail List].[e-Email List]") Is Not Null) AND (("Where [Mail List].[e-News List]") Is Not Null) AND (("Where [Mail List].[Third Age Matters]") Is Not Null));

The query runs without any error message but merely counts all records in the table irrespective of whether they are yes/no or null/not null.

What puzzles me is the fact that the 'Total' facility in a Table structure gives the option for 'None' or 'Count' and if 'Count' is selected it does just that it counts the records in each column.

Regards,

jcbhydro

jcbhydro · Oct 24, 2014

Good Afternoon,

After a deal of experimentation, and great assistance from Forum Members. I have eventually succeeded with my 'Totals Query.
The Count facility counts the number of records OK, but it counts Yes and No in tickboxes, not just the Yes ticks.
The solution as suggested is to use SUM for the tick box columns and to use ABS to convert the result to a positive number.

My SQL Code noww is;
SELECT Count([Mail List].Surname) AS CountOfSurname, Sum(ABS([Mail List].SO)) AS SumOfSO, Sum(ABS([Mail List].[Gift Aid])) AS [SumOfGift Aid], Sum(ABS([Mail List].[e-Mail List])) AS [SumOfe-Mail List], Sum(ABS([Mail List].[e-News List])) AS [SumOfe-News List], Sum(ABS([Mail List].[Third Age Matters])) AS [SumOfThird Age Matters], Sum(ABS([Mail List].[Leader of Groups])) AS [SumOfLeader of Groups], Sum(ABS([Mail List].OnlineApplic)) AS SumOfOnlineApplic, Sum(ABS([Mail List].[O/L Transfer])) AS [SumOfO/L Transfer]
FROM [Mail List];

Many thanks to all who pushed me to the winning post.

jcbhydro

Reort to show totals extracted from Tables (1 Viewer)

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Nifty Access Guy

Retired

Retired

Registered User.

Super Moderator

Retired

Super Moderator

Registered User.

Attachments

Super Moderator

Registered User.

Super Moderator

Registered User.

Retired

Registered User.

Registered User.

Similar threads

Users who are viewing this thread