Creating a library system on Access

This forum works best if you ask very specific questions, one per post.

For example you have used the Import wizard to import the Excel data into your table. What happened? Any errors? What exactly do you need help with?

Sorry, I’m a bit all over the place with it. 

I did import my excel sheet, to create three tables of books. That was as far as I got. 

After this point I’m a bot lost. I need to create a system to check books in and out

I have read various material about access, but it is all a bit more comlicated than I remember. 

As Tom said we need more info to answer the first 2 questions.

With regard to your third questions, I think the template is not suited to have several copies of a book (title). You will check out a copy of a book (with a barcode).

The information of the book (author(s) (max 2?), isbn and many more) should be stored once (not for each copy). So you need a table book and a related table copy. Transaction should be related to copies.

You might like to take a look at Library.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates how a basic lending library database or similar can be built.  The model for the database is as below:



As you can see it caters for the library holding multiple copies of the same publication by means of a related Items table, in which each row represents one physical copy of a publication.  When a publication is borrowed it is the specific copy of the publication which is associated with the borrower by means of the composite PublicationID/ItemNumber foreign key in Loans, which references the composite primary key of Items.

As regards importing data from Excel, such data will usually not be in a 'normalized' format which tallies with the set of related tables in a database.  The data will thus need to be 'decomposed' into a set of correctly normalized tables in Access.  You'll find a demo of how this can be done by executing a set of 'append' queries in DecomposerDemo.zip in my same OneDrive folder.  The rule of thumb is that data must be inserted into the referenced tables in each relationship before it is inserted into the referencing table.  My demo illustrates this, with a brief explanation of each stage of the process as you step through the demo.

Thank you I think I am going to focus on creating all of the tables in the access document so that I can ask more specific questions in the future.

Hi Ken,

That's brilliant I've had a look through it and the multiple copies problem doesn't seem as daunting now. I am going to start putting my tables in and see how far I get. I may be back to bug you.

Matt