Head Scratcher.

[Shido151]

Doing a Windows 7 Roll out and we sent a web form for every employee to inventory the software, well people are rquesting alot of software we don't have license on file for.

I have a DB of Employees Requesting Software, and Another DB of the Software Employees have licenses for, and I need to match the two together somehow.

Problem is The Employees were able to type in the software request in the web form. The License DB looks nice and neat

Name	ID #	Software	Manufacture	Version
Smith, Ronald	3232	Acrobat Pro	Adobe	7
Rooney, Mitch	33312	Acrobat Pro	Adobe	3.1
Rooney, Mitch	33312	Femap	MSC	10.2
Rooney, Mitch	33312	Record Now	Nero	8
Clarck, John	5642	FireWorks	Adobe	4

And My Requested Software looks DB like this

Name	ID #	Reqest
Smith, Ronald	3232	Adobe Acrobat / Version: 7;
Rooney, Mitch	33312	Record Now / Version: V. 6;
Rooney, Mitch	33312	Adobe Acrobat (standard) / Version: 8;
Rooney, Mitch	33312	Roxio Creater Business / Version: 10.3;
Rooney, Mitch	33312	Snagit / Version: 10;
Clarck, John	5642	Fireworks 4 / Version: ;
Clarck, John	5642	FACET3D / Version: Latest;
Clarck, John	5642	EasyFit / Version: 3.3;
Clarck, John	5642	Brava Reader / Version: 7.1;

I have to figure out a way to match the software we have a license for on file and weed out all the Misc request for request for software the user owns seperately. I can match the query up via The ID # but when I do that I get every request matched with repeating licence. Any ideas?

[Rawb]

I'm assuming the ID refers to the USER's ID and not a software/program ID?

One thing you could do which might help some is to split out the Software and Version info in the Requests:

SELECT 
  Trim(Left([Request], InStr([Request], "/") - 1)) AS [Software], 
  Trim(Mid([Request], InStr([Request], ":") + 1, Len([Request])) AS [Version]
FROM
  [Requested Software];

Note: I just threw the Query together so there may be a few typos in it. If you can't get it to work but you think the idea might help, just lemme know and I'll make sure it works!