My new database

[PaulM]

Hiya folks

Obviously I'm new to databasing so the following etc, etc.....
I have.....
A large table containing all of the data about 200 laptops.
7 other smaller tables with all of the data about the laptops by room name

So there's about 30 entries in each of the smaller tables.

So what I want to know is can I make it so that if I enter data in the large
field can ti also appear in the relevant other table? Ie..if I make an entry
to the effect that laptop 7 in room GM1 has a fault then can I set everything
up so that the entry also goes into the table that is specific to GM1?

I thank you in advance

Paul

 

[Guest]

Paul,

One cardinal rule of relational database design is to store data once in a
single place. You can always display the data anyway you want--all the
machines in a given room, all the rooms with XYZ manufacturer, etc., by using
queries & forms.

Think in terms of "things" and "attributes". Each thing is a table, each
attribute a field.

From what you've said so far, you need at least several tables:

Manufacturers
--------------------------------------------
MfrID AutoNumber (PK)
Manufacturer Text
Address
.... other manufacturer-specific fields


Rooms
--------------------------------------------
RoomID AutoNumber (PK)
RoomName Text
.... other room-specific fields

Laptops
---------------------------------------------
LaptopID AutoNumber (Primary Key)
MfrID Integer (Foreign Key to Manufacturers)
RoomID Integer (FK to Rooms)
SerialNumber Text
.... other machine-specific fields

With this arrangement, the data specific to each laptop is stored ONCE in
the Laptop table. To associate the machine with a room, a room ID field is
included in the table.

For this and other foreign key fields, the usual way to permit data entry is
to provide a combo box Bound to the numeric foreign key field, but with a
RowSource that includes both the PK and the more meaningful 2nd column
description. By setting the first ColumnWidth set to 0", the combo box
*stores* the numeric FK but *displays* the description. If you enable the
wizard in form design view (View, Toolbox, <toggle on the button with the
wand and stars>), Access will guide you through when you attempt to place a
combo box. Select "Hide Key Field" when prompted.

Hope that helps.
Sprinks

 

[Keith Wilby]

Hiya folks

Obviously I'm new to databasing so the following etc, etc.....
I have.....
A large table containing all of the data about 200 laptops.
7 other smaller tables with all of the data about the laptops by room name

So there's about 30 entries in each of the smaller tables.

So what I want to know is can I make it so that if I enter data in the
large
field can ti also appear in the relevant other table? Ie..if I make an
entry
to the effect that laptop 7 in room GM1 has a fault then can I set
everything
up so that the entry also goes into the table that is specific to GM1?

I thank you in advance

Paul

Hi Paul.

That's not the way relational data should be stored. Each data element
should be stored once only. You need to think about relationships between
entities. What is the relationship between a laptop and a room? Can a room
have many laptops? Can a laptop be in many rooms? Without knowing more
about what you're trying to do it's hard to say more but storing repeated
data is a major no-no.

Regards,
Keith.
www.keithwilby.com

 

[PaulM]

Thanks for the rapid response, people...I see where I may be going wrong. I
shall redress ...oh alright..I'll start from scratch and plan a little more.
What I want to be able to do is to check how many laptops are damaged at any
one time and perhaps have nice looking reports for the powers that be.
I'm more 3D modelling, graphics and multimedia than apps such as Excell and
Access so please excuse the silly questions...still..if you don't ask, you
don't find out..
Once again

Thanks a lot for the speedy replies, it's much appreciated

Paul M