Database - allocate software to device

[Robert Jacobs]

Thank you experts, in advance!

I am attempting to make an asset managing database for my personal use
within my company. The trillion Excel pages I have to update atm is
becoming ridiculous.

What I have:
A table with all employees' first and last name, division, department,
etc.
A table with all devices' serial number, type, version, etc.

Each device is linked to an employee... each employee can have
multiple devices.


What I need:
I would like to know the best way to assign software to these
devices. I would like to have a table of all of the software we own
(i.e. Microsoft Office Pro 2007 Open License) and the number of
licenses of that software we have purchased (i.e. 100 licenses), then
somehow assign those licenses one at a time to the devices they are
installed on... when the 100 licenses have been assigned, no more will
be available to assign to another device. I'd like to include the CD
key as well, but that's the simple part. We'd have to be able to
assign multiple types of software to each device, as well (i.e. a
computer could have MS Office as well as Lotus Notes...)

Does anybody know how I can accomplish this? I don't expect it to be
done for me (although that would be nice... any takers?), but maybe an
idea on what step to take next would be helpful!

Please note - I AM AN EXTREME NEWBIE TO ACCESS - this is my (count
'em) SECOND ever made database, so please don't go way too far over my
head.

Thanks again, experts!!!!

 

[Robert Jacobs]

Thank you experts, in advance!

I am attempting to make an asset managing database for my personal use
within my company.  The trillion Excel pages I have to update atm is
becoming ridiculous.

What I have:
A table with all employees' first and last name, division, department,
etc.
A table with all devices' serial number, type, version, etc.

Each device is linked to an employee... each employee can have
multiple devices.

What I need:
I would like to know the best way to assign software to these
devices.  I would like to have a table of all of the software we own
(i.e. Microsoft Office Pro 2007 Open License) and the number of
licenses of that software we have purchased (i.e. 100 licenses), then
somehow assign those licenses one at a time to the devices they are
installed on... when the 100 licenses have been assigned, no more will
be available to assign to another device.  I'd like to include the CD
key as well, but that's the simple part.  We'd have to be able to
assign multiple types of software to each device, as well (i.e. a
computer could have MS Office as well as Lotus Notes...)

Does anybody know how I can accomplish this?  I don't expect it to be
done for me (although that would be nice... any takers?), but maybe an
idea on what step to take next would be helpful!

Please note - I AM AN EXTREME NEWBIE TO ACCESS - this is my (count
'em) SECOND ever made database, so please don't go way too far over my
head.

Thanks again, experts!!!!

Oh, and I'm using MS Access 2003. Thanks!

 

[Robert Jacobs]

Thank you experts, in advance!

I am attempting to make an asset managing database for my personal use
within my company.  The trillion Excel pages I have to update atm is
becoming ridiculous.

What I have:
A table with all employees' first and last name, division, department,
etc.
A table with all devices' serial number, type, version, etc.

Each device is linked to an employee... each employee can have
multiple devices.

What I need:
I would like to know the best way to assign software to these
devices.  I would like to have a table of all of the software we own
(i.e. Microsoft Office Pro 2007 Open License) and the number of
licenses of that software we have purchased (i.e. 100 licenses), then
somehow assign those licenses one at a time to the devices they are
installed on... when the 100 licenses have been assigned, no more will
be available to assign to another device.  I'd like to include the CD
key as well, but that's the simple part.  We'd have to be able to
assign multiple types of software to each device, as well (i.e. a
computer could have MS Office as well as Lotus Notes...)

Does anybody know how I can accomplish this?  I don't expect it to be
done for me (although that would be nice... any takers?), but maybe an
idea on what step to take next would be helpful!

Please note - I AM AN EXTREME NEWBIE TO ACCESS - this is my (count
'em) SECOND ever made database, so please don't go way too far over my
head.

Wow.  Great description, especially for a newbie.

I did this exact thing a long time ago.  Here's the basic gist of what I did.

tblSoftwareTitle(SoftwareTitleID, SoftwareTitle, VersionNo, LicenseKey,
NumSeats)

tblComputer(ComputerSerialNo, Make, Model,...)

then the join table is
InstalledSoftware(isComputerSerialNo, isSoftwareTitleID, <optional stuff like
"date installed">)

Now you can find how many copies of each title are installed on any system.
to "allocate" software to a device, you would create a subform based on
InstalledSoftware, set the SoftwareTitleID up as a combobox and have it show
SoftwareTitleID and maybe [SoftwareTitle] & " v" & [VersionNo].  (basically
you would create a combobox and base the combo on a query (for the time being,
.. don't want to get too complicated.  Yet.)

Hope this gets you started.  If you want, I could slap this together and e-
mail it.  You *can* make this more complex (but more flexible), but that
requires more knowledge than I expect you have right now...

HTH,
Pieter

Pieter, thanks for your response! Well, I can kind of understand some
of what you're suggesting. Will this idea allow me to make some type
of pool of software, list the number of licenses available for that
software, and allocate those licenses one at a time? Also, with your
combo box idea, would I have to make however many combo boxes as
pieces of software I'd like to distribute? i.e. a Combo Box that I'd
fill with MS Office, another one for Lotus Notes, another for
AltiView, another for Visio, etc.?

I saw you offer up slapping together a basic example and e-mailing it,
that would be greatly appreciated! I don't really understand what a
JOIN table is, or how I would link them together (without disrupting
the employee to hardware tables, especially). I don't know if you can
see my e-mail, but it's:

r o b e r t j a c o b s i t @ g m a i l . c o m

(no spaces, of course)

Thanks again, a thousand times - I'd like to become proficient in all
things Access, but considering I can barely use Excel, and have only
ever made a few batch files in my life (and I have nobody to help me,
besides the internet, and the 2 minutes of free time I have per day),
I don't see that happening. Again, I appreciate it!

 

[KARL DEWEY]

Each device is linked to an employee... each employee can have multiple
devices.
How do you accomplish this? Do you put the EmployeeID in a field of the
device record or have an Assignment table like this --
tblAssigned --
AssignID - autonumber - primary key
EmpID - number - foreign key
DeviceID - number - foreign key AssignDate - DateTime
TurnInDate - DateTime
Condition - text
-- Use EmpID, DeviceID, & AssignDate to create a non-duplicate index.

Create a one-to-many relationship with Cascade Update & Referential
Integerity.

Use the same method for the software. One device to many software.

 

[Mark Andrews]

Robert,

Just a thought but asset management is covered by many software packages,
some are pretty inexpensive, some are very expensive.
For example my friend sells an asset management program in filemaker
http://www.chickengirl.com/

At a minimum you might want to look at some asset management packages to get
some ideas of
features/functionality you might like.

You can even get advanced and do things like "discovery" where the software
goes out and queries the machines on your network
to discover what software is installed.

Not altering your plans to build it yourself (always good to get another
Access developer on board), just think it might help to
look at the marketplace a bit. Give you some ideas for screen design
etc....

 

[Robert Jacobs]

devices.
How do you accomplish this?  Do you put the EmployeeID in a field of the
device record or have an Assignment table like this --
tblAssigned --
AssignID - autonumber - primary key
EmpID - number - foreign key
DeviceID - number - foreign key AssignDate - DateTime
TurnInDate - DateTime
Condition - text
   -- Use EmpID, DeviceID, & AssignDate to create a non-duplicate index.

Create a one-to-many relationship with Cascade Update & Referential
Integerity.

Use the same method for the software.  One device to many software.

I use EmployeeID (autonumber) in the Employee table, and EmployeeID
(number) with a 1 to many relationship in the Hardware table.

And I like assigning many pieces of software to one device, and I was
able to get that to work - the problem was licensing. If I have 150
licenses of a certain piece of software, I want to be able to define
that, then allocate those licenses to the devices, not just say "this
device has this software" without proving that we own that software to
begin with... that should allow me to keep up with how many available
licenses I have, and if I need to purchase more the next time the
renewal comes around.

Now, I think your second options was to create an assign table, but
you started losing me with foreign keys and non-duplicate index - but
it looks like it might be able to achieve what I'm looking for
here...? I'd need more info to do this (could prob. figure it out
eventually)... Sorry again for being so ignorant.

And thanks again, for responding!

 

[Robert Jacobs]

Robert,

Just a thought but asset management is covered by many software packages,
some are pretty inexpensive, some are very expensive.
For example my friend sells an asset management program in filemakerhttp://www.chickengirl.com/

At a minimum you might want to look at some asset management packages to get
some ideas of
features/functionality you might like.

You can even get advanced and do things like "discovery" where the software
goes out and queries the machines on your network
to discover what software is installed.

Not altering your plans to build it yourself (always good to get another
Access developer on board), just think it might help to
look at the marketplace a bit.  Give you some ideas for screen design
etc....

Thank you, Mark, for the advice. We actually have used TrackIT!
software in the past for this, as well as a couple freebies, but this
is kind of for a different purpose. The end result of this database
will be to list purchased software, hardware, etc., and print
documentation for the insurance companies each year with this info.
Microsoft has an asset database for free, but it only includes
hardware (and automobiles, etc.), and not software - nor a way to
assign anything to those devices... And I am attempting to make a
free database for my own use - not really company sponsored, if you
know what I mean - all done in my free time... I just want something
better than this Excel form I inherited that was created years ago. I
think if I can get this software pool and allocation working in this
DB, it will fit my needs perfectly, for what I'm doing any way - I
don't really want something that fits everybody else's needs, or
something with a thousand options that I won't use. I want a basic
input and output DB for me to enter employees and their devices into,
and assign them software that's in a separate table (multiple pieces
of software, and count down the licenses, so I can't assign a piece of
software to somebody if we don't own any more licenses for it...)

Thanks for your words, and I will continue to look everywhere I can
for good resources, but if not free, I'd better figure this thing
out... ha ha. Thanks again!

 

[Robert Jacobs]

Robert,

Just a thought but asset management is covered by many software packages,
some are pretty inexpensive, some are very expensive.
For example my friend sells an asset management program in filemakerhttp://www.chickengirl.com/

At a minimum you might want to look at some asset management packages to get
some ideas of
features/functionality you might like.

You can even get advanced and do things like "discovery" where the software
goes out and queries the machines on your network
to discover what software is installed.

Not altering your plans to build it yourself (always good to get another
Access developer on board), just think it might help to
look at the marketplace a bit.  Give you some ideas for screen design
etc....

Thank you, Mark, for the advice. We actually have used TrackIT!
software in the past for this, as well as a couple freebies, but this
is kind of for a different purpose. The end result of this database
will be to list purchased software, hardware, etc., and print
documentation for the insurance companies each year with this info.
Microsoft has an asset database for free, but it only includes
hardware (and automobiles, etc.), and not software - nor a way to
assign anything to those devices... And I am attempting to make a
free database for my own use - not really company sponsored, if you
know what I mean - all done in my free time... I just want something
better than this Excel form I inherited that was created years ago. I
think if I can get this software pool and allocation working in this
DB, it will fit my needs perfectly, for what I'm doing any way - I
don't really want something that fits everybody else's needs, or
something with a thousand options that I won't use. I want a basic
input and output DB for me to enter employees and their devices into,
and assign them software that's in a separate table (multiple pieces
of software, and count down the licenses, so I can't assign a piece of
software to somebody if we don't own any more licenses for it...)

Thanks for your words, and I will continue to look everywhere I can
for good resources, but if not free, I'd better figure this thing
out... ha ha. Thanks again!

 

[KARL DEWEY]

If I have 150 licenses of a certain piece of software, I want to be able to
define
that, then allocate those licenses to the devices, not just say "this device
has this software" without proving that we own that software to begin with...
that should allow me to keep up with how many available licenses I have,

Assign a local serial number to each license copy.
Create a table named CountNumber with field named CountNUM containing number
from 0 (zero) through your maximum spread. The query below generates a
serial number record for each license copy.
INSERT INTO YourTable ( SN )
SELECT [Enter Starting Serial #]+[CountNUM] AS Expr1
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter qunaity of records]-1));

Reference the textboxes on the form instead of [Enter Starting Serial #]
and [Enter qunaity of records] to enter data from form. Add your other
fields that define the software - Name, Version, etc.
A foreign key is the 'many' side number in a one-to-many relationship
matching the primary key field.
The index I mentioned is where you use multiple fields for a single index,
no duplicates, so that the combination of the fields are unique. A license
issued to a device on a date becomes unique.

 

[Robert Jacobs]

define
that, then allocate those licenses to the devices, not just say "this device
has this software" without proving that we own that software to begin with...
that should allow me to keep up with how many available licenses I have,

Assign a local serial number to each license copy.
Create a table named CountNumber with field named CountNUM containing number
from 0 (zero) through your maximum spread.  The query below generates a
serial number record for each license copy.
INSERT INTO YourTable ( SN )
SELECT  [Enter Starting Serial #]+[CountNUM] AS Expr1
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter qunaity of records]-1));

Reference the textboxes on the form instead of  [Enter Starting Serial #]    
and   [Enter qunaity of records] to enter data from form.  Add your other
fields that define the software - Name, Version, etc.

A foreign key is the 'many' side number in a one-to-many relationship
matching the primary key field.  
The index I mentioned is where you use multiple fields for a single index,
no duplicates, so that the combination of the fields are unique.  A license
issued to a device on a date becomes unique.

--
Build a little, test a little.

I use EmployeeID (autonumber) in the Employee table, and EmployeeID
(number) with a 1 to many relationship in the Hardware table.

And I like assigning many pieces of software to one device, and I was
able to get that to work - the problem was licensing.  If I have 150
licenses of a certain piece of software, I want to be able to define
that, then allocate those licenses to the devices, not just say "this
device has this software" without proving that we own that software to
begin with... that should allow me to keep up with how many available
licenses I have, and if I need to purchase more the next time the
renewal comes around.

Now, I think your second options was to create an assign table, but
you started losing me with foreign keys and non-duplicate index - but
it looks like it might be able to achieve what I'm looking for
here...? I'd need more info to do this (could prob. figure it out
eventually)...  Sorry again for being so ignorant.

And thanks again, for responding!
.

Wow - thanks. I'll give some of this a try. Appreciate all your help!