Crosstab(?) list of software installed

[UTMonkey]

Hi All,



Hope you can help, I currently have a table with the following columns

PC, Software Installed

So the data in the table may look like this.

 
PC1, MS Access
PC2, MS Access
PC2, Excel
PC3, MS Access
PC2, Outlook
PC3, Ladyboys of Bangkok screen saver.
PC3, Outlook

I would like to be able to do the following, first -

Produce a query which looks like the following

 
                                                  PC1     PC2       PC3
Excel                                                     [tick]
Outlook                                                   [tick]   [tick]
Ladyboys of Bangkok screen saver                          [tick]
MS Access                                        [tick]   [tick]   [tick]

And then, which would be really cool for the query to sort by the software that installed on the most PC's - see below

 
                                                  PC1     PC2       PC3
MS Access                                        [tick]   [tick]   [tick]
Outlook                                                   [tick]   [tick]
Excel                                                     [tick]
Ladyboys of Bangkok screen saver                          [tick]

The best I can do with Crosstab is to generate data with the PC names and the software for the rows - no problem, but the problem is a) how to change the data in the crosstab to a "tick" and b) how to sort by most popular software.

Hope you can help, thanks in advance.

Regards

[ajetrumpet]

wow, what's with the cross posts today! This is like #9 I've seen. ridiculous... Hey Matrix, where are you when we need you!?

I don't think you can get your TICKS with any query in Access. Sorry. I'm always wrong though, so that may not have any value.

I'd evaluate the reason why you're doing it. Just to be cool? Or cuz it's required?

[UTMonkey]

Sorry, desperate times.

Ok, forget the ticks - how about a yes/no?

Mark

p.s. it's required.....

[ajetrumpet]

Mark,

you CAN'T change a field type in a query. You simply cannot do it. you can change a DATA type, but you can't change a field type.

[June7]

What is 'tick'? The crosstab can give you a count of each software under each PC. This is not what you need?

Okay, I get it, each pc should have have only one copy of any software so should total 1 or 0. The crosstab will do that.

The crosstab can include a column that adds the other columns. Can't sort on this aggregate calc column but can build another query that uses the crosstab and sort on the column.

Can have expressions in the second query or in textboxes on form or report display whatever you want for the 0 and 1 values:
Comp1: IIf(PC1=0,Null,"tick")