'Matrix' style reports

[scaifea]

OK, firstly I feel so dumb for needing to ask this ... but here goes ....

I have 1 table - one field inside that table is dynamic depending on other fields in the table, but thats updated easily *one point to me*

The dynamic field can be set to one of nine indicators (R1, R2, R3, P1, P2, P3, A1, A2 and A3).

I want to show the customer names against each of these indicators in a matrix style report

example:

_____________
| R3 | R2 | R1 |
---------------
| P3 | P2 | P1 |
---------------
| A3 | A2 | A1 |
---------------

How on earth do I do it ? I know the answer will be simple, and I'll kick myself, but please someone just help me.

All I need is the methodology, and templets etc would be a bonus.

Thanks in advance

 

[mark curtis]

use a crosstab query.

how did you create the one field inside a table which is dynamic depending on other fields in the table?

 

[scaifea]

Ta

Thanks for that I'll have a look at using cross-tab, its probably the only thing I didn't look at yesterday ;-)

The field is updated using a simple 'update table' query ...

[UPDATE [Tier 1 Accounts] SET [Tier 1 Accounts].RPA = "R1"
WHERE ((([Tier 1 Accounts].[2003 Billed])>300000) AND (([Tier 1 Accounts].[ntl % of Customer Spend (%)])>=0.6));]

Cheers

 

[scaifea]

STUCK!!!!

OK, I am really stuck, and I need some help.

Please find my problem here

If it makes sense, I would appreciate a reponse from one of ypou experts.

Cheers all

 

[intrep11]

Any one got any answer on this one

Has any one got any idea how to do this as it is something that i am going to have to try and achieve and i have no idea how to do it

and the books i have dont really help

in my case it will be like this

accident location

month accident code

any advice greatly recieved
thanks nd

 

[Rich]

Post a 97ver of your db here and someone will take a look at it for you

 

[scaifea]

Thanks

Great, thanks for the offer.
Stripped down and zipped mdb attached

 

[intrep11]

thanks

there isnt much need to post the whole DB all the data on accidents is stored in one table called tbldata (and it already has 10 reports and is too big)

which has the following headings

ID (primary key)
IDlocal(indexed)
shipname
date of report
accident type (a-q)
brief info (a short field to give me infomation)

what i am trying to achieve is this (attached jpg)
where the number is the date it happened and the letter the incident type
its v anoying cause i made it work in excel with out hassle
any guidance on what to do and where to head is much appreciated (i am not asking for someone to do this for me but to educate me on how to do it myself)

 

[scaifea]

Thanks

Great, thanks for the offer.
Due to file size restrictions, would you accept this zipped xls data ?

 

[Rich]

Pleas both post your db's with reports etc removed and a small amount of data to work with, trying to do this from data in a spreadsheet is not possible

 

[scaifea]

Here it is

Here it is ...

 

[intrep11]

ok

thanks

have taken out all but one report and left 10 dummy entries in the tbldata section

 

[Pat Hartman]

Crosstabs can only pivot a single column at a time. Since you have two columns, you will need to create two crosstabs and join them.