Help with Cross tab query appreciated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Help with Cross tab query appreciated

Author

Topic

spib
Starting Member

9 Posts

Posted - 2002-08-21 : 05:31:01

Hi,

I have two tables: the first is called FILES and contains data on one or more unique files. The second is called EVENTS and a record is added to this table whenever an event takes place on a file in the FILES table. Simple table definitions are shown below

FILES
=====
SEQ_NO (primary key)
NAME

EVENTS
=======
SEQ_NO (primary key)
FILE_ID (points to FILES.SEQ_NO)
TYPE

What I would like to do is present a list of FILES with a count of each event type drawn from the event table

SEQ_NO NAME EVENT1 EVENT2 EVENT3
-----------------------------------------
1 FILE1 6 9 8
2 FILE2 7 3 1

Does this make sense?

Cheers

Edited by - spib on 09/15/2002 13:07:15

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-21 : 06:04:04

what you're looking to do is a form of "cross-tab".....search here (or Books Online) for examples of same...

spib
Starting Member

9 Posts

Posted - 2002-09-15 : 11:19:27

OK, I got the following

TRANSFORM Count(Events.File_id) AS CountOfFile_id
SELECT Events.file_id, Count(Events.File_id) AS [Total]
FROM Events
GROUP BY Events.file_id
PIVOT Events.Type;

This works almost exactly as I would like. However, I need to able to sort on any of the colums, particularly the total count. How can I do this? A simple ORDER BY doesn't work because I get the error 'Cannot use the cross tab of a non-fixed column in a sub query'. This is Access 2000 by the way.

spib
Starting Member

9 Posts

Posted - 2002-09-17 : 07:00:30

OK, noone seems to be able to help

What I've done is put the SQL statement into a query in Access (analagous to a view right?) and then select from that in my app. This is not too bad but it means that I can't change the criteria of the query.

Anyone have any ideas about what I should do to get around this?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-17 : 09:49:21

No. I remember having the same problem when I was using Access. There ARE limits to what it can do, you know.

If you know some Visual Basic you can modify the QueryDef of the cross-tab query to make it more dynamic (changing the SQL property). There's some documentation in the MS Access help file (under the Contents menu, look for Visual Basic Reference) but you'll have to work out the details on your own, they won't have a specific example for your situation.

Actually VB is sort of a last-chance effort, it doesn't work well outside of a form or report (and isn't all that great there either)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources