How to create a simple form to work with two tables

[Guest]

I am trying to write a databse that will do as follows:
In one table I have the details of the properties of a particular item (each
item is another record)
In another table I have a list of "borrowers" (each borrower is another
record)
Now I want to create a form/table that will allow me keep track and save
what a "borrower" borrowd. A borrower can have more then one Item out at
once. I will input the dates that the person neads the item for.
Also I want to be able to pull up a screen and lets saymark off that a
person neads an item with properties A and F (for example), between these two
dates, and it will then display me a list of what items are available forthat
time.

 

[John Vinson]

I am trying to write a databse that will do as follows:
In one table I have the details of the properties of a particular item (each
item is another record)
In another table I have a list of "borrowers" (each borrower is another
record)
Now I want to create a form/table that will allow me keep track and save
what a "borrower" borrowd. A borrower can have more then one Item out at
once. I will input the dates that the person neads the item for.
Also I want to be able to pull up a screen and lets saymark off that a
person neads an item with properties A and F (for example), between these two
dates, and it will then display me a list of what items are available forthat
time.

You almost surely need a *third table*. If one person can borrow
several items, and each item can be borrowed (over time) by several
people, you need a Loans table with fields for the ItemID, the
BorrowerID, the date borrowed and the date returned.

Your loans form would be based either on the borrower table (if you
want to see what items Fred has borrowed) or on the Items table (if
you want to see who has borrowed the Three-Tined Blivet); on the form
would be a Subform based on the loans table.

For the "available" search form, you could use a Query joining the
Items table to the Loans table using a left outer join (click the join
line and select "Show all records in Items and matching records in
Loans") with criteria on the date-loaned and date-returned fields to
exclude items that are currently loaned out.


John W. Vinson[MVP]