I am looking for the best starting point to create a database to track IT
software and Hardware

In my ideal world it would print off reports stating which users use which
computer, Which computers have which software, and which users use which
software and probably a mix of all 3. would probably also be helpful if I
could include asset numbers for all our IT hardware.

Currently all this info is in rather unmanageable spreadsheets. I have a
working (and rapidly expanding) knowledge of Access and am quite happy to
read reference materials and follow suggestions.

Any advice/ help/ guidance would be greatly appreciated.

We would also be looking at another one to track HR stats and leave bookings
to simplify reporting processes.

Thanks in advance

Hi Nadihaha

Would start off with the tables.  Probably users, equipment and a usage
table that lists the user number and the equipment number.  Since you want to
do a HR database later, you can use the same table for users.

Next I would create a query that listed all equipment by user.  From there
you can create a form based on the query using the wizard.  By that stage you
will have some skills and can then look at equipment for each user perhaps
using a subform to list the equipment and the main form for the user.

Show quoteHide quote

There is an Asset Tracking database template which you should have in your
Access application if you did a full install. If you don't have it, you can
download it from Microsoft at:

http://office.microsoft.com/en-us/templates/CT101426031033.aspx

Show quoteHide quote

You have actually hit upon a fairly advanced concept of relational database
design here, that of normalization to Fifth Normal Form (5NF).  I won't go
into the formal definition of this right now as it’s a little abstract.  I
can better explain it by an example.

You'll start with three tables, Employees, Computers and Software, each of
which represents one of the three main entity types with which you are
currently concerned.  Lets assume the primary keys are EmployeeID, ComputerID
and SoftwareID.  The Computers table would include the AssetNumber column.

Now, you can clearly model the relationship between the above tables by a
table with foreign key columns EmployeeID, ComputerID and SoftwareID
referencing the primary keys of the three 'referenced' tables.  However, this
table could give rise to certain update problems, though this is not
necessarily the case as it depends on the real world constraints governing
the database.  These problems relate to the insertion or deletion of rows
from the table, and are concerned with the necessity to insert/delete one row
if another is inserted/deleted in some circumstances, but not necessarily the
converse.

You might be familiar with normalizing a table by decomposing it into two
tables, and you could decompose this table into two tables, one with
EmployeeID and  ComputerID columns, the other with ComputerID and SoftwareID
columns.  The problem with this is that if you join the two tables over
ComputerID you may well end up with spurious rows in the result table
implying that some users use certain software on certain computers when this
is not in fact the case.  So you need to include a further table with columns
EmployeeID and SoftwareID.  When this is joined to the join of the first two
tables, this time over EmployeeID and SotwareID it removes the spurious rows.

Now that probably sounds like complete gobbledegook, but don't worry about
that.   The reason I've spelt it out is that you'll see that the three tables
Employees_Computers, Computers_Software and Employees_Software pretty well
exactly match your description of the requirements "which users use which
computer, which computers have which software, and which users use which
software".  So intuitively you've already identified the three tables
required to model the relationships between the three main tables, and gone
most of the way towards answering your own question.

Having set up the tables as described above  you can return which employees
use which software on which computers with a query such as:

SELECT FirstName, LastName, ComputerName, SoftwareTitle
FROM Software
INNER JOIN (Computers
INNER JOIN (Employees
INNER JOIN ((Employees_Computers
INNER JOIN Computers_Software
ON Employees_Computers.ComputerID = Computers_Software.ComputerID)
INNER JOIN Employees_Software
ON (Computers_Software.SoftwareID = Employees_Software.SoftwareID)
AND (Employees_Computers.EmployeeID = Employees_Software.EmployeeID))
ON (Employees.EmployeeID = Employees_Software.EmployeeID)
AND (Employees.EmployeeID = Employees_Computers.EmployeeID)
AND (Employees.EmployeeID = Employees_Computers.EmployeeID))
ON (Computers.ComputerID = Employees_Computers.ComputerID)
AND (Computers.ComputerID = Computers_Software.ComputerID))
ON (Software.SoftwareID = Employees_Software.SoftwareID)
AND (Software.SoftwareID = Computers_Software.SoftwareID);

A report can be based on the query.  The report wizard will do most of the
work in setting this up.

As you already have an Employees table in this model you can then use this as
the basis for HR management, leave bookings etc by creating other tables as
necessary, which can be related to Employees on the EmployeeID column.

Ken Sheridan
Stafford, England

Nadihaha wrote:
Show quoteHide quote

On 29 June, 06:50, Nadihaha <Nadih***@discussions.microsoft.com>
wrote:
Show quoteHide quoteI have done the same thing

Pls see here
For total MS Access mastery
http://access-databases.com/

junction table setup
Access Database Setup
Copy Field Data
Macro - one at a time, please!
add primairy key into txt field
Add a Footer to a Table
How do I set up a column to populate...
loading Access when I already have a 2007 student Office pkg loade
multiple users updating the same table and sharing
Access 2000 Only - Open multiple tables from query form