Need help with my database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I'm fairly new to Access - I just started working with it this week
because I wanted a stronger storage solution than creating workgroup after
workgroup in Excel.

The primary purpose of the database is to manage Microsoft patches installed
on my network machines sorted by PC Name and IP.

My database consists of two tables: tblHostList and tblPatchList.
tblHostList contains all the host data, PC Name, IP, OS , etc and
tblPatchList has patch information.

Below is a jpg of the two tables (250k)

http://packav.shackspace.com/a2.jpg


I have the related the tables based on OS. The Operating System field on
tblHostList should corrispond to the matching OS field on tblPatchList.

Below is a jpg of the relationship settings (<100k)

http://packav.shackspace.com/a3.jpg

Finally I created a nice report.

Below is a jpg of the report (150k)

http://packav.shackspace.com/a1.jpg

Isn't it beautiful? There's a problem though, the report (and any
queries)only shows patches for WINXP!

I believe there might be a problem with the way I have my relationships set,
or perhaps my OS table design is faulted, but I've tried everything I know to
do, and it still is only linking my WINXP patches and systems.

I could use any assistence you outlook gurus can muster.

Thanks in advance!
 
Your data structure is flawed.

The entities you are modelling include (I think) the following:

-Operating systems.

-Computers. Each computer has one (or more than one?) OS.

-Patches. Each patch is for one (or more than one?) OS, and can be
applied to zero or more computers.

You need one table for each of these entities. Let's call them tblOS,
tblComputers, tblPatches. * means a field is, or is part of, the table's
primary key.

tblOSs
OS* (name of operating system)

tblComputers
PCName*
IPAddress
OS - foreign key into tblOSs
Other fields

tblPatches
PatchID *
OS
DateReleased

You also need a joining table to implement the many-to-many relationship
between Computers and Patches, e.g.
tblPatchesApplied
ComputerID* - foreign key into tblComputers
PatchID* - foreign key into tblPatches
DateApplied
Comments
A record in tblComputersPatches stores the fact that a particular patch
was applied to a particular computer on a particular date.

If any of your computers may have more than one OS installed, things get
a bit more complicated: you'll have to remove the OS foreign key field
from tblComputers and implement the many-to-many relationship between
Computers and OSsvia a joining table
tblComputerOS
ComputerID*
OS*

Similarly, if any patch can apply to more than one OS you'd remove the
OS foreign key field from tblPatches and add a table to track which
patches apply to which OSs:
tblPatchesOSs
PatchID*
OS*
 
Ah, thank you sir!

I believe I was trying to cram too much information onto two tables. After
I included the joining (junction) tables and rearranged how my data was
stored on the tables - my reports came out perfect. Since each patch can
match multiple OSs, I was having a hard time finding a way to represent that
in the tables. This explains my OS1, OS2, etc.

I decided to exclude the tblPatchesApplied since any Patch that is inputted
into tblPatches is going to be on those systems with matching OS. I could
create this table later, but maintaining tblPatchesApplied would require alot
more data entry than I have time to do (Unless you know a shortcut)

I also learned that a foreign key is basically a column on a table mapped to
another column on a different table through relationships. (The Access help
is very vaugue about foreign keys)

Thanks again.
 
I decided to exclude the tblPatchesApplied since any Patch that is inputted
into tblPatches is going to be on those systems with matching OS. I could
create this table later, but maintaining tblPatchesApplied would require alot
more data entry than I have time to do (Unless you know a shortcut)
Click to expand...

This is fine provided you don't need to record which patches have been
applied to which computers.
 
Back
Top