How to count records in Many to Many scenario?

[sg2808]

Hi,

I have two tables, [Rules] and [Process] which are in a many to many relationship via a junction table[JRule_Process]. It has the following field (relevant ones for this discussion):

[Rules]
RuleID
RuleName

[Process]
ProcessID
DetailProcessName

[JRule_Process]
JunctionID
RuleID
ProcessID


I have almost 5000 unique rules and around 100 Detail Process Names. Currently, only some of the rules are mapped with processes.
I have designed a query (with JOIN) which shows all rules and processes where it is mapped and where it is not. So, when I run the query, I can see all the [RuleName] and partially populated column for the [DetailProcessName].

I want to do a count of the "blank" record in the [DetailProcessName] column. How can I do it?

For the above scenerio, what I want is to count the number of process records where it is mapped to Rule and where it is not.

In the query design view, I only selected [DetailProcessName] and I put the critiria as "Process.DetailProcessName = Null" and used the Count function but it is not working.

How do I design the query so that I can count all records where [ProcessName] is mapped to a [RuleName] and also where it is not ?

Please advise.

Thanks,
SG

[sg2808]

Hi,

In the design query, I have selected a field and I have marked "Unique Value" as Yes in the property. When I run the query I see 345 records. However, with the same settings, when I use Total function and set the Groupby as "Count", it then shows the count value as 688. Why is this so? It seems that the count funtion is not taking into account the "Unique Value" setting.

Wondering if there are any additional settings which will give the count value as 345?

Any suggestions pls?

Thanks,
SG

[Rabbit]

You'll have to use two queries. One query will do a count on Processes where the ID EXISTS in the junction table. The other one will do the same thing except where the ID NOT EXISTS.

[Rabbit]

Can't help without seeing the code and sample data.

But just so you're aware, using DISTINCT only affects the final values displayed. It's not used to control what is input into the query engine. To do that, subquery the DISTINCT and then do the count in the outer query.

Is this related to your thread about counting MANY to MANY records?

[sg2808]

Thanks Rabbit.

Sorry, I am not good with SQL and I trying to use the Query Design view in Access to query the data. Not sure how to do this in the design view.

Yes, it is related to my previous thread.

[Rabbit]

Please do not double post your questions. I have merged the thread with the original.

If you're going to be doing work in databases, I strongly suggest you learn SQL.

However, you can replicate the functionality by creating one query that selects your distinct records. And then another query that uses that query to do your counts.