Totals Query Problem

[bmcd]

I want to do something fairly simple. I have 2 tables that are structured as follows:

Table_FormLogInfo (Table #1)
-------------------------------------
ID
AreaID
FormReceived
ReceivedDate

* Note that the field "FormReceived" can be set to 1 or 0.

Table_AreaAbbr (Table #2)
---------------------------------
AreaID
AreaAbbr

I'm trying to set up a totals query where I do the following:

AreaAbbr is in column 1 with Total set to "Group By"
FormReceived is in column 2 with Total set to "Count"
FormReceived is in column 3 with Total set to "Where" and Criteria set to 1

The query is working correctly but not exactly how I want it to. It returns only those "AreaAbbrs" where the "FormReceived" field is set to 1 and it counts them. What I would like it to do is return ALL "AreaAbbrs" and set the count of "FormReceived" to zero instead of omitting those that do not have "FormReceived" set to 1.

Can I accomplish this?

Thank You,

Becky McDermott

 

[andrew93]

Hi Becky

I think you need to do this with 2 queries because I don;t think you can combine the count with the crtieria you want. The first query includes the data from just Table_FormLogInfo where FormReceived = 1.

The SQL for the query looks like this :

SELECT Table_FormLogInfo.ID, Table_FormLogInfo.AreaID, Table_FormLogInfo.FormReceived, Table_FormLogInfo.ReceivedDate
FROM Table_FormLogInfo
WHERE (((Table_FormLogInfo.FormReceived)=1))
WITH OWNERACCESS OPTION;

Save this query (I used the name, qryForms1).

The 2nd query looks pretty much like your query, but swap Table_FormLogInfo for qryForms1 (or whatever you called your query).

In your query design screen, double click the join between the table and query and click the option that says something like 'Include all records from Table_AreaAbbr and only the matching records from qryForms1'.

Save and run the query. All of the "AreaAbbr's" should be there and where there are no forms received, you should see a zero value.

The SQL for the 2nd query looks like this:

SELECT Table_AreaAbbr.AreaAbbr, Count(qryForms1.FormReceived) AS CountOfFormReceived
FROM Table_AreaAbbr LEFT JOIN qryForms1 ON Table_AreaAbbr.AreaID = qryForms1.AreaID
GROUP BY Table_AreaAbbr.AreaAbbr
ORDER BY Table_AreaAbbr.AreaAbbr
WITH OWNERACCESS OPTION;

HTH, Andrew

 

[Jon K]

Try this query (type/paste in the SQL View of a new query):-

SELECT AreaAbbr, -Sum([FormReceived]=1) AS [Count of FormReceived=1]
FROM AreaAbbr INNER JOIN FormLogInfo ON AreaAbbr.AreaID = FormLogInfo.AreaID
GROUP BY AreaAbbr.AreaAbbr;


If the FormReceived field may contain Null values for some AreaIDs in the FormLogInfo table, you can use Nz(...)+0 on the FormReceived expression, i.e. change the expression to:-

Nz(-Sum([FormReceived]=1))+0
---------------------------------------

Edit:-

What I would like it to do is return ALL "AreaAbbrs" and set the count of "FormReceived" to zero instead of omitting those that do not have "FormReceived" set to 1.

I didn't notice the ALL there in my first reading. To return ALL AreaAbbrs, you can use a Left Join with Nz()+0:-

SELECT AreaAbbr, Nz(-Sum([FormReceived]=1))+0 AS [Count of FormReceived=1]
FROM AreaAbbr LEFT JOIN FormLogInfo ON AreaAbbr.AreaID = FormLogInfo.AreaID
GROUP BY AreaAbbr;
.

 

[andrew93]

I'm curious as to how you manually enter this into the query design screen given you used different table names :

-Sum([FormReceived]=1) AS [Count of FormReceived=1]

How would you key that in? Exactly as you wrote it?

Andrew

 

[Jon K]

Originally Posted by andrew93
I'm curious as to how you manually enter this into the query design screen given you used different table names :

-Sum([FormReceived]=1) AS [Count of FormReceived=1]

How would you key that in? Exactly as you wrote it?

As FormReceived exists in only one of the tables, you can leave out its table prefix in the SQL statement.

Try building the tables and query to see for yourself.
.

 

[andrew93]

Jon,

Don't get me wrong, that is a pretty neat query but I think you misunderstood my question....maybe I will have to be a little less subtle.

In your post you used different table names (compared to the Becky's table names) so Becky cannot copy and paste your SQL into the database without changing the table names to make it work. If Becky retains the existing table names, and manually enters the line "Nz(-Sum([FormReceived]=1))+0 AS [Count of FormReceived=1]" into a query, a syntax error would occur. Becky called her tables Table_AreaAbbr & Table_FormLogInfo while your SQL refers to AreaAbbr & FormLogInfo.

If Becky wants to retain the original table names and use your suggestion, she would need to enter it into the query like this "Count of FormReceived=1: Nz(-Sum([FormReceived]=1))+0" and change 'Group By' to 'Expression'.

There you go Becky - two ways of doing what you want.

Andrew

 

[Jon K]

Yes, I misunderstood your question. And I thought "Table_" was just an indication, not a part of the table names. That was why I used "..... in the FormLogInfo table, ...." in my post.
.