Library system in access [help and advice plz]1

[shanmukha.kovvuri]

hey im currently creating a small library system for my college
project, and i need some help and advice.

My tables are;


* PK = Primary Key


tblBook
(PK)ISBN
Title
AuthorID
PublisherID
NumberOfCopies
CategoryName
PublicationYear
Picture


tblBookCopy
(PK)CopyID
ISBN
DatePurchased
ReplacementCost
Lending


tblBookLoan
(PK)LoanID
CopyID
MemberID
DateDueBack


tblMember
(PK)MemberID
Name
Surname
Sex
Address1
Address2
PostCode
TelNumber


i also have other tables, Author, Publisher, Category.
anyway, since ISBN is unique to each title of book, if i have
multiple
copies of that book, each will have to be identitfied, im using
CopyID. For example NumberOfCopies of a book in tblBook is 5, how can
tblBookCopy be made to add 5 unique entries to of that Book, if you
understand wat i mean. also im new at this, but any help will be much
appreciated.

 

[storrboy]

hey im currently creating a small library system for my college
project, and i need some help and advice.

My tables are;

* PK = Primary Key

tblBook
(PK)ISBN
Title
AuthorID
PublisherID
NumberOfCopies
CategoryName
PublicationYear
Picture

tblBookCopy
(PK)CopyID
ISBN
DatePurchased
ReplacementCost
Lending

tblBookLoan
(PK)LoanID
CopyID
MemberID
DateDueBack

tblMember
(PK)MemberID
Name
Surname
Sex
Address1
Address2
PostCode
TelNumber

i also have other tables, Author, Publisher, Category.
anyway, since ISBN is unique to each title of book, if i have
multiple
copies of that book, each will have to be identitfied, im using
CopyID. For example NumberOfCopies of a book in tblBook is 5, how can
tblBookCopy be made to add 5 unique entries to of that Book, if you
understand wat i mean. also im new at this, but any help will be much
appreciated.

Do you mean to limit the number of entries in tblBookCopy to the
number in tblBook?
Makes more sense to me to not use the tblBook.NumberOfCopies field and
if you ever need to know how many copies there are, count the number
of occurances of the ISBN number in tblBookCopy.

 

[shanmukha.kovvuri]

Do you mean to limit the number of entries in tblBookCopy to the
number in tblBook?
Makes more sense to me to not use the tblBook.NumberOfCopies field and
if you ever need to know how many copies there are, count the number
of occurances of the ISBN number in tblBookCopy.- Hide quoted text -

- Show quoted text -

well the reason u need a number of copies field is that say u have
more than 1 copy of a book, say 5 of them this means that all 5 books
will have the same ISBN, repeated. i thought it would be better to add
a number of copies add assign a unique CopyID to each book. Also maybe
the Number Of copies should be in the BookCopy table ???. i was also
thinking about in a form i have to enter a book that has come in
stock, i will have to enter the number that have come in too, the
system must then like some how give book a CopyID. plz anymore help
will be appricated

 

[storrboy]

well the reason u need a number of copies field is that say u have
more than 1 copy of a book, say 5 of them this means that all 5 books
will have the same ISBN, repeated.

Set the ISBN field in tblBookCopy to Allow Duplicates. This will allow
more than one entry. To keep the copies unique, use the ISBN
number and the CopyID number as a composite-key. This will still
allow multiple ISBNs but only in unique combinations with the
copyID.

Also maybe
the Number Of copies should be in the BookCopy table ???.

As originally mentioned, just use the Count() funtion in a query
or form control to determine the number of entries of a given
ISBN to see how many copies you have.

i was also
thinking about in a form i have to enter a book that has come in
stock, i will have to enter the number that have come in too, the
system must then like some how give book a CopyID. plz anymore help
will be appricated

Are the books actually being marked with a copy ID? Will you know
what book is which copy entry. These answers and the format and
type of copy id will help determine how to enter them, but it is
not likely to be too difficult.

 

[Fred Boer]

Dear Shanmukha:

Do you realize you are getting responses to this question in the newsgroup
microsoft.public.access.gettingstarted? You might want to check there
also...

Cheers!
Fred Boer