Help please..... (1 Viewer)

[Rey]

I created two tables:

1.- user information such as name, use date, time, place, etc.
2.- equipment such as computer, projector, laptop, etc.

Table number 2 is a yes/no table, so I only have to mark what kind of equipment this person is going to use.

My question is, how do I create a relationship between these two tables, so when I do a query I only get what is mark as yes on table number 2 and all the information in table 1?

Any help or advice will be appreciated.

Thank you.

 

[pdx_man]

You will need to have a field in the Equipment table indicating who the item is checked out to. This field would contain the user ID. Link the User_ID in the User table to the User_ID in the equipment table.

 

[Rey]

If I had a database of all the equipment I think this would be easy, unfortunatelly I don't. My problem is that I have many different users for the same piece of equipment, sometimes I get a person requesting a piece of equipment for the entire year for maybe once a week, but this equipment must be available for other people to use when it is not use by this person, and if somebody is using this equipment then I have to give this persong a different laptop, projector, etc.

Thanks for trying to help.

Rey

 

[RobertQ]

Hi,

the only way to solve this is to create a table which contains your equipment and a field like: current user which is linked to you first table.
For my opinion the secon table can be removed.

Kind regards

 

[neileg]

RobertQ is right. Your yes no table is doing nothing for you. Your relationship should be between the piece of equipment and the user. The existence of a relationship is a yes, and an absence is a no. Obviously the time will change the relationship.

Don't make the mistake that many spreadsheet users do when they start with databases. The useful information will usually come from a query that combines data from different tables against criteria, not from simple inspection of the data tables themselves.

 

[Rey]

I think I got it this time. I created a table with the user information and another table for the equipment information, I made a relation between the equipment fields and I think I got what I needed (at least for the query). I will need to create a form to enter this information.

Thanks Neil, RobertQ and pdx_man for taking time to unser to this posting.

*********This site ROCKS**********This site ROCKS

 

[The_Doc_Man]

The way I see this one, your optimum design is this:

Persons table
-- person ID (prime key, probably autonumber)
-- person name
-- person phone
-- ... other person data

Equipment table
-- Equipment ID (prime key, probably autonumber)
-- equipment description
-- equipment type
-- equipment serial number (or other identifying data when you have more than one of the same type of item)

CheckOut table
-- person ID
-- equipment ID
-- date of checkout (or date on which reservation of item occurs)
-- expected date of return

Now, how to use this? Your form shows two things: A list of persons and a list of equipment. When you check out an item, you create an entry in the checkout table that links the item to the person.

To see what is checked out, query the checkout table for entries where today's date is between the date of checkout and the date of return. To see what is available on a given date, query the table based on that date rather than today's date.