Re: simple database
From: Bruce (anonymous_at_discussions.microsoft.com)
Date: 08/23/04
- Next message: Lynn Trapp: "Re: _be growing"
- Previous message: GRCC: "Access 97 add ins missing"
- In reply to: Barbara: "simple database"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 23 Aug 2004 07:38:57 -0700
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.
>-----Original Message-----
>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
>
>
>
>.
>
- Next message: Lynn Trapp: "Re: _be growing"
- Previous message: GRCC: "Access 97 add ins missing"
- In reply to: Barbara: "simple database"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
