A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Database - allocate software to device



 
 
Thread Tools Display Modes
  #1  
Old May 5th, 2010, 08:00 PM posted to microsoft.public.access.forms
Robert Jacobs
external usenet poster
 
Posts: 13
Default Database - allocate software to device

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!!!!
  #2  
Old May 5th, 2010, 08:01 PM posted to microsoft.public.access.forms
Robert Jacobs
external usenet poster
 
Posts: 13
Default Database - allocate software to device

On May 5, 2:00*pm, Robert Jacobs wrote:
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!
  #3  
Old May 5th, 2010, 09:19 PM posted to microsoft.public.access.forms
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default Database - allocate software to device

Robert Jacobs wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #4  
Old May 5th, 2010, 09:41 PM posted to microsoft.public.access.forms
Robert Jacobs
external usenet poster
 
Posts: 13
Default Database - allocate software to device

On May 5, 3:19*pm, "PieterLinden via AccessMonster.com" u49887@uwe
wrote:
Robert Jacobs wrote:
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

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1


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!
  #5  
Old May 5th, 2010, 09:43 PM posted to microsoft.public.access.forms
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Database - allocate software to device

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.

--
Build a little, test a little.


"Robert Jacobs" wrote:

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!!!!
.

  #6  
Old May 5th, 2010, 09:58 PM posted to microsoft.public.access.forms
Mark Andrews[_4_]
external usenet poster
 
Posts: 169
Default Database - allocate software to device

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....

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com


"Robert Jacobs" wrote in message
...
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!!!!


  #7  
Old May 5th, 2010, 10:07 PM posted to microsoft.public.access.forms
Robert Jacobs
external usenet poster
 
Posts: 13
Default Database - allocate software to device

On May 5, 3:43*pm, KARL DEWEY
wrote:
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.

--
Build a little, test a little.

"Robert Jacobs" wrote:
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!!!!
.


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!
  #8  
Old May 5th, 2010, 11:51 PM posted to microsoft.public.access.forms
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Database - allocate software to device

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.

you started losing me with foreign keys and non-duplicate index

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.


"Robert Jacobs" wrote:

On May 5, 3:43 pm, KARL DEWEY
wrote:
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.

--
Build a little, test a little.

"Robert Jacobs" wrote:
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!!!!
.


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!
.

  #9  
Old May 6th, 2010, 01:46 PM posted to microsoft.public.access.forms
Robert Jacobs
external usenet poster
 
Posts: 13
Default Database - allocate software to device

On May 5, 5:51*pm, KARL DEWEY
wrote:
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.

you started losing me with foreign keys and non-duplicate index


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.

"Robert Jacobs" wrote:
On May 5, 3:43 pm, KARL DEWEY
wrote:
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.


--
Build a little, test a little.


"Robert Jacobs" wrote:
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!!!!
.


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!
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.