By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

How to count records in Many to Many scenario?

P: 91
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
Apr 26 '12 #1
Share this Question
Share on Google+
5 Replies


P: 91
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
Apr 26 '12 #2

Rabbit
Expert Mod 10K+
P: 12,366
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.
Apr 26 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
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?
Apr 26 '12 #4

P: 91
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.
Apr 27 '12 #5

Rabbit
Expert Mod 10K+
P: 12,366
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.
Apr 27 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.