One field, two criteria!

[Roo]

Hi there
I have a table of which I need to count the instances of one of the fields.
The trouble is that I need to return two different counts based on two different criteria.

I don't really want to duplicate the table just to be able to achieve this, and I wanted to be able to construct it in a way that I can use in my ASP page which interrogates the database.

Of course everything I've tried applies the two criterias at once to the results. Stuck on this one, any help much appreciated.

 

[neileg]

Bear in mind I know nothing about ASP!

If you want two counts, you'll need to use two totals queries or to use DCount() twice, using your two different criteria.

 

[Roo]

Thanks for your reply.
I didn't want to have to do two queries, but I will look at the DCount method.

 

[Roo]

Unfortunately this doesn't appear to be working for me.

I have tried putting this in as an expression in my query:
(my two counts that I need)


ID1: DCount("[ID]","DDts_tbl")

ID2: DCount("[ID]","DDTS_tbl","[When Detected]<>[When Caused]")

It returns the same results for both columns though.

(I need to return results grouped by Project).

 

[neileg]

Your sytax looks OK. Make sure you've spelled your field names in the criterion correctly with the correct upper/lower case. If there's an error here, that means the criterion will always be true and you will return all records.

Are you sure there are records where [When Detected]=[When Caused]?

 

[Roo]

Thanks Neil
I'm glad that you believe it should work, because I think the same, but can't fathom it!
I do know that the criteria should return different results, as I have constructed a lone query which justs hooks out those values based on the same criteria but by doing it via the WHERE method.

I will check the field names, but I think they're ok.

Thanks for your help.

 

[neileg]

By the way, do you realise that DCount() is just another way of running a query? A stored Access query will be more efficient because they are compiled, not interpreted.

I still don't understand why you don't want two queries. You can union the results of the two queries so they become one query, you know.

 

[Roo]

I did try that Neil, but you end up with one column and the results are one after another e.g.
Project1 45
Project1 56
Project2 10
Project2 20

I need them in separate columns.

 

[neileg]

You mean like this?

 

[Roo]

Thanks Neil very much for your example db.
This does do exactly what I wanted, but I was kind of hoping to be able to construct one SQL query to insert into as ASP page, but I may have to use the two queries combined as you have written.
Thanks again for your help, much appreciate.

 

[neileg]

This does do exactly what I wanted, but I was kind of hoping to be able to construct one SQL query to insert into as ASP page, but I may have to use the two queries combined as you have written.

But the third query combines the two queries into one. Look at in SQL view and post this into your ASP page. SQL is SQL after all, it doesn't matter that it's the result of nested queries.

 

[Roo]

The SQL of the third query references the other two queries and not the tables, so without the other two queries the SQL below on the ASP page would not work I don't think.

SELECT qryCountAll.Project, qryCountAll.CountOfID, qryCountNotEqual.CountOfID
FROM qryCountAll LEFT JOIN qryCountNotEqual ON qryCountAll.Project = qryCountNotEqual.Project;

 

[neileg]

If I was any good at this stuff, I would know how to write this as one SQL statement. But I'm just a hacker, so I can't!

 

[Roo]

You've been very helpful, thanks.