Counting # of occurences

[iTanas]

Hi, I have 3 tables: One is a list of softwares, another is list of
workstations, a 3rd one is a relationship between the two that looks like:

Computer1: SoftwareA
Computer1: SoftwareB
Computer1: SoftwareD
Computer2: SoftwareA
Computer2: SoftwareB
Computer3: SoftwareC
Computer3: SoftwareD

How would you just keep a count how many SoftwareAs there are total, because
I'd then like to create another table that has that same software list and "#
of Licenses" column so that I can compare and report the "# of Licenses" to
#" of installations" of SoftwareA and Bs etc..? Thanks.

 

[Joshua A. Booker]

iTanas,

Try adding a licenses field to the software table to track how many you own.
How many you have installed can be calculated from the relationship table
using the count operator in a totals query. It's best to use a totals query
so you don't have to update the installed count every time you add a record
in the relationship table.

HTH,
Josh

 

[Douglas J Steele]

Sorry, Joshua, but that's not a good idea. You should never store calculated
values.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

It's pretty simple to create a Totals query that will always give you the
correct values.

The SQL would look like:

SELECT SoftwareName, Count(ComputerName) As NumberOfInstallations
FROM MyTable
GROUP BY SoftwareName

 

[Joshua A. Booker]

Doug,

My suggestion was to use a totals query and not to store the calculated
value. The Licenses field is is so they can compare the quantity owned with
the calculated quantity installed.

Josh

 

[Douglas J Steele]

Sorry, you're right: you did suggest a totals query for the number of
installations.

Depending on how the licenses are obtained, though, it may also be necessary
to track the details for them, in which case you'd use a totals query for
them as well. I know that we have many different types of licenses for a
single product. For instance, we may still be using Access 97, but can no
longer purchase licenses for Access 97. Instead, we have to purchase a
license for the current version of Access, and Microsoft lets us apply it to
an installation of Access 97. That means that we may have 40,000 licenses
for Access 97, 20,000 for Access 2000, 10,000 for Access 2002 and 10,000 for
Access 2003. It's important we know the differences, since it can impact the
cost of converting everyone to Access 2007 when it comes out.

 

[John Vinson]

Hi, I have 3 tables: One is a list of softwares, another is list of
workstations, a 3rd one is a relationship between the two that looks like:

Computer1: SoftwareA
Computer1: SoftwareB
Computer1: SoftwareD
Computer2: SoftwareA
Computer2: SoftwareB
Computer3: SoftwareC
Computer3: SoftwareD

How would you just keep a count how many SoftwareAs there are total, because
I'd then like to create another table that has that same software list and "#
of Licenses" column so that I can compare and report the "# of Licenses" to
#" of installations" of SoftwareA and Bs etc..? Thanks.

You don't need or want a new table - you can get this count "on the
fly" using a Totals query. Create a Query based on the table; make it
a Totals query by clicking the Greek Sigma icon (like a sideways M);
and select the SoftwareID field TWICE (don't select the computer field
at all).

Group By one of the SoftwareID fields, and use Count as the Totals
operator for the other.

You can base a Form for onscreen display, or a Report for printing, on
this Query.

John W. Vinson[MVP]