Restricting counts using a table of equivalences

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm writing a query that utilises tables in the Track-It database.

In Track-It the software installed on each PC is listed in a big table called "ITEMS". Each line lists the product name and the workstation number that the product is installed on, eg.
PRODUCT.............................................................WS_NUM (workstation number)
WINDOWS 2000.... ... ... ... 118
WINDOWS 2000 SP2 ... ... ... 118
WINDOWS 2000 SP4 ... ... ... 118
WINDOWS 2000.... ... ... ... 341
etc

I have created a table that links into this (using the PRODUCT name - there are no primary keys in Track-It tables) to represent software equivalences, so that any entry of say, WINDOWS 2000 or its service packs is listed as belonging to the WINDOWS 2000 license, eg.
PRODUCT..................................................................EQUIVALENCE
WINDOWS 2000... ... ... ... WINDOWS 2000
WINDOWS 2000 SP2 ... ... ... WINDOWS 2000
etc

Now, I want to count the number of Windows 2000 licenses installed using some interaction of these two tables, but I don't want to double count. So for example, I don't want a PC installation with W2K and 2 service packs (such as WS_NUM 118 above) to be counted as 3 licences but only as 1. I can't figure out how to have the query count only one instance where there are multiple entries.

I'm quite stumped, as every query I try to design counts all instances in the ITEM table, thus producing some large overcounts of the actual licences used. Can anyone suggest anything? I've tried the "Unique Values/Unique Record" settings in the query properties, but no luck.
 
strSQL = "SELECT DISTINCT ITEM, etc"

Use DISTINCT
-----Original Message-----
I'm writing a query that utilises tables in the Track-It database.

In Track-It the software installed on each PC is listed
Click to expand...
in a big table called "ITEMS". Each line lists the product
name and the workstation number that the product is
installed on, eg.
PRODUCT...................................................
Click to expand...
...........WS_NUM (workstation number)
WINDOWS 2000.... ... ... ... 118
WINDOWS 2000 SP2 ... ... ... 118
WINDOWS 2000 SP4 ... ... ... 118
WINDOWS 2000.... ... ... ... 341
etc

I have created a table that links into this (using the
Click to expand...
PRODUCT name - there are no primary keys in Track-It
tables) to represent software equivalences, so that any
entry of say, WINDOWS 2000 or its service packs is listed
as belonging to the WINDOWS 2000 license, eg.
PRODUCT................................................... ................EQUIVALENCE
WINDOWS 2000... ... ... ... WINDOWS 2000
WINDOWS 2000 SP2 ... ... ... WINDOWS 2000
etc

Now, I want to count the number of Windows 2000 licenses
Click to expand...
installed using some interaction of these two tables, but
I don't want to double count. So for example, I don't want
a PC installation with W2K and 2 service packs (such as
WS_NUM 118 above) to be counted as 3 licences but only as
1. I can't figure out how to have the query count only one
instance where there are multiple entries.
I'm quite stumped, as every query I try to design counts
Click to expand...
all instances in the ITEM table, thus producing some large
overcounts of the actual licences used. Can anyone suggest
anything? I've tried the "Unique Values/Unique Record"
settings in the query properties, but no luck.
 
Can you post the SQL of your query? I think what you need is to show only the
WS_Num and the Equivalence value in a distinct query and then use that to get
your counts

SELECT Distinct Items.WS_Num, YourTable.Equivalence
FROM Items INNER JOIN YourTable
ON Items.Product = Equivalence.MatchValue

Now you can call that query.

SELECT QryOne.Equivalence, Count(QryOne.WS_Num)
FROM QryOne

It later versions of Access you can probably combine this all into one query.
 
John
Thanks so much! That all worked a treat. I had to tweak the second query a bit, but otherwise you've saved me from scratching a hole in my head.
Cheers
Andrew
 
Back
Top