Multi-Column CrossTab Query? Is it possible?

[GComyn]

I've been trying to create (in Access 97) a Multi-column crosstab query for a while now, and not getting anywhere.

I was wondering if anyone out there has done this, if it can be done.

Here is an example of what I'm looking for....

                           Month
           January 2001          February 2001
Name    Accounts   Balance   Accounts    Balance
CRS           25   $   250         50    $   500
CSI           50   $ 1,000         40    $   800

etc...
The 'Month' would be a label on the report and the actual months would be pulled from the query. the above is how I want the report to look, not the query... but I can't get the query to do multi-column crosstabs.....

Any help would be appreciated......


Gcomyn

 

[dianal]

What are the initial tables?

 

[Databaseguy]

You have to create 2 x-tab queries, and set the column headingd using the query prpoerties tab for each one. Then, create a "normal" query and pull in the x-tab fields from the two x-tab queries.

I'm locked out of my office, but I'll post the Microsoft web site that lists how to do this about 9:00 PST.

 

[GComyn]

Ok.. the tables that I am linking together have the following fields:

Field Name           Type
Month                Date (Calculation)
AgencyName           Text
Accounts             Count of Text field
TotalBalance         Sum of Double Field

[some other fields used for ordering correctly]

DatabaseGuy.. if you could expand on what you are talking about, I would appreciate it.

Gcomyn

 

[Databaseguy]

Go to the Micrsoft knowledgebase

http://search.support.microsoft.com/kb/c.asp?=0&SD=GN&LN=EN-US

and search for Q209143. This article descibes in detail exactly how to perform what you are trying to do. The only drawback is if you add/delete or change field names. But, that's another story...

 

[GComyn]

Databaseguy:

Much thanks!!!! That is (from what I've read so far) exactly what I'm looking for....except that I"m using Access97... I'm trying it now... It looks like it will work, just have to pare the initial queries down to what is absolutely necessary (it takes a while to add them.)

Thanks!!!!!!!

GComyn