Inventory Database with ability to "check out" equipment to an individual

[ajh2014]

Hello,

New to the forums and a basic user of Access. I'm building an inventory database that has 200+ seperate types of equipment and a varrying amount of pieces of equipment for those different types. I used a template from MS to get me started and have ran into a bit of a problem. My first questions is, should I build one single table for all 4000+ pieces of equipment or break it down into 200+ tables named for the associated equipment?

My second questions is is how do I get a command button to add a specific piece of equipment to a list so that I can then print the list out showing the total equipment that the individual has assigned to them? I can post my database file but will have to remove some info prior to doing so. Any help is definitely appreciated and sorry if these questions have been solved. I used the search function but didn't find any credible answers. Thank you

Tony

[ajh2014]

Currently I have a table listing all the different kinds of equipment and a seperate table listing all 4000+ pieces of equipment. Thank you!!!

[orange]

Did you do any research on database/table design? You may wish to Google 'Database normalization'.

You have equipment in 1 table, do you have any other tables? You haven't told readers much about WHAT you are trying to do.
You have said How you have done something.
More info would be helpful to readers, if they are to help you.

[ajh2014]

Ok let me start from the beginning... I'm trying to design a database utility that will allow me to inventory 4700 pieces of equipment. I then want to be able to "check out" these pieces of equipment to end users and account for where/who the equipment is checked out to and then check in when the equipment is returned. I currently have all pieces of equipment put into one table but am in the process of making different tables for all of the equipment types. Thanks for the help!

[burrina]

Perhaps this data model will give you an idea. http://www.databaseanswers.org/data_...ment/index.htm
Or http://office.microsoft.com/en-us/te...010206883.aspx
HTH

[ssanfu]

Without knowing more about your project requirements, I would have two tables:

Table: "EquipCategory"
----------------
EquipCatID_PK (Autonumber)
EquipCatDesc (Text)

Table: "Equipment"
------------------
EquipID_PK (Autonumber)
EquipCat_FK (Number - Long)
EquipNumber (Text)
EquipDesc (Text)

Relationship
--------------
Table: EquipCategory ---------> Equipment
Field: EquipCatID_PK --------> EquipCat_FK


My $0.02

[orange]

ajh2014,

When i read your requirement, a "library model" comes to mind. You have a large number of items (equipment/books) to manage. Equipment/Books can be assigned to categories Equipment Type/Books/DeweyDecimal..or Subject/Author..
Equipment/Books are loaned to individuals for a period of time. The Equipment/Books are returned, and are returned to Inventory. At any time you can determine the current location of any piece of Equipment/Book.

Just for consideration....
Good luck.

[ajh2014]

Sorry for being so needy... I have absolutely no idea how to get this database to do what I want it to do. I have read through so many webpages and access books that I don't even know where to begin. I'm posting my database so anyone that wishes to help me will be able to view what I have already. So here I go...

My database will be tracking my electronic assets. I have already looked into using a template but what is available will not work for me. If I try to alter any of the templates I ended up breaking them anyways. Of my assets I have about 230 different types and over 4000 total items. I want to be able to check out these items to an individual. I would like to set up a form to where I type in a nomenclature or select it from a drop box and have all associated types of that equipment show up in a list box. I would like to then select which one I want to issue to the customer and have it apear in another listbox which will show all items checked out to that individual. Everything that I have looked up about list boxes has gotten me no where and I am getting frustrated with how nothing is working. Thank you for your time and any effort to help me is greatly appreciated.

[June7]

Templates can certainly be a source of ideas. Which did you look at?

Desktop Asset Tracking http://office.microsoft.com/en-us/te...001225342.aspx

Lending Library http://office.microsoft.com/en-us/te...in=TC001225342

[ajh2014]

I looked at both but was overwhelmed with everything that was in there. I'm totally lost...

[ssanfu]

I looked at your dB... Not too bad - no spaces in names, no special characters/punctuation. but you did have a few look up fields.
I removed the lookup part of the field. See http://access.mvps.org/access/lookupfields.htm
All data should be entered through a form, never through tables.

In Master MOD.accdb:
I deleted the lookups and edited the records in Master where the FK field value did not match the look up table PK (dept table).
Look at the relationship window. I added comments to tblMaster (design view).


I added a dB that I designed off of you dB. (good old paper and pencil).

In Master SS.accdb:

I deleted all data except in tblStatus.
I do have a couple of questions:

How does Department fit in? Does the asset belong to a dept or does a customer have a dept?
What is tblNomenclature?
What is "cMan"?


The first step is getting a good table structure. First enter data into all tables except "tblAssetTracking".
Then enter a few records into "tblAssetTracking"..
Next create a couple of queries to see of you can derive the data you want.
After that comes forms and reports..

Looking at the relationship window, I can also see a little different structure. But I don't know your data or requirements. So I'm kinda spit balling here.
You get the hard part - you make the decisions.

[ajh2014]

Steve,

Thanks for your reply. You're giving me hope to a situation that I was thrown into. A lot of the data in the master table came from our old tracking databse. It was designed by a company that went bankrupt a couple years back so there's no support, hence why I'm on the forums. The cman column, I believe, was the old department place holder. I wanted to create my own by including the tbldept portion. tblnomenclature is the description of the asset, ie laptop. I couldn't include actual nomenclatures due to the nature of my job so I apologize for it being so generic. I hope that it didn't confuse you too much. As far as the department goes... we own the equipment but check it out to individuals. i want to include the department that the person works in so that when they are late getting their monthly inventories back to us I know who to contact to rectify the situation. If you have any other questions feel free to PM me or post to the board. I'll be checking it regularly because I would like to get this database completed. And thanks!!!

[ssanfu]

A lot of the data in the master table came from our old tracking databse. It was designed by a company that went bankrupt a couple years back so there's no support,

What is the table structure of the current dB?

we own the equipment but check it out to individuals. i want to include the department that the person works in

Does the individual change departments often? Isn't the dept an attribute of the individual?


Regarding my version of your Master dB, I don't know your data so I'll create an example.

Earth Movers include Grader & Bulldozer
Cranes include 5 ton Mobile & 1 Ton Mobile

The "AssetTypeID_PK" should be stored in the "AssetTypeID_FK"

In table AssetTypes

AssetTypeID_PK	Desc
1	Earth Movers
2	Cranes

In table Assets

ID	AssetTypeID_FK	AssetName	AssetSN
1	1	Grader	Cat-12586
2	1	Bulldozer	1986-01
3	2	5 ton Mobile	MW-87698532
4	2	1 Ton Mobile	Manitowoc-160-A-1998

Does this help?

[ajh2014]

Here's an updated version with some additional info in some of the tables. Anyone willing to take a look at it and to provide feedback that would be great. I am currently trying to build a form so that I can do data entry and to also "check out" items to individual users. Thank you!

[ssanfu]

Why did you put look up fields in tblCustomer in "RateID_FK" & "CustomerDept"??

See
The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm


No experienced programmer I know of uses look up fields.

trying to build a form so that I can do data entry and to also "check out" items to individual user

You will probably use a main form sub form arragnment.
What do yo mean by "Data Entry"? Examples please or your ideas.