Access2000 problem can anyone help me please?

[helplesss]

I am building an access2000 dbase for video rentals. I have a table with the rental info (ie. Ph. #, Video Number, Rental Date). I have another table that stores RETURN info (ie. Video Number and Return Date). How do I connect the RETRUN Video Number & Date to the Exact RENTAL record as there may be multiples of same video numbers from different rentals? I know this may sound easy but I just can't think of it and been trying for weeks! THANKS for any suggestions!

 

[AtomicChip]

If you haven't done so already, I would create a third table to store individual video information. Include the VideoNumber field in the video table, set it as autonum, and also set it as the table's primary key.
Set the VideoNumber fields in both the rental and return fields as number (not autonum). Make sure that the names (VideoNumber) in all three tables is the same. Open up the relationship view (I believe it's under 'Tools, Relationships'), and add all three tables.

Connect the VideoNumber field from the videos table to the rentals table, and enforce referential entegrity. Then, connect the videos table the same way to the returns table.

If you have any further problems, please let me know, and I'll figure something else out.

 

[mailzero28]

Are you sure you need a RETURN table? Why not just include a field ReturnDate and ReturnType in your RENTAL table. If these are blank the video has not be returned and your Table Design stays intact, because these are bonified attributes of the table RENTAL.

By the by, you need to ensure you have a unique identifier for each rental e.g. an Autonumber.

bye

Martin.

 

[helplesss]

Thanks guys for all your suggestions! If I don't decide to have another table (Returninfo) and put the return date in the same table as the Rentalinfo, i'm confused as to how i put the return date and video # in the form... I have 2 forms... rentalform (rental date, ph. #, video # and as you suggest put return date in this form??). Now i make another form called Returnform and put Video # and Return Date but block out the other fields?? My other question is how do I know if it's the correct field to pull up the most current record from rentalinfo table? THANKS anyone and everyone!!