Thank you very much Paul, Nick and Indranil, for your patience reading of my previous posts and valuable and detailed explanations with examples. I got complete solutions in the context which I was looking for.. from your answers. Just to accommodate searching within item type in advanced search, I was using item type at bibliographic record level (942$c) not at item level (952$y) previously and it has triggered problems at report generation. And I admit, I was unaware of 952$y field and assumed that whatever is in 942$c is same as 952$y (of course, it uses the same info) and thought 942$c is re-appearing again at the item level and did not modify anything further. Thank you again for this forum for helping. With thanks Satish MV Librarian Govt. Engineering College, Hassan. Karnataka. On Sun, Mar 1, 2015 at 12:07 AM, Indranil Das Gupta <indradg@gmail.com> wrote:
Satish,
On Sat, Feb 28, 2015 at 10:10 AM, SATISH <lis4satish@gmail.com> wrote:
I found one solution, but it is not through Koha. I exported unique records generated by both the item type BB and BK and put it in a single excel file and applied conditional formating and applied filter ( to ISBN column, since it is unique) by colour and found the duplicate biblionumber/titles generated between BB and BK (not among BB or BK) and removed manually. But, still I expect, automated software to perform this using sql.
I do not know the workflow at your library. But here is what I would do:
1/ Create two patron categories - a) NS - Regular Students b) RS - Reserved Students (the category codes and names are just place holders, you can call them whatever you want)
NOTE: All students who do not require additional institutional / govt support are added under the first category; all students entitled to "Book Bank" use under the second one.
2/ Create ***three*** item types - a) RB (Regular Books) and b) BB (Bookbank Books) AND c) " BK" as Books
3/ Define the following Circulation rules for your library:
a> Regular Student - Book Bank - 0 Checkouts - 0 loan period b> Regular Student - Regular Books - 2 Checkouts - 14 day loan period c> Reserved Student - Book Bank - 2 Checkouts - 14 day loan period d> Reserved Student - Regular Books - 2 Checkouts - 14 day loan period
Note: adjust the number of allowed checkouts against itemtypes and the loan period as per your library policy
4/ Cataloging - Lets take the following example: We've 3 copies of the book "iGenetics" by "Peter J. Russell. Of which 2 copies may be issued to general students and 1 copy to be reserved for book bank use. While entering the bibliographic data (NOT your holdings data) enter "BK" (the third itemtype defined in this example) in the MARC21 field 942$c (recorded in biblioitems.itemtype). ___BUT___ when you add your ***actual*** items (against that particular ISBN / ISSN etc), make sure you appropriately flag the 952$y (recorded in items.itype) field as either "RB" or "BB" as is the case with the specific copy. So, you could have two copies of "iGenetic" by Peter J. Peters as "RB" and one copy marked as "BB"
If you follow that above workflow, then the following SQL will give you the result you want:
SELECT b.biblionumber AS "Biblio No.", b.title AS Title, b.author AS Author, t.editionstatement AS Edition, t.publishercode AS Publisher, t.isbn AS ISBN, count(i.itemnumber) AS "No of Copies" FROM biblio b LEFT JOIN biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber) WHERE i.itype='BB' OR i.itype='RB' GROUP BY b.biblionumber
hope this helps
-- Indranil Das Gupta
Phone : +91-98300-20971 Blog : http://indradg.randomink.org/blog IRC : indradg on irc://irc.freenode.net Twitter : indradg
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=- Please exchange editable Office documents only in ODF Format. No other format is acceptable. Support Open Standards.
For a free editor supporting ODF, please visit LibreOffice - http://www.documentfoundation.org
--