newbie help re tables

[rama1712]

I have been asked to create a DB in access of computer inventory.

I have several tables setup
Station Name
Room Location
Staff Name
Department

I then have another table setup that has 4 Lookup fields linked to above. Table called inventory

What I want to happen is when you entery Data in to the table Inventory if it is not present in the 4 above tables it is automacticly added.

When I sent relationships i come up with differnet messages

I have tried 1 to many 1 to 1 .

Can someone please send me an example DB or screenshots.
Of what I need to do.

Thanks

I can send the DB to you if it would help.

Thanks
Stuart
stuart@rama1712.com

 

[directormac]

Insufficient Data

You're going to need to fill in some more details. Is each Staff member related to only one room? To only one workstation? Also, tblInventory doesn't need to relate to all four tables--so far, just tblStations and tblStaff.

Based on your description, your tables thus far should look something like this:

tblStations:
autoStationKey (PK, autonumber)
txtStationName (text)
fkRoomID (long)

tblRooms:
autoRoomKey (PK, autonumber)
txtRoomName (text)

tblStaff:
autoStaffKey (PK, autonumber)
txtStaffLastName (text)
txtStaffFirstName (text)
fkDepartmentID (long)

tblDepartments:
autoDepartmentKey (PK, autonumber)
txtDepartmentName (text)

tblInventory:
autoInventoryKey (PK, autonumber)
fkStaffID (long)
fkStationID (long)


BUT, from the way you're describing things, I think some reworking of your tables might be in order. Upload your db so those willing to help have a better understanding.

--Willing Mac

 

[rama1712]

This is what i want made into a db

Attached is a ss that needs to be put made into a database

What we want is

A record of Each Computer with the relevant data attached to that record.
IE NAME,LOCATION,Staff Responible,Department, IP Addreess MAC Address and the a memo field.

We then want a record of that station with Purchase date,price, order number, warrenty period, serial num and a memo field.

I know I can do this in a flat file but when it comes to Access I am lost on how to link all info to gether so that if I selcet ICT01 in a query I will get all relevant data pulled to gether in a record.

Stuart

 

[directormac]

One table each for: Rooms, Stations, Staff, Departments, Equipment, EquipmentTypes (monitor, desktop, printer...). Your workhorse will be tblEquipment:

autoEquipmentKey
txtEquipmentSerialNumber
fkEquipmentTypeID
fkStationID
fkStaffID
mmoEquipmentNotes

(no need to store related rooms here, since you get that from the station. Ditto on departments, which you get through staff).

Someday, you can add the appropriate tables to track service tickets on the equipment as well. In fact, search the archives here for "service call" and you might find something to help with your current needs as well.

--Terse Mac