Web Development

Ask something
Discussion Locked

Calculations in Access 2000

0 helpful
5 replies

by gallold

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have created a database to track inventory control. One form accepts input about a specific computer, which then links to another form to enter the software on that computer. This information is linked together and stored in two tables. I have another table that keeps track of all company software purchased with the number of copies purchased and the number of licenses available and used for that product.

I am looking for a automatic way to add/delete to the number of copies/licenses used when a specific software is added or deleted from the computer database. So, when I add "Software 1" to a specific computer, the number of used licenses increments by one. Are there any suggestions on how to do this?

5 replies

by mark.thomson

Try a query something like

SELECT SWname, count(SWname) as numlicences FROM ComputerSWTable Group BY SWName

by andrew

It sounds like you need a table to track the number of available/used licenses. All of this information is probably somewhere in the 3 tables you have, but not in a readily accessible format.

Describing an ERD without pictures is tough, but I'll try:

I envision 3 tables total:

Computer:
ComputerID (PK)
ComputerName

SoftwareInventory:
SoftwareID (PK)
SoftwareName
NumberLicensesPurchased
NumberLicensesRemaining

ComputerSoftware:
ComputerID (FK)
SoftwareID (FK)
DateInstalled

In this model ComputerSoftware has one row for each piece of software for each computer. When you add a row to this table, you can update SoftwareInventory, decrementing the NumberLicensesRemaining counter. If more licenes are purchased, both counter get incremented.

Now, storing calculated info such as this usually a no-no, since you can retrieve it with this query:

NumberLicensesRemaining = NumberLicensesPurchased - (SELECT Count(*) FROM ComputerSoftware WHERE SoftwareID = @whatever)

Hope this helps.

This discussion is closed to new replies.