generating primary keys based on values in another table

[junkmale]

Hi...

i'm trying to do up a basic library database, and having problems with one part...

one of the tables (BOOKS) will have fields: "BOOK ID"(Primary Key), and "Number of Copies".

On another table(Book copies), I'm trying to generate unique call numbers for each copy of a book. There would then be two fields in this table: "BOOK ID"(foreign key) which will, according to the value of "Number of Copies", generate out unique call numbers for the book copies.

Rather confusing, so i use an example:
If i were to buy 5 copies of a new book, say Cinderella (ID: B0001) for the Library, i would key in ID: "B0001", Number of Copies: "5" in the BOOK table.

I then need the other table (BOOK COPY) to generate out 5 unique call numbers for me, when i key in ID: B0001 in the BOOK COPY table.

for eg, when i key in B0001, the following are generated: "C0001", "C0002", "C0003", "C0004" and "C0005" to represent each unique copy of the book.

Is this possible?

Or is there at least a way to make it easier for the data entry guy than to have to do it manually (ie. think up 5 unique call numbers for the books and key them in himself) ???

 

[namliam]

With some VBA coding and maybe a sample db its not that hard...

Regards

The Mailman

 

[junkmale]

as you can tell probably tell, i'm not too familiar with Visual Basic programming... and the sample db i found that comes along with ACCESS do not appear to carry the required function i require.

Am i right to say what i need is an "append query"???

do you happen to know anywhere i may find more suitable sample databases/templates???

thanks.

 

[namliam]

What i mean by sample db is a part of the DB you allready have. the 2 tables and a form which has manual input.

Am i right to say what i need is an "append query"???

NO well kind off but NO

SQL cant "THINK"... You need something can "think"
New book => 5 books
Book ID BookNR
B0001 1
B0001 2
B0001 3
B0001 4
B0001 5

SQL cant do that... * thinking now... maybe it can... definatly it can (do this, not think) *

Still your sample DB would be nice to have....
also a booknumber like my example above would be much nicer rather than your C0001. But if that is what you desire....

Regards

 

[junkmale]

i've done up a simple sample db... with 2 tables:BOOKS, & BOOK COPY, and the wizarded-form for BOOKS. Also keyed in some random book titles for you to work with.

I'm not even sure if what little I've set up is correct, because my intention is that once the person has keyed in the form, the values should appear in BOTH tables, and a new attribute should also be formed in BOOK-COPY: "CALL NUMBER", generating call numbers based on the value for "Number of copies".

As for the preferred format of Call Numbers generated, i'm looking more at something like C00001.05, for the "5th copy" of a book with ID B00001. This would of course, be the ideal scenario...

 

[junkmale]

Also keyed in some random book titles for you to work with.

sorry, removed those in the end cos was worried it wouldn't fit in with the limitations on file size here....

 

[namliam]

2 things!
1) dont use spaces in ANY names any time .... Its not good practice
2) Dont use any special characters like ( ) * etc in names either. Not good practice and will run you into trouble....

Also at this moment it only works for <= 10 copies you will need to expand the table if you want more....

Regards

 

[junkmale]

Thanks for the advice for the names... as you can probably tell, i'm quite new at this, and that came as good advice.

Thanks for the example you worked out... that worked great.
BTW, is the append query you created "redundant"? since the required work is done thru the command button in the BOOKS: form.

But I was hoping for COPYID to be a primary key in the BOOKCOPY table. i've been reading up more on SQL after looking through your example and now i'm trying to make the button also insert another column CALLNUMBER into BOOKCOPY table. The value of this should be something like C0000105 for copy 5 of a book with BOOKID B00001....

This CALLNUMBER will then be the primary key for the BOOKCOPY table. Some difficulty so far, but I'm reading up more and hoping to find the answer soon.

 

[namliam]

DONT GO THERE!!!

I created a compound KEY the combination of BOOKID and CopyID should be unique.... Leaving your BOOKID in tackt so you can link back to your BOOKID in your books table

BookID CopyID
B00001 C00001

BookID Title
B00001 The chronicals of a Mailman

If you go your way and combine BookID and CopyID into 1 field like your suggested you lose that ability which is one of the strong points of a relational DB like Access....

BTW, is the append query you created "redundant"? since the required work is done thru the command button in the BOOKS: form.

Yes its redundant, but i left it to show you how i did it and how to go from a QBE designed query to SQL in a command button.

Regards

 

[junkmale]

DONT GO THERE!!!

I created a compound KEY the combination of BOOKID and CopyID should be unique.... Leaving your BOOKID in tackt so you can link back to your BOOKID in your books table

BookID CopyID
B00001 C00001

BookID Title
B00001 The chronicals of a Mailman

If you go your way and combine BookID and CopyID into 1 field like your suggested you lose that ability which is one of the strong points of a relational DB like Access....

I understand what you mean by a compound key and how it allows me to link back to BOOKID in books table.

But i was thinking along the lines of having this :

CallNumber BookID CopyID
C0000105 B00001 05

CallNumber("C";BookID;CopyID) would be the primary key and BookID would be a "foreign key", which still allows me to link back to the books table??? is this right?

 

[namliam]

That is right, however... your doubling up now which isnt a good practice either....

CallNumber BookID CopyID
C0000105 B00001 05

See you have 000001 and 05 doubled up If you absolutely need the compound field then leave out the CopyID since that would be part of your CallNumber....

