Inventory help

[Brad Reichert]

A while back I was working on a PC inventory database and was getting help
here. I setup my database as recommended (below). I put manufacturers into
the Manufacturer table and Buildings into the Building table. My problem is
that I don't understand how to start using the table. I've tried creating
forms with the Equipment and EquipLocation tables but that doesn't seem to
work. Can someone please point this Access Newbie in the right direction?
I've been using Excel to track inventory and I know Access will do a better
job, I just don't know how to set it up.

Thanks,

Brad


Original Recommendations
----------------------------
Your nomenclature is confusing, so I feel this is what you should have:

tblBuilding
'This table records info about each building
BuildingID (AutoNumber - Primary Key)
BuildingName
etc...

tblRoom
'This table records info about each room within each building
RoomID (Autonumber - Primary Key)
BuildingID (Long Integer) - FK to tblBuilding.BuildingID
RoomName (Text)
etc...

tblEquipment
'This is an inventoryTable - for all equipment
EquipmentID (AutoNumber - Primary Key)
SerialNo (Text)
ModelNo (Text)
EquipmentType (Integer) - 1=PC, 2=Monitor, 3=Table
ManufacturerID (Long Integer) - FK to tblManufacturer.ManufacturerID
...etc

tblManufacturer
'This table records info about all manufacturers
ManufacturerID (AutoNumber - Primary Key)
ManufName (Text)
etc...

tblEquipLocation
'This is the working equipment location table
LocationID (Autonumber - Primary Key)
LocationDate (Date/Time)
EquipID (Long Integer) - FK to tblEquipment.EquipmentID

All equipment should be maintained in the same table, using a flag to
determine what type of equipment it is (PC, monitor, table, or whatever).

Whenever you move a piece of equipment, its location is registered in
tblEquipmentLocation, using a date and time. That way, you can always query
this table usign Max(LocationDate) to determine where each equipment item
is. You can also get a history of where each item has been.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia

 

[LeAnne]

Hi Brad,

Well, what you do with your database depends on what questions you plan
to ask of your data. You say you want to "track inventory." Are you
tracking inventory on hand? Inventory sold? Purchases by product? By
customer? By date? The tables just store your raw data. To blend the
data from multiple tables and make sense of it all, use queries. Forms
are how you interact with your data. A form based on a table is a window
into that table. Forms can also be based on queries to display the
updated results of a Totals query, display data from linked tables, and
so on. And Reports are used to organize and present your data.

Take a look at the example database Northwinds.mdb included with most
versions of Access (some later versions may not include Northwinds, but
you can download it from the MS Support page
(http://support.microsoft.com/default.aspx?scid=fh;EN-US;acc). This
database demonstrates an inventory tracking application for gourmet food
items, and includes numerous examples of user interface forms, queries,
reports, and other neat stuff used to track employees, vendors,
products, and sales.

hth,

LeAnne

 

[Mart]

From looking at how the tables are set up you need to link
tblEquipLocation to the Equipment table.

The tables need to relate to each other so that should you
look at the tblEquipLocation table you should be able to
find out the id no of the bit of equipment and the id no
of the location.

Sometime it's simpler to have fewer tables when you start
as this can just confuse the issued. It's not really the
correct way... but if you've got to get something up and
running.

Have you got your tables joined in a query that your form
looks at?