multiple records across subforms

[subtlesnake]

I have library system containing 4 tables : book, customer, copy (for multiple copies of the same book) and loan. The loan table takes the foreign keys CustomerID and CopyID, and uses a LoanID as the primary key.

Currently, I use 1 subform to display the books a customer has out, which updates via a CustomerID combo box.

What I want is to display each record in a separate subform, so when I select a given CustomerID if the user has 3 books out (the maximum) there will be three separate records reading Title: Author: etc. There would be a return button by each record to return the book.

Is this possible? And how would I go about doing it? Thanks for any help in advance.

 

[hkaing79]

I would create a rank query (subquery that compares value less than or greater than the current record). This will give you something like:
CustomerID, BookID, Rank
1, 1, 1
1, 2, 2
1, 3, 3
2, 4, 1
3, 5, 1
3, 6, 2
...etc

Then create a crosstab of it using CustomerID as your Row Heading, BookID as Value, and Rank as Column Heading.

Or in your case, each subform would filter the data set using WHERE Rank=1, Rank=2, and Rank=3.

The problem would be if a customer checked out two copies of the same book.