Newbie crosstab help

[ghobbitnz]

Hi can someone help me with a crosstab problem? It may be it can be acheived without a crosstab but with some clever SQL instead but I cant seem to work it out

I have a group of people who are producing stats each month,so for the month of January I have a table (tblstats) with something along the lines of

Username stats1 stats2 stats3 stats4 stats5
John 5 9 4 5 10
Joe 7 4 5 1 3
Jane 10 3 2 6 8

and the same again for February

Username stats1 stats2 stats3 stats4 stats5
John 6 9 4 5 10
Joe 8 4 5 1 3
Jane 9 5 2 6 8

and again for March etc.

I need to get the totals for each user for stats 1, 3 and 5. So for John and stats1 it would be 5+6+march+april etc, and then the same again for stats 3 and then stats5. In the end, for the whole year, I would have something like

Username stats1 stats3 stats5
John 102 103 123
Joe 112 97 106
Jane 111 102 98

I can run a query no problem with
Select tblstats.username,tblstats.stats1,tblstats.stats3,tblstats.stats5
From tblstats;

From this I get multiple entries for each user i.e. the user John appears 12 times (once for each month of the year) as does all the other users. I tried using something to the effect of

tblstats.username,sum(tblstats.stats1) AS stats1, sum(tblstats.stats3) AS stats3, sum(tblstats.stats5) AS stats5
From tblstats;

But needless to say that doesnt work!

I've tried doing a crosstab query and that will give me the username just the once but I cant get it to give me the totals for more than one column. I assume this has something to do with the Transform statement but I'm not very familiar with crosstabs.

Can someone help me with what I need to put in to acheive what I need to do?

many thanks if you can

Steve

 

[Lilliabeth]

I have a group of people who are producing stats each month,so for the month of January I have a table (tblstats) with something along the lines of

Username stats1 stats2 stats3 stats4 stats5
John 5 9 4 5 10
Joe 7 4 5 1 3
Jane 10 3 2 6 8

and the same again for February

Username stats1 stats2 stats3 stats4 stats5
John 6 9 4 5 10
Joe 8 4 5 1 3
Jane 9 5 2 6 8

and again for March etc.

Why the odd design (seperate field for each stat#) and why do you have seperate tables for each month?

Sometimes I see business reasons to break a fundamental rule of database design, but I'm not seeing it here. Is this data being imported like this or something?



Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Lilliabeth]

I just noticed your title mentions that you are a newbie, this explains why you are trying to figure a way to make a crosstab make up for your design issues.

Before beginning a project like a database, it would be beneficial to do a little research. There are just a few rules of database design that you must know about. They are called Normal Forms. If you look around these fora, you will find hundreds of references to database fundamentals and Normalization.

If you had read the fundamentals, you might have created something like this table:

Username StatID Stat Date
John 1 5 1/1/2007
John 2 9 1/1/2007
John 3 4 1/1/2007
John 1 6 2/1/2007

etc, etc

Now in an ordinary query, you can group by Username and StatID, enter criteria (1 or 3 or 5) for StatID, and sum Stat to get something like this:

Username StatID SumOfStat
John 1 20
John 3 32
John 5 18
Joe 1 24

etc, etc

Look around these fora for one of the hundreds of posts that link to the database 101 fundamentals called rules of normalization aka normal forms.

Good luck!!

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[ghobbitnz]

Hi

Thanks for your reply. These are not multiple tables but one table and yes its imported data from an excel spreadsheet.

I can see how the way I explained it above that you may think its mutliple tables but no. Its one table to which I append the stats to each month. I need to get the info out of the table I already have.

thanks anyway

 

[Lilliabeth]

maybe I muisunderstand again, but I think you do not need crosstab, just a query...


Add username, stat1, stat3, and stat5 to query grid. Turn on Group By for Username, and Sum the others

to get the following

SELECT tblTable.Username, Sum(tblTable.Stat1) AS SumOfStat1, Sum(tblTable.Stat3) AS SumOfStat3, Sum(tblTable.Stat5) AS SumOfStat5
FROM tblTable
GROUP BY tblTable.Username;



Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[SkipVought]

have a group of people who are producing stats each month,so for the month of January I have a table (tblstats) with something along the lines of

You must educate your group of people to enter the data in a pre-determined NORMALIZED table format in their spreadsheets.

Skip,

_[red][/red]

 

[dhookom]

IMHO in some situations, data can be entered/edited in non-normalized format. However, the data should be stored in normalized tables. You can move data around with queries and/or code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ghobbitnz]

Thanks all for your advice

I used the SQL provided and thats doing more or less what I needed and can carry on from there. Many thanks for the help - I thought there had to be a way somehow without using crosstab.

I'm using several pieces of software which does complicate things a bit. The users are not entering any info as such into anything. The data is generated from a Lotus Notes application that our firm uses (cant get around that one). I then need to get the data out of lotus notes but unfortunately cant in any useful way that I can deal with. The application produces a comma delimeted file which I then put into excel to produce the stats I need and then that is uploaded to an access database where the user can then generate reports from there.

We are currently working with developers using datamining to better acheive what we need but what I'm doing above allows us to do what we need in a cheap and nasty to get the stats we need for our reports until the datamining comes online

thanks again

 

[SkipVought]

Duane,

Of course, the data can be massaged programatically. But I've found that in some instances, the process can be improved upstream, by just asking the questions. People just don't realize how much more useful the data can be, if it's cast properly. As data moves downstream, it gets used by more and more people. Alot of people use tools like Excel, and trying to do a lookup or pull data out of non-normalized source data becomes a challenge.

Skip,

_[red][/red]

 

[lespaul]

you can download the NotesSQL ODBC driver and then write queries from Excel (maybe you can get it to work from Access, but I could only get Excel to correctly interpret the data) and get the data in the "normalized" form that has been suggested.

you can download the driver here


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ghobbitnz]

Many thanks all
Steve