Optimal Applications List - Difficult Problem

[redandblack2533]

Hello Everyone-

I'm a bit of a novice when it comes to Access and I have a bit of a tricky problem that I could use some help with.

In a particular environmnet, there are approximately 5,000 applicatoins and 120,000 PCs. A given PC could have any mix of applications installed on it (on average a PC has about 100 applications).

My database is as follows:

5 fiels: ID # (Primary key), ComputerID, ApplicationID, ApplicationName, ApplicationVersion

There are approximately 12 million records.

So, if my PC (ComputerID=99) is one of the 120k in the environemt, and it has 3 applications (Internet Explorer, Outlook, QuickTime) the records for my PC would look something like this:

ID # ComputerID ApplicationID ApplicationName ApplicationVersion
1 99 0001 Internet Explorer 1.0
2 99 0002 Outlook 2.0
3 99 0003 QuickTime 3.0

What I need to get is the smallest list of applications that accounts for ALL application installs on 60,000 of the PCs.

Obviously, ComputerID and ApplicationID are the fields that I need to be working with, but while I can grasp the basic logic of how to solve the problem I'm struggling to leverage Access to apply it.

Any help would be appreciated.



Thanks!

[June7]

Not sure what you mean by 'smallest list of applications'. You want a list of application names that have been installed? Something like:

SELECT DISTINCT ApplicationName FROM tablename WHERE Not ComputerID Is Null;

[redandblack2533]

So the list that I'm trying to get is one that contains every applicatoin installed on 60,000 of the PCs in the environment.

Let's call it List A and when every one of a given PC's applications is on List A, then that PCs is "covered".

Suppose that 5 PCs in the environment only have 1 application installed, Internet Explorer.

So I could take Internet Explorer and put it on List A, then 5 PCs in the environment would be covered. Certainly IE is installed on other PCs as well, but the rest are not covered because they also have other applications installed besides IE.

Next, suppose that 10 PCs in environment have only two applications installed, Internet Explorer and Outlook. By adding Outlook to List A, I would now have 15 PCs covered: the original 5 that were covered by only IE, and the additional 10 that are covered by both IE and Outlook.

I need to repeat this process until I have 60,000 PCs covered. I need for the list to be optimal in that it coveres 60k PCs with the smallest possible list of applications.

[June7]

So the list that I'm trying to get is one that contains every applicatoin installed on 60,000 of the PCs in the environment.

Maybe your requirement is just way over my head because after that, your description makes no sense to me.

Who manages 120,000 computers? Why are you interested in only 60,000 for this requirement? How do you know which 60,000?

If the query I suggested does not address this requirement, maybe someone else will have a better understanding and offer idea. Possibly what you want will require VBA.

[orange]

I have the same basic misunderstanding of your requirement as June7. However,if you consider 3 tables:

Computer (CompId, Location....other PC info)
Application(AppId, AppName, AppVersion ..other App specific fields) You may have to separate out Version???
CompHasApp(CompAppId, CompId(FK), AppID (FK) where CompId +AppId is composite unique index also, and any other specific CompHasApp fields (possibly a RecordDate...)

Perhaps your query or question comes down to a Count of PCs with X or more applications installed??? Just a guess