Count based on multiple variables held in another table

[Entropy]

Hi everyone, this is my first post and I hope I have not broken any forum rules. I have had a search of the previous posts but cannot find anything that matches my problem.

I'm fairly competent on Excel and need to migrate my spread sheets to a database due to size (maybe my Excel thinking is the cause of the problem). However I am new to Access and stuck with how I can tackle this problem.

I want to run a count on a query but the count is based on a list of variables in another table.

The main query is 2 column list with PC name in col1 and the discovered software in col 2. Each app found is listed on a separate line so there are a number of lines with the same machine name and all apps found.

E.G. QUERY1
PC1 Access2010
PC1 Office2010
PC1 Excel2010
PC1 Visio2010
PC2 Office2010
PC2 Excel2010
PC2 Powerpoint2010

The counts that I want to do is count the machines with App1 ONLY installed, Machines with APP1 AND APP2 installed etc. The Boolean logic is AND, OR, NOT and ONLY. This data is held in a table that I call my 'Rules Table'

E.G. TABLE 2
App1 Access2010 AND Office2010


App2 Office2010 ONLY
App3 Excel2010 OR Office2010
App4 Visio2010 NOT Powerpoint2010

I have managed to get to count by the value in column 2 of the table i.e. Access, Office, Excel and Visio, but cannot see how to use the logical value in Column 3 and the additional search value in Column 4.

Any advice on how I can tackle this would be greatly appreciated and I hope this makes sense.

[June7]

This criteria requires each record to consider data in other records of same table - never easy. Requires multiple queries (or nested subquery) or domain aggregate (DLookup, DSum, DCount, etc). Maybe even a VBA custom function.

Your logical phrases won't work because the syntax has to be:

[App]="Access2010" OR [App]="Office2010" - Build a query with the query designer and you will see that structure in the SQL View window. The AND operator won't work because no record can meet the condition.

Criteria using IN is possible: [App] IN ("Access2010", "Office2010")

Neither of which consider the ONLY requirement.

What you want will probably require multiple queries to accomplish.

I would suggest starting with a CROSSTAB but need 3 fields for that. A CROSSTAB can be mimicked with expressions in an aggregate (GROUP BY) Totals query.
SELECT PCID, Sum(IIf([app]="Access",1,0)) AS Access, Sum(IIf([app]="Excel",1,0)) AS Excel, Sum(IIf([app]="Visio",1,0)) AS Visio, Sum(IIf([app]="Powerpoint",1,0)) AS PP, Sum(IIf([app]="Office",1,0)) AS Office
FROM Table1
GROUP BY PCID;

Now use that query as the data source for further analysis. Query for all pc's with only Access and Excel:

SELECT * FROM query1 WHERE Visio=0 AND PP=0 AND Office=0;

etc.

The alternative might be a VBA function called from query.

[Entropy]

Thanks for the prompt reply. I had something similar

Select count (distinct PCID) from Query1 where installed = ‘Office2010’ and PCID NOT IN (select PCID from Query1 where installed = ‘Access’);

However the query is 800,000 lines and the Table is 290 lines (hence the move to Access) so its unfeasible to create a hard coded SQL call for each line in the Table. I was wondering if there was a more dynamic way that the counts count be run. At the moment it feels that I will need to read up on my VBA for Access.

[June7]

Don't understand what you mean by 'the query is 800,000 lines and the Table is 290 lines'. The table of PC/apps is 800,000 records? Table2 of the criteria is 290 records? How many apps are you tracking?

VBA can alter a query object to dynamically modify filter criteria. However, the syntax I showed is still required.