yes/no checklist query help

[nightclock]

i have a database on a checklist of videogames and i'm trying to run a query to show me how many games from each publisher i have but i'm having a problem, it'll only show each publisher once but it won't count any of the yes and no it just counts the total of how many times the publisher shows up in the row so instead of being

5/10 Ubisoft
8/13 EA
2/5 Activision

it'll return the results as

10 Ubisoft
13 EA
5 Activision

on top of this i have another question that i want to know if it's possible to expand/shrink in each publisher result so for example if it was correctly displaying the way i want it above it'd be like this

5/10 ubisoft

-yes prince of persia 1
-yes prince of persia 2
-no prince of persia 3
-no assassin's creed 5
-yes assassin's creed 6
-no assassin's creed 7
-no ghost recon 4
-no ghost recon 5
-yes ghost recon 6
-yes ghost recon 7

[John_G]

Please show us your table structure, and the SQL of your query. Your phrase

counts the total of how many times the publisher shows up in the row

is a red flag that your data may not be properly normalized.

For your last question - a query can't expand a list as you would like - for that layout you would need a report or a form-subform arrangement.

[CJ_London]

want to know if it's possible to expand/shrink in each publisher result

you would need to use the activeX treeview control used in a form, however it is not well documented.

In a query, if you have two tables, publishers and games, and set the relationships correctly then in a new query with just publishers and the table source you can right click on the query gui (top bit) and select properties if not already displayed.

In Subdatasheet name put the name of your games table then populate the Link Child Fields and Link Master Fields with the relevant primary and foreign keys. When you run the query, click on the + button to expand. Note this facility is not made available in forms

[nightclock]

Please show us your table structure, and the SQL of your query. Your phrase



is a red flag that your data may not be properly normalized.

For your last question - a query can't expand a list as you would like - for that layout you would need a report or a form-subform arrangement.

i have no SQL running in the query i'm just running it through the duplicate query wizard and as for saying row i meant column

[John_G]

Thanks, Ajax - I didn't know that (probably because I never had a need for it).

There's always a lot to learn in this business.

[John_G]

it'll only show each publisher once but it won't count any of the yes and no it just counts the total of how many times the publisher shows up in the column

To show how many games you have from each publisher, you need to add a criteria to your query to count only records where the checkbox field is True.

If you really want to have both values (e.g. 5 and 10 for Ubisoft), then don't use the criteria. Instead, in your totals query, count the number of occurrences of the checkbox with Count() as you did before (I assume). That will give you the 10 part.

Create another field with an expression something like this: IIf([checkbox],1,0), where [checkbox] is the name of the checkbox field, and SUM that expression. That will give you the 5 part.

If you want to show the results as "5 / 10", create an expression like this:

Sum(IIf([checkbox],1,0)) & " / " & Count([checkbox])

and in the query, set the value in the Total row to "Expression" (select from the dropdown)

[CJ_London]

i have no SQL running in the query i'm just running it through the duplicate query wizard and as for saying row i meant column

The query window you are using is just a way of visualising the SQL. If you look to the top left of the ribbon you will see a view option - one of these is SQL - select it and you will see the SQL that the query window has generated. Showing the SQL code is the best way to get help on this and other forums with regards queries.

I didn't know that

has its uses when developing - find it quite useful to swap round - i.e. games table is the one in the query and publishers is the Subdatasheet so you can check who the owner is