This is not the best way to design these tables, but here's a trick to get
you out of trouble.
Internally Access uses -1 for True, and 0 for False. Therefore if you sum
the yes/no field, you get the negative of the number of Yes answers.
Steps:
1. Create a query using this table.
2. Depress the Total button on the toolbar.
Access adds a Total row to the grid.
3. Drag the State identifier into the grid.
In the Total row under this field, accept Group By.
4. Drag each of the Technology fields into the grid.
In the Total row, choose Sum.
Once you have that working, if you want to change the negatives to
positives, switch the query to SQL View (View menu), and add a minus before
the calcuations. For example, change:
Sum(Table1.Astronomy) AS SumOfAstronomy
to:
-Sum(Table1.Astronomy) AS SumOfAstronomy
To explain the comment in the first paragraph, it would be a much better
design to create a Technology table where you have 7 records for the 7 types
of technology, and then an OrganizationTechnology table with fields:
TechnologyID relates to Technology.TechnologyID
OrganizationID relates to Organization.OrganizationID
StateID relates to State.StateID
If an organization handles 5 technologies in Iowa, they have 5 rows. If they
handle 2 in Kansas, that's another 2 records. Now there's only one field to
search, count, whatever, for all the technologies.
Technically, this is called a "junction" table, and it is the standard way
of resolving the many-to-many relation that exists between technologies and
organizations into a pair of one-to-many relations. If that's a new concept,
there's another example here:
http://allenbrowne.com/casu-06.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<to********@googlemail.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
>
I have an access dbs with 2 key tables. One is a State table that has
State id and State name fields. This feeds a combo box in a form to
provide a reference point for the State Id field in the Main table.
This allows multiple State instances, as I am mapping a number of
organizations in each state.
The main table has one entry per organization and one state reference
per line. It also has 7 Yes/No fields to identify what technology each
org has.
What I want is a query that gives me a full list if states and a count
of how many of each tech they have.
I have made queries that show a count of each technology per state, but
not one that gives all. The closest I have come is to have a report
that gives all, but only if there is at least one instance of each
technology per state. As I said, I want to know when there are no
instances of a tech in a state.
