Showing results for 
Show  only  | Search instead for 
Did you mean: 
Champion Sweeper
Need a query thats a bit above me.

I need a report that shows all people who have software X and what version of Y. Y can have multiple versions.

tblSoftware.softwareName = 'Y' AND
(tblSoftware.softwareName LIKE 'YA%' OR tblSoftware.softwareName LIKE 'YB%' OR tblSoftware.softwareName LIKE 'YC%' OR tblSoftware.softwareName NOT LIKE 'YD%')

for example (I know thats valid code, just an example of what I'm looking for).

I don't need to display software X in the ourput, just the version of Software Y.

X=MP2 and Y=MS Office versions (XP, 2003, 2007). So if they show up on the report, they will have MP2 and it will display the Office version.

If you can get me a small template I can do the rest.
Lansweeper Alumni
I tested it on the v4 beta, maybe that's the problem.
Champion Sweeper
Very intresting way to make 2 tables like that. That will come in handy, thanks!

However it still wasn't working. I had to load the old report builder and I figured out the code I needed.

tblSoftware.softwareName AS software1,
tblSoftware1.softwareName AS software2,
tblSoftware1.softwareVersion AS version2
INNER JOIN tblSoftware ON (tblComputers.Computername = tblSoftware.ComputerName)
INNER JOIN tblSoftware tblSoftware1 ON (tblSoftware1.ComputerName = tblSoftware.ComputerName)
tblSoftware.softwareName = 'MP2 6.0 Oracle Edition' AND
(tblSoftware1.softwareName LIKE 'Microsoft Office Professional%' OR
tblSoftware1.softwareName LIKE 'Microsoft Office Standard%' OR
tblSoftware1.softwareName LIKE 'Microsoft Office 2000%' OR
tblSoftware1.softwareName LIKE 'Microsoft Office XP%')


EDIT: I had no idea that I could use () like that, I just happened to use them in my example to explain what I wanted, not knowing thats how to do it 🙂
Lansweeper Alumni
try this:

Select tblComputers.Computername, tblSoftware.softwareName As software1,
tblSoftware1.softwareName As software2, tblSoftware1.softwareVersion
As version2
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
tblSoftware tblSoftware1 On tblSoftware1.ComputerName =
Where tblSoftware.softwareName = 'Microsoft office xp professional' And
tblSoftware1.softwareName Like 'adobe%'