simple database

  • Thread starter Thread starter Barbara
  • Start date Start date
B

Barbara

I'm new to access, I just entered over 100 items into a
database I was trying to build, after I hit save, all the
info disappeared.
I am looking for an easy way to do a simple database. I
have read the whole Microsoft System book on access, and
have done all the exercises in the book also.
I am looking for a tool database. I would like to have
the following:
Tool ID (I don't want auto numbering)
Tool Description (I would like to link that to the ID,
so when I pick the id the description
comes up also)
Employee Name (this will be the person who has the tool)
Date Received (The date the employee received the tool)
Date Returned (The date they sent it to the shop or
another employee)
Employee Name (The name of the employee the tool was sent
to)
Also is there anyway of setting it up so that I always
see who has the tool and who had it previously. I know I
would need another column with names and dates, and that
is fine, but I have no idea on how to set up so the names
would jump to the next column etc..

Any help would be greatly appreciated. I honestly have no
idea of what I'm doing.

Thank you,

Barbara
 
Hi Barbara,

Always when designing a database you need to work out what are the
real-world things and concepts - the database word is entities - you're
modelling. Here, it looks as if you have the following:
-Tools
-some kind of transaction during which a tool changes hands (shop to
employee, employee to employee, or employee to shop)
-persons (employees) or places/institutions (e.g. "shop") that may have
custody of one or more tools.

You can model these with three principal tables (others may be needed if
the description above turns out not to be adequate:

tblTools
ToolID - Primary Key (an autonumber would be simplest)
ToolDescription
perhaps other fields such as Condition, DateAcquired,
DateLastServiced...

tblPossessors (called this because some Possessors - e.g. "shop" - won't
actually be employees)
PossessorID Primary Key (preferably this should be the Employee
ID used in your company's other databases)
PossessorName
etc.
(This table will have one record for each employee plus
others for "shop", "sent away for repair" or whatever is needed.)

tblTransfers
ToolID - Foreign Key into tblTools
PossessorId - Foreign Key into tblPossessors
TransferDate - Date/time
All these three fields should be in the primary key.


With tables like this it should be possible to do everything you're
asking for.

Each time a tool changes hands, create a record in tblTransfers (easily
done with a form and subform similar to the Orders/Order Details form in
the Northwind sample database) that shows the Tool, the date and the new
Possessor.

To find out who's got a tool, you do a query for the most recent record
in tblTransfers with that toolID (in a query that joins tblTransfers and
tblPossessors so it can return employee name, not just ID). To find out
how long they've had it, look at that record's TransferDate.

To get the history of a tool, pull out all the records in tblTransfers
for that tool, and you'll be able to see who had it, when and for how
long.
 
Check out www.caljel.co.uk for a simple tool management
database, I use it and find it easy to use and understand.
Sounds like it doesn't quite do what you're after but they
do offer a customisation service.

Ken
 
Into what component of the database did you enter over 100
items? A table? It is not necessary to save records in
Access, so something seems to be irregular right from the
start if what you thought were records disappeared.
Access databases start from tables and the relationships
between them. For table design, think about making the
table as small as is possible. Design tables that you can
describe without using the word "and" (except for the last
item on a list, such as name AND address, which is
perfectly OK). Another thing to remember is that the
Primary Key is often invisible to the user, and need not
have any real world significance. The company's Employee
ID number can be a primary key in that table, but I would
argue against even that because I have known numbering
systems to change. Let the Tools table use an autonumber
primary key. You never need to look at it again, and you
are free to ignore it. Use some other number as you wish,
As John in another post pointed out, there are apparently
three different elements to your database: Tools,
Possessors, and Transfers are the names he is using.
tblTools contains tool information, nothing more.
tblPossessors is as he says, but I would argue against
using EmployeeID as the primary key if some of the
Possessors aren't employees. I don't think you want Shop
or Repairs listed as employees. You could probably
construct a query combining tblEmployees and
tblOtherPlaces or something of the sort.
Each posessor can possess many tools, and each tool will
pass through the hands of many possessors. This is a many-
to-many relationship. Since a direct many-to-many
relationship is not really possible, you will need a
junction table, which is where tblTransfer comes in. To
my thinking, a junction table should contain its own
primary key, and foreign keys named identically to the
primay keys in the other tables, and other information
such as TransferDate as needed. In the relationships
window (see Help) establish relationships between the
primary keys and the foreign keys.
I am not in favor of combined field primary keys if there
is any way to avoid them, but that is just my opinion.
Consider, though, if combining Possessor, Tool, and Date
would be a problem if the possessor ever takes possession
of the same tool twice in one day.
Use autoform to make a form for each table. Drag the icon
for the form based on tblTransfers onto tblTools in design
view. Make a query (qryPossessors) based on tblPossessors
and sort it as you wish (e.g. alphabetically). Use the
query as the row source for a combo box in the main form.
Experiment with this setup for a while to see if it does
what you need. Afterward you can customize the forms to
suit your preferences.
Keep in mind that viewing selected data (such as the last
person who used the tool) is a function of filtering data,
not of storing selected data. Once you get the basic
relationships to work properly, you can filter the data to
suit your needs. Start with the relationships. Once they
work as intended you can refine what you see.
 
Back
Top