My advice is go with copy id only... Maybe even taking away the C000001 and making it a numberic field only:
BookID CopyID
B00001 1
B00001 2
B00001 3
B00001 4
B00001 5

Same with the bookID really... Point beeing your limmitting yourself to (in this case) B00001 ... B99999 beeing a little less than a 100.000 books....

If you leave it all numberic you can have 10 million books if you want to...

Regards

 

[junkmale]

Initially, I wanted to put B00001 for books, and P00001 for periodicals, and so on....
this was because i thought it would be clearer and easier to differentiate....
after what you said, i think the advantage of easily increasing capacity outweighs the ease of differentiating between IDs...

The end result should then look something like this
Table: BookCopy

BookID: CopyID: CallNumber
1: 01: 1B01
1: 02: 1B02
2: 01: 2B01

number of copies restricted 99 for any book.

I'm still thinking of generating a call number though, this would help differentiate a book from a periodical, for example in borrower's records.

Some doubling up, as you say here though... or perhaps i could remove CopyID and save half the doubling?

BookID: CallNumber
1: 1B01
1: 1B02
2: 2B01


What do you think?

 

[junkmale]

BookID: CallNumber
1: 1B01
1: 1B02
2: 2B01

To do this, i was thinking what i essentially need to do is "multiply" the BookID by 1000. then "add" CopyID to it... for example for copyID 3 of bookID 1, we'll have 1003. This number we'll then force into format 0000B00.

how can i do this in access??? and if i force into format 0000B00, i'm restricting myself again to call numbers less than 9999....
does %000B00 work? sorry if some ideas here are really absurd, cos i'm really too new at this and need to start making some serious mistakes.

 

[namliam]

What do you think?

I think you might know what I think... NO !

Dont create a callnumber, rather use a compound key, from 2 fields

BookID, CopyID
1 1
1 2
1 3
1 4
2 1
2 2
2 3

Then in you book table have a field to tell you what "kind of book" you have ....

---------------------------------------------------
On your second post, Your method is not going to work..

If you want to 'concatinate' 3 fields like that you cannot use your method. Whereas if you are turning around a date for instance (all number) the you could
2003 * 10000 + 9*100 + 11 = 20030911
Allthough cint(Format(11-09-2003,"yyyymmdd")) will do the same and easier...
--------------------------------------------------------
I think you are going to back yourself into a corner if you are going to use a "CallNumber". I think the compound key would be preverable. If you need 1 field to rever to maybe use an autonumber.... as the "Foreign Key" for bookID -Callnumber-

Regards

P.S. just a question on the side: Have you ever heard the term "Normalization"?

 

[junkmale]

okie... actually i kind of agree with you. In fact, in between the time i posted and the time i read this post of yours, i'd realised probably that i do not really need a call number.

on a seperate note,
this form for data entry, ie Books which had been previously included, should be the "main" form needed for the library. That means, when the library gets a new book, the guy has only to open this form, and key in ALL the information that comes with the books, including information meant for other tables, eg Publisher, Author.

the question arises here.

for example, for the data for "PublisherId", i need in both BOOKS and PUBLISHER table.

ideally for the data entry guy, a "look-up field" generated from the existing entries in PUBLISHER table would be excellent.

BUT, what if there is this book is published by a new/unique publisher??? after the guys clicks the drag down menu and cannot find the publisher name and ID, i would need spaces on the same form for the guy to key in the new PublisherName and PublisherID and other attributes of the PublisherTable.

That should be easy enough. BUT, how do i make it such that it's an "either" function? such that the person can only choose either from the drop down menu, or to key in the various fields.

ie. He cannot choose Sunrise Publishers - ID0001 from the lookup field and then go ahead to key in Novo Publishers in the optional fields provided.

P.S. just a question on the side: Have you ever heard the term "Normalization"?

i had just read about it... something about optimisation of tables, ie mainly reducing duplication of data right? not too clear on it though...

 

[namliam]

Before designing DB's you should know just about all there is to know about Normalization! It indeed has to do with optimization, part of it your doing allready ( seperately storing the Publisher and using an ID to link it to a book for instance)

To Add to a dropdown list, add a popup form On double click or On not in list or add a small button behind it. Then open a form, where the user can input his data on the (new/unknow) publisher.... Then return to your 'main' form...

Regards

 

[junkmale]

plenty thanks for your valuable help along the way.


I'll scurry along with my database, and come back again for help when new problems arise.

 

[junkmale]

To Add to a dropdown list, add a popup form On double click or On not in list or add a small button behind it. Then open a form, where the user can input his data on the (new/unknow) publisher.... Then return to your 'main' form...

Regards

Hi there again, sorry, but need more help.
probably elementary to you, but i've wasted hours trying to get it to work.
I've tried to do what you've said above, to try to add a popup form On double click or On not in list. but I can't quite get it to work. What exactly is suppose to happen? Will there be an extra line in my dropdown "Not in list" which i select and a form pops out? Could you give me the maybe some more specific instructions or code to make it work?

Alternatively, I've added a button which simply opens the formublisher. The problem with this solution is that after keying in the information for PublisherID:108, and closing this form, the the information of PublisherID: 108 is not updated in my dropdown list for the first form. That is to say I still cannot select this new publisher. I have to close the form and re-open it for the publisher to be available for choosing. How do i solve this?

Looking forward to your reply!

 

[namliam]

To show your new publisher add a requery of the combobox after closing the popup form....

Besides having a module to handle the not in list event you must also set Limit To List to yes, otherwise the not in list event will not be triggered...

Hope that helps you

Regards