linking tables

[Waldo]

I've been doing an unexpected bit of access this summer do to a student job, but I'm not all that familiar with it unfortunately. None the less the database has organically expanded to good functionality.



But now I've stumbled upon something I couldn't work out. I have one table (my main "tblinventaris") with data on inventory such as laptops etc. which I want to link to a table called "tblMO docenten" with the names of people. What I wish to do is make it so in the form connected to "tblMO docenten" users can add and change a person connected with an inventory item (such as a laptop or beamer). Thereby users have to be able to see descriptive variables from the inventory object such as serial number, type,...

How do I best go around doing this? My database: http://www.box.net/shared/oydm5qfkmk76x9fqq07i

Much cheers and happiness your way if you can help me out, been sweeting on this for a full day

Waldo

[June7]

Is this inventory for sale or is it property of your business you keep track of?

You have a list of items and list of people? What is the nature of table relationship? Each item must be associated with only one person? Each user can have more than one item? Then include a field in tblinventaris for the personID (not name, each person record should have a unique ID).

[Waldo]

Thanks June for your reply. This is an inventory to keep track of property within the company.

I would like to add several items to one person (one item=one owner; only a select few items belong to an owner) so that you can look up what items one person has. I had a solution where you could check the boxes of items in use with one person but I could not get the value associated with the ID of the variable 'category' (which is in turn connected to another table).

So what is the best way to go forward? Which structure to best use? And in case of progressing as stated above, how can I get the value associated with an id to show instead of only the ID.

Thanks again!

[June7]

My suggestion remains same. Have a field in items table for the employee ID.

Get detail info associated the the employee ID by joining tables in queries.

[Waldo]

What kind of query do I need to use for that? And don't I need a table also to store the links between those two tables (in fact will be 3 tables)? Certainly in view of making comboboxes in forms & making reports based on that query...

[June7]

If each item can have only one owner then all you have to do is store the owner ID in a field of the items table. Then a query would join the tables on the two owner ID fields. This will make the owner detail fields available.

Is it possible you will have employees in the table that don't have items assigned? Is it possible to have items in table that have not been assigned? The jointype depends on which way you want to organize records. To see ALL items jointype "Show all records from items...", to see ALL employees "Show all records from employees ...", to see only records that have an item/employee association "Show only where both fields are equal"

[Waldo]

Thanks June to solve the problem. I was a bit too much 'in it' to look at the general picture. Was able to solve it after the weekend Thanks for the help!