Help with "crosstab" query without specific row headings

[cardgage]

Good evening everyone,

I hope that you are enjoying the holiday season! I am having some difficulty displaying my data in a way that I am happy with. I currently have a query that has converted integer data into categorical data (1s and 0s) for a project that I am working on, and would like to calculate sums for each variable based on the group that the individual is assigned to. Here is what my data looks like now:

ID	Group	Var1	Var2	Var3	Var4
1	1	0	0	1	0
2	1	1	0	1	1
3	2	0	0	0	1
4	2	0	1	0	1

And here is how I would like the data to be represented:

	Group 1	Group 2
CountOfID	2	2
SumOfVar1	1	0
SumOfVar2	0	1
SumOfVar3	2	0
SumOfVar4	1	2

Is this something that is possible to accomplish within Access? I could do this manually in Excel, but if data are updated/added (which is likely to occur) it would be nice to just re-run the query and export instead. Any help with this will be greatly appreciated!

Cheers,
cardgage

[Micron]

Based on your inputs and outputs, it is very doable.
Create a Totals query; GroupBy Group for the first column, use the aggregate function Count on the ID field and the Sum function on the var fields. A couple of red flags:
- I hope you have not actually named your field "Group"
- if you end up with a new Var, do you need to add a new field to your table? If so, this is not optimal (probably the db is not normalized).

[cardgage]

Thank you for the help Micron, this worked like a charm!

I did not name my field "Group" - just making it easier to see what the purpose of the field was.

Unfortunately, I would have to add a new field to my table if a new variable is added. The way the project is set up, everything had to be defined (including variables analyzed) before data collection began, so we did not anticipate having to add new variables. However, for my own reference, would you mind pointing me towards a reference for achieving this optimally? I am not familiar with normalization of databases.

[Micron]

There are a gazillion web results for database normalization and other design aspects. The trick is to find some that speak to your level of db knowledge.
Here's some to try for a start
http://www.fmsinc.com/free/newtips/primarykey.asp
http://allenbrowne.com/AppIssueBadWord.html
http://www.access-programmers.co.uk/...d.php?t=225837
http://www.utteraccess.com/wiki/index.php/Autonumbers

I borrowed these references from 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/...ng-part-i.html

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

WRT normalization, the key is to consider things related to the business nature of the db (such as customers) as entities. Any characteristic or attribute of that entity goes in that table. Their orders do not. The number of times you've contacted them do not. They go into related tables, which are treated exactly the same way. Those tables generally have the primary key from the entity table as a foreign key in the related table, and what is considered optimal in most cases is to use the autonumber field from the entity table as the foreign key value. This way, if the company changes some aspect of their information, it does not need to be propagated throughout the db by either enforcing cascading updates (through defined relationships) or via query, assuming that type of operation is desirable.