How to capture totals... or sums... or something

[Oscar.Ingalls]

Ok, so here it is. I have a fairly simple DB use to track incidents. This is an enterprise system covering 65 locations under 7 different units. I need a very simple report that basically counts the total open issues divided by a count of issues without an action taken against them by unit.



I tried to figure out how to do this on a query but have no idea how. I know how to count a single total on a report, but how to go beyond that I've got nothing.

I'm very new to this, I have zero skill in VBA. Help?

[Rod]

Should be able to get your counts via an aggregate query (no VBA!) but I can't be certain until I have sight of your db design. Are you able to post it? Publish as v2007 or earlier - or provide a picture of your data relationships.

[Oscar.Ingalls]

I Can't upload the DB as it's already populated and is big. Does this help?

[Oscar.Ingalls]

Aggregate query? What and how?

[Rod]

Relationship diagram (also known as a Bachmann diagram) is fine; we can work with that. I note however that you have not specified relationship types. This is not critical when specifying queries so you need not rework anything now. How familiar are you with the Query Design Grid - the one you see when you follow Create/Query Design? I don't want to spend time and effort giving you unnecessary instructions.

An aggregate query is the more usual SQL term for what MS Access refers to as a 'Totals' query.

I'll need to prototype (copy) your db so that I can give you meaningful help. Leave it with me for a while.

[Oscar.Ingalls]

Silly question, but how do I send it to you? It's 3.75mb after I cleaned it up. I can load it into my dropbox or something.

[Oscar.Ingalls]

I'd say that I know enough to make queries without using the wizard. I know how to change around between append, make, and delete queries. I know basic criteria stuff. I haven't needed to mix tables in queries so have no knowledge in such. Is that enough brain power to go on?

[Oscar.Ingalls]

Going to bed for the night. I will pick this back up in the AM on any instructions you give me. Thank you for any assist you can provide.

[Rod]

Have a look at this.



Note that the sigma button on the ribbon is selected to indicate a totals (aggregate) query.

I assume you can determine incidents with no action from the status. Change the criteria row to be whatever state indicates no action (3rd column of grid).

I've grouped on business unit and counted Issue.ID.

I've given ID a more meaningful alias for ID of, 'Count of Issues.'

Get back to me if you need further help.

[Oscar.Ingalls]

Ah yes. A very different manner to get there, but I was able to do what I think is the same by setting up an "Is null' or "is not null".

On the original relationships, I am needing to count total records, then divide that by the number of Notes = "Is Not Null" (the notes field is where the action is described) where Status = "2 or 3 or 5 or Is Null" (to filter out the records with closed status... Closed = 4) to give me a percentage of open items that have action taken against them.

I could do this in excel by a simple countif Description = Is Not Null / countif Notes = Is Not Null. But in access I'm just a self taught noob.

[Rod]

Slightly modified: I have made the test for no action to be Trim(Nz(Notes,"")) <> "" This is a little better than simply testing for Null as it will also discount white space. Also I have changed the test for open items to look at the Status fielld on the Issue table.

You can't count all records and filtered sub groups in one step.

[Oscar.Ingalls]

Nice, thank you. That counts all records sorted by business unit.

So now is the next step, how to count those issues that Notes = Is Null. I will try to use what you sent me to get there. Is this combining 2 queries?

But then comes how to divide the one by the other and get that into a report.

[Rod]

Read up on the DCount function. I would use that for counting the total number of issues and place the result somewhere in the report heading. You then have the denominator for all the individual divisions, the numerators come from the query.

To count the no action issues simply change the comparison to equality: Trim(Nz(Notes,"")) = ""

[Oscar.Ingalls]

Nice, thank you. That counts all records sorted by business unit. I twisted it a tad and now can count by business unt the issues without action.

But then comes how to divide the one by the other and get that into a report.

[Oscar.Ingalls]

Ok, side question. When that query evaluates the table issue, if there is an issue but notes = is null, how do I make the count show a zero instead of not showing up at all in the query? Does that make sense?