Query counting number of apperances in multichoice lists.

[Wilkobeczka]

Hello guys ,
I have a problem with creating a query which would count for me how many times each option in a multi-choice list is chosen. I want to count options which are in query "character" as "name" in a list called "characters involved" in table "chapters list".
For example: I have chapter 1 with characters A, B and C and chapter 2 with A, C and D. The query should display that character A appeared 2 times, B 1 time, etc.
Thank you in advance for any help! <3

[Micron]

I have a problem with creating a query

This sounds like you tried something that didn't work. If so, and the following suggestion doesn't help, post back with what you tried. A simple Totals query should work, using Count against the field you want to count and group by chapter. If a book title is also involved, the first group level probably should be that one. The problem with such queries is that sometimes, including fields for which you want to see data but not really group by can introduce problems. There might be other aggregate functions beside count that you can employ which don't affect the outcome, or using the Where condition, but still allow you do see the data. Other times a second query is required.

Try creating a totals query, and post back with the sql you tried that came closest to what you want.

[Wilkobeczka]

I've tried something like that: (my Access is in Polish so 'Kwerenda' is simply query and 'Policz'=count)
SELECT [Chapters list].[Characters involved], Count([Character Kwerenda].Name) AS PoliczOfName
FROM [Character Kwerenda], [Chapters list]
ORDER BY Count([Character Kwerenda].Name) DESC;
-> However my English isn't so bad, I got a little lost reading what I have to do, but I'm pretty sure that this is what you've described. Unfortunately, the results were not as expected: Options chosen were taken as a whole, not separate so I got results:
A B C -> 7
A C D -> 7

[Micron]

Are you sure this is a Totals query? You clicked the summation character at the top (∑)?
I ask because I don't see a GroupBy clause in your sql statement.
Also, there are no multi-value or lookup fields involve here are there?
EDIT:
I just noticed you have a comma in your sql where I think a period should be - [Character Kwerenda], [Chapters list], presuming the first [] is a table or query name and the second [] is a field. If this were based on a table called Chapters list, this should work:

SELECT [Chapters list].[Characters involved], Count([Chapters list].[Characters involved]) AS [CountOfCharacters involved]
FROM [Chapters list]
GROUP BY [Chapters list].[Characters involved];

I would advise you to read up on object naming conventions. Spaces are one of the things that should not be in your object names.
http://www.access-programmers.co.uk/...d.php?t=225837

[ssanfu]

FROM [Character Kwerenda], [Chapters list]

It also looks like there is a Cartesian join on the two queries (or query and a table)


Also not that "Name" is a reserved word in Access and shouldn't be used as an object name. And it is better to NOT have spaces in object names

[Wilkobeczka]

Maybe I'll simply take a screenshot of what I've got:

This is a table with data about chapters. Characters are taken from a query which simply show the content of this table:
as I wanted to have a possibility to add a new character to a chapter whenever I add a new one to the table of characters. The same query is a source for the list of characters.
The results of query look as follows:

And here's the query itself:

In "Sum" There is "Expression" for characters involved and "Count" for the name. I'm completely new to Access so I don't except that I've made some basic, stupid mistake.

PS: I've changed the "Name" field to "Character's name"

[Micron]

Guys, sorry for the late edit.
@Wilkobeczka - please review the edit.

[Wilkobeczka]

Micron, Access showed that I cannot use multi-option/data/value etc. field (sorry for not being precise but I'm not sure what's the original translation) [Chapters list].[Characters involved] in the clause GROUP BY.


And thank's for telling me that the name's of fields shouldn't have spaces - I'll change it .

[Micron]

OK, a couple of problems.
- your data is not properly normalized (you are using multi-value fields) as shown in your 1st and 3rd images. As you now see, you cannot re-create what I did because you cannot group by on a mvf (there are other things you cannot do or need a work-around for with these fields). I never have and never will use them. I think you have 2 options:
a) learn and implement proper normalization and rebuild your db (learning will help avoid such design flaws in the future)
b) you need a query that parses out the multi-values, then you should be able to create a totals query on the parsing query. The key aspect of this sql statement is the .Value property of the mvf. You would need the mvf and the .Value field for each mvf in your parsing query. What you will get is a view of how your table data should be.
SELECT tblMulti.Field1, tblMulti.Field1.Value FROM tblMulti;

Second problem
- you have gone from bad (using "Name") to bad; using an apostrophe. I'm going to provide some of my bookmarked links for you to review.

naming and reserved words:
http://www.access-programmers.co.uk/...d.php?t=225837

http://allenbrowne.com/AppIssueBadWord.html

db design (note plethora of topics in left nav bar)
http://www.fmsinc.com/free/newtips/primarykey.asp

More valuable stuff, courtesy of Orange:

Database Design Basics

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html