generate Report Borrow Book By Month and Year

[ultra5219]

hi guys.
anyone know how to generate Report Borrow book By month and Year.
For example, when i open report view, the report prompt the user enter month (July) and year (2016) and generate overall summary List of borrow Book By month and year. I did Test usung query but how to generate in report form. the report shows July 2016 and below list borrow books.
Thanks

PS: i attach my sample file access.

 

[plog]

You need to take a step back and fix your tables. When I open your relationships a huge mistake is immediately seen--a circular path. There should only be one way to get from one table to another--you've created a circle. I can go clockwise from BookRecord to Membership, or I can go counterclockwise and get there. There should be only one way to get between them.

Then when I look closer, I see you have 2 tables for borrowing books. That data should be all in the same table. I'm sure there are database models of this you can look to for guidance. I would research this more and fix your tables, then the report you want becomes simple.

 

[sneuberg]

To add to plog's observations you shouldn't have the same data repeated in different tables unless they are keys. The BookName shouldn't be in BorrowBook and the Membername shouldn't be in either the ReturnBook or BorrowBook abeit as plog said their shouldn't be two table to start with. You can alway get this data with a join in a query. If you are thinking about making these lookup fields please consider the evils of this behavior.

The BookID apparent just identifies a book in general and not a specific book. I infer this from quantity field the in BorrowBook table. If this is the case how is the return book condition connected with a specific book. Also the ReturnBook book table doesn't have a quantity. If three copies of Gone with the Wind were borrowed what do you do when three are return at once?

There's a Yes/No field for Return_Book_Condition_OK and one for Return_Book_Condition_poor. Aren't these mutually exclusive? What's the meaning the when both of these fields are Yes or No.

 

[ultra5219]

You need to take a step back and fix your tables. When I open your relationships a huge mistake is immediately seen--a circular path. There should only be one way to get from one table to another--you've created a circle. I can go clockwise from BookRecord to Membership, or I can go counterclockwise and get there. There should be only one way to get between them.

Then when I look closer, I see you have 2 tables for borrowing books. That data should be all in the same table. I'm sure there are database models of this you can look to for guidance. I would research this more and fix your tables, then the report you want becomes simple.

I see.. well im not very good in database access. can u fix for me the table and relationship? this is just for my small practical excercise

 

[ultra5219]

To add to plog's observations you shouldn't have the same data repeated in different tables unless they are keys. The BookName shouldn't be in BorrowBook and the Membername shouldn't be in either the ReturnBook or BorrowBook abeit as plog said their shouldn't be two table to start with. You can alway get this data with a join in a query. If you are thinking about making these lookup fields please consider.

The BookID apparent just identifies a book in general and not a specific book. I infer this from quantity field the in BorrowBook table. If this is the case how is the return book condition connected with a specific book. Also the ReturnBook book table doesn't have a quantity. If three copies of Gone with the Wind were borrowed what do you do when three are return at once?

There's a Yes/No field for Return_Book_Condition_OK and one for Return_Book_Condition_poor. Aren't these mutually exclusive? What's the meaning the when both of these fields are Yes or No.

Well The YES and No condition means if YES the book return in good conditions and Poor means when the return the book conditions very poor.
Book Record represent all the record of book. Borrow book table means the record for all who borrow the book and Return book means they return the book from the library.

Any ideas how to fix the table and query or relationship? im still beginner of this access database. maybe u can show me the solutions?

Thanks

 

[plog]

Google "database schema for library".

 

[sneuberg]

This site has several models for libraries. I came across this through plog's Google suggestion.

 

[jdraw]

Cross posted http://www.accessforums.net/showthread.php?t=61268