Am I making this more complicated than it has to be?

[Guest]

Here's my data:

Workstation ID Product Name Version
87878787 Word 2000
87878787 Excel 2000
99999999 Word 2000
77777777 Excel 2000
77777777 Powerpoint 2000
66666666 Word 2000

And here's what I'm trying to get from that:

There's one workstation that has Word & Excel, two have Word only and one
has Excel & Powerpoint.

I need to find the count of workstations for each possible combination of
products (there are 8 different products and each one has multiple version
possibilties).

Any help would be greatly appreciated!!

Lauri S

 

[Michael Gramelspacher]

Here's my data:

Workstation ID Product Name Version
87878787 Word 2000
87878787 Excel 2000
99999999 Word 2000
77777777 Excel 2000
77777777 Powerpoint 2000
66666666 Word 2000

And here's what I'm trying to get from that:

There's one workstation that has Word & Excel, two have Word only and one
has Excel & Powerpoint.

I need to find the count of workstations for each possible combination of
products (there are 8 different products and each one has multiple version
possibilties).

Any help would be greatly appreciated!!

Lauri S

Is this what you want?

SELECT a.ProductName,
a.ProductVersion,
COUNT(* ) AS [Product Total]
FROM WorkstationProducts AS a
GROUP BY a.ProductName,a.ProductVersion;

ProductName ProductVersion Product Total
Excel 2000 2
Powerpoint 2000 1
Word 2000 3

 

[Guest]

Is this what you want?

SELECT a.ProductName,
a.ProductVersion,
COUNT(* ) AS [Product Total]
FROM WorkstationProducts AS a
GROUP BY a.ProductName,a.ProductVersion;

ProductName ProductVersion Product Total
Excel 2000 2
Powerpoint 2000 1
Word 2000 3

Nope. It doesn't tell me that one computer had both Word & Excel installed
and one had only Word.

The results I'm looking for would be something like this:

Count of Workstations Word 2000 Excel 2000 PowerPoint 2000
2 X
1 X
X
1 X X

Lauri S.

 

[John Spencer]

This looks like you may need a crosstab query to get the data into a format
you want and then use the crosstab as the basis of another query.
Unfortunately you would need to know the fields returned by the crosstab to
build the second query.

Another method would be to use Duane Hookom's Concatenate function -
probably very slow.

SELECT Count(WorkStationID) as the count
, Concatenate("SELECT ProductName & "" "" & Version FROM YourTable WHERE
WorkstationID = " & YourTable.WorkStationID & " ORDER BY ProductName,
Version") as ListSoftware
FROM YourTable
GROUP BY Concatenate("SELECT ProductName & "" "" & Version FROM YourTable
WHERE WorkstationID = " & YourTable.WorkStationID & " ORDER BY ProductName,
Version")

To get the concatenate function go to
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
and download the sample database.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Darhl Thomason]

Nope. It doesn't tell me that one computer had both Word & Excel
installed
and one had only Word.

The results I'm looking for would be something like this:

Count of Workstations Word 2000 Excel 2000 PowerPoint 2000
2 X
1 X
X
1 X X

Lauri S.

Maybe you could do it with a binary string representation?

Example:

Let's say for argument you have 8 products/versions.
Position in binary = product/revision
1 = Word 2k
2 = Excel 2k
3 = PowerPoint 2k
4 = Access 2k
5 = Word 97
6 = Excel 97
7 = PowerPoint 97
8 = Access 97

You could continue this on for as many products/revisions you have.

Then when you check to see if a product is installed it changes the flag for
it's position from 0 to 1. So your binary string for Word 2k & Excel 2k is
11000000.

If a workstation has Word 2k & PowerPoint 2k the binary string is 10100000.

Then store this string in your workstation record for future reference (or
however you're doing that). For example, if your workstation record shows
the software flag to be 01111011, it would mean this workstation does not
have Word 2k or Excel 97, but does have Excel 2k, PowerPoint 2k, Access 2k,
Word 97, PowerPoint 97, & Access 97.

Then do a count of how many records have the same software flag and display
that.

Of course, you could store these binary strings as numbers if you wanted to,
binary 11000000 is decimal 192, binary 10100000 is decimal 160, binary
01111011 is decimal 123...any decimal number can be converted into a binary
number, then use the binary on/off to represent if the software is installed
or not.

HTH

Darhl