Rolling Inventory

[Voodeux2014]

Here is what I would like to do. I have set up a database to track all assets in our company. What I would like to do know is be able to track all the accessories (mice, keyboard, speakers, cables, etc.) and know how many i have total as well as know how many are not being used. What is the best way to do this with being able to set up an automatic report that gets emailed when we get down to a certain amount of available accessories?

[ranman256]

1. you should have put everyting in 1 table, tEqup
[EqType], [make], [model]
keyboard, ibm,
PC , ibm,
Monitor, sumsung,
etc...

(yes it works)


2.
Because you have different tables for each equp, you need a count query for each.
Do a count on the equpt type, count, and if assigned

3.
roll all these up into a union query.

[Voodeux2014]

So, in other words, you are saying that I should have all of the different assets combined into one table? Ive learned from the beginning of when i started playing with Access it is better to break tables up as it makes it easier to manage. Have I taught myself wrong?

[burrina]

Yes, different tables for different things but in general you don't need a lot of them. Example:
tblEquipment
tblEqupimentType
Linked by Primary and Fake Keys.
I did NOT download your db.

Check out this link for examples. http://www.databaseanswers.org/data_models/

HTH

[rpeare]

So, in other words, you are saying that I should have all of the different assets combined into one table? Ive learned from the beginning of when i started playing with Access it is better to break tables up as it makes it easier to manage. Have I taught myself wrong?

It's better to break tables up where the data is not duplicated. For instance the make/model of a monitor is not going to change over time and it's universal no matter what type of equipment it is.

So for instance you have a different table for computers printers, phones, monitors, accessories

All of that stuff could be in the same table and the information that is unique to the type of equipment would be stored in the sub tables.

So for instance you have a serial number, model all your tables as far as I can tell (but not manufacturer which is odd!)

So you could have a table that tells you what type of device it is (or what category you want it in)

TypeID  TypeDesc
1       Computer
2       Monitor
3       Accessory

then in your equipment table you would add a filed for the typeID to help you categorize them.

Then in your table PECComputers you would store the foreign key to your equipment table and any other information that was relevant to that piece of equipment. For instance on your current table PECComputers you have 'currentname', 'currentuser' along with 'video type' so you can do one of two things. Either you can put the 'video type' on the equipment table, and suppress that field during data entry if they do not choose a relevant category (monitor, phone?) or you can have your table store those 1 or 2 piece of information that relate specifically to computers, personally I favor the former because this is going to be a 'static' list of items once they are entered the first time.

You'd want a separate table for the history of users (if that's important) or, again you could put it on the main equipment table if the only thing you care about is the current user)

Lastly I see in your computers table you have the ability to add up to 4 monitors which I assume would be the PK from the monitors table instead of pecmonitor1 through 4 you should have a junction table which would store the processor ID (servicetag?) and the 'secondary' pieces of equipment attached to that computer (monitors, mice, etc)

[ssanfu]

I agree with rpeare. Right now your structure is like a spreadsheet: short and wide. A properly designed relational structure is tall and narrow.

The other things that bother me in your design is the use of multi-value fields and look up FIELDS (different than look up tables).
Multi-value fields are difficult to work with; I do not know of any programmers that use MVFs.
And look up FIELDS hide what is going on with the dB. See
The Evils of Lookup Fields in Tables http://access.mvps.org/access/lookupfields.htm

You have text fields as PK fields. While you can use text fields, they are much slower than numbers (Long Integers).
See
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm


I would suggest using descriptive field names; several tables with "ID" as a name can get confusing when creating queries.
But no spaces, punctuation or special characters in field names! Kudos!


OK, that is my $0.02 worth....

[Voodeux2014]

Thank you very much to the both of you. The reason why I did not put manufacturer in the table is because we get all of our monitors and computers from Dell. No need to include that since it is the only manufacturer that we have here.