I need some help w/ my database

[Antimatter]

Hello i'm new here, and what begun as a simple book list database project which would list my books and their author(s) and my first version was working great w/ droupdown boxes and so forth that would automaticaly add the item to their tables if the user (me) added a new topic or new author for example.

however one flaw was it didn't support multiple authors per books and ive been working on this for the past couple of days and well ive been doing alots of reading and researching and got a couple time an forum to work properly and allow me to add new more authors to an book but its still very messy and i'm trying to figure out how to setup my tables better, anyway they say a picture is worth a thousand words so here's two pictures.

this is the picture of my database relationship. Here's a little explaination behide the logic of couple of my tables, mainly the following: topic, trilogy, series, publisher. In the orginal version i had these as an droup down combo box that added the item into the list if they wasn't already there, it greatly speed up data entery because i could just type in a couple of letter and bam they would pop up and if not there i would just finish then jump to next field and it would automaticaly add it to the table for furture reference.

Plus ive been reading about normalizing and i also had alots of dupicate fields so it would help droup the numbers of errors in the database if i could select it from a droupdown list and so forth, in the book table it would just store the ID to the other tables, so it would be just bunch of numbers but when the user check on the data via the form the user would see the actual information and not the ID number. and i can use queries to join the database information together for me to review if i need to.

Anyway ive redesigned the database many time, the first incaration which was my first time ive ever used access in my life time was a simple flat table, second incaration was similar to the one showen above but i had the author as another one of those droupdown combo table w/ one entery and nothing of the tables had an autonumber as its primary key, the primary key was the only colume in the table which was like author name etc...

then i did some research and ran into problem concerning multiple authors to book so i begun my third incaration, and it seems to be processing along quite good, but i'm having problems w/ data entery and i'm not sure if its the proper way of seting many to many relationship.

here's the form that the wizard creates, i would like one similar to this but instead of text entery for a couple field i want droupdown, then i also want to add droupdown for the author subform so i can droupdown and select an author from the list, same w/ position and so forth, i also want to get rid of the ID's but if i do the database would complain that the tables are not joined or something, and for subform it would complain that it can't update it or something. tried to look the errors in the help file, no luck there

anyway here's the form as i would like it to look

btw ignore the misname of first, it should be author i drafted it up quickly

then the visual designer picture

anyway to explain the two pictures above, the first picture is how i want the form to look like and i have it that way but it will error out whenever i try to enter new data in the main and the subform but for both different type of errors.

then second picture shows the droupdown box as i want it to be, i store the author name in 3 seperate field in the author table, which is first middle last, anyway i want the combo box to take all three and merge it in the form of first middle last, and if the user (me) enters a new author not on the list it will seperate the names up and store them in each of their respective fields, anyway the first name and last name is pretty much mandatory but if it dosen't find a third then it will skip and put a blank for the middle name field.


Sorry for the hordes of questions but this is my very first database ive ever constructed and i only begun on the project two days ago. and i'm actualy surprised on the amount of progress ive done but i'm stuck on how to enter the data and show the data on the form part and i'm not sure if my tables are designed properly to do what i want them to do.

thank you for your help!


[edit:] ive also checked some of the avaiable demo database out there related with books but most of them uses authors as the master list, i want to use books as master list so when i check a book it will list all the authors and allow me to add remove authors from the books and also add and remove books from the list, most of the ones avaiable are one to one relationship or you select authors and it presents you with a list of books that the author wrote.

i figure that if i want a list of book that a partcluar author wrote i could probably draft up an query and search for a listing of that one partclular author. atleast from what ive learn about query which i'll admit isn't that much

 

[ecniv]

You've obviously put some work into it, and thought about your design, but you have found it appears to be flawed (slightly).

I take it this is for a school/college/uni project so keep all that you have done as a record, you can use it in the final documentation as an attempt and what happened.


Ok, this is only a suggestion:
Table layout:
tblBook
BookID - auto - pk
ISBN
Collection Title
Title
AuthorID
PublisherID
BookTypeID
YearPublished

tblAuthors
AuthorID - auto - pk
Forename
Surname
other fields of info

tblPublishers
PublisherID - auto - pk
PName
PAdd1
PAdd2
PAdd3
PAdd4
ZipPostcode
Other fields (tel, fax, email etc)

tblBookTypes
BookTypeID - auto - pk
BookType - text (hardback, paperback etc)

The Books table holds the main information, all the other tables are lookup information, so you can put them into screens for the user(s) to update.

The main table then can be updated with the book and whos publishing it and when etc...

As I put at the top this is only a suggestion and you can use it or warp it or whatever you like (Paper airplane?)

I'd also suggest putting the tables in one db and linking to it another mdb which holds the forms, queries, reports etc. Up to you how you implement it.


Vince

 

[Antimatter]

ecniv: thanks for that suggrestion, i will probably take some of the extra fields and add it into the database, but correct me if i am wrong from my first look at your suggrestion it appears to me that it only support one author many book model?

And actualy this is sort of an freelance project its not for school or anything of that sort, i'm actualy an second year in Software Engineering program at my university, and i wanted to create an database to keep track of my books, if you saw my book collection you'll understand why, last time i counted i had over 400 books and i'm starting to lose track of the books lol, anyway theyre a wide varity of books from novels to computer programing books which often has multiple authors.

The Database i have is sort of rough i want to refine it as soon as i get the overall design down pat. Basically i want to get the links, reference, and general form working before i add in additional data or should i add it in now?

the main area that i am having problem with is the support to have multiple authors per books. and have the ability to edit that at the same time as the creation/edition of the book information.

that's the main reason i tried to setup the database in the way that it was so i could have support for many to many links because authors can write many books, and books can have many authors.

and its a good idea about a second mdb to hold the forms and queies and report, one thing ive ran into is i can't view or create reports because i don't have a printer hookup is there a way around this?

 

[sdebo2]

Looking at your forms, when your curser is in one of the subform fields, they should turn into combo boxes that allow you to pick from a list. However if you want more control over how the subform looks you could change the “Default View” of the Form Properties for the sub form. Play with the three options (Single Form, Continuous Forms, Datasheet) but I think “Continuous Forms” might be what your looking form.

 

[Antimatter]

sdebo2: How would i do that i right clicked and select properities for the subform on my main form and i couldn't find anything called or similar to default view so i'm not clear on that care to explain more?

pat hartman: i thought so, thanks, but i'm still having problems geting the database to not error out with the tables are not joined or can't update the table when i add new entery when i input information into the forms. any idea on that?


On the relationship view notice that one of the table is called shared, it has an ID SharedID as the primary, i'm thinking of removing that and turning the primary Key to be the ISBN so it will be a one to one relationship with the book table, which is what i intended from the beguning, or would it be better to leave it alone? ive seen two point of view, one is to use auto number on all tables the other one is to use natural keys when you can, and ISBN is an natural key.

I removed the SharedID and now i have a one to one relationship between books and the shared table

but problem is i no longer can have dupicate ISBN, which is how i define multiple authors, so i'm restoring it back to its orginal format

 

[Antimatter]

i did some research before i came to the forum, and i ran over the many to many database, and it actualy was the model for my current database, it show me how to do a many to many relationship.

Anyway i didn't know that about the compound key i'll give that a shot, the shared ID is there to provide an unique value to each field, it currently is the key but i'll take your advice and try out the compound key.

now on your question on the position, it is basically an table that define the postition, such as "Authors, editer, illustrator" and so forth, and althought i haven't yet seen this happen but i still put it in the shared table because it could happen, basically by that i mean often authors are authors but someday i might run over a book in which the author is an illustrator for example. which i have't ran over yet but i still provide for it so i won't have to modify the database when i happen upon that case.

onice i have the compound key defined i'll give a shot again at recreating that darned form and i hope i can get it to work, the form is my primary point of problem, along w/ my newbishness to this whole thing.


[edit] YESSS!!! i got the form and database to work properly, appearly the compound key was part of the trick to get it to work, and i also tweeked the forms some more and it finally worked, i'll post up pictures of my final completed form when i'm done fixing and tweeking it up.

thanks for all of your helps



[edit] i said i would post up a picture of the form when i'm done, here's a picture its almost done just need addition of droupdown box and some shuffling and fixing of tab order then i'm on my way to data entery anyway for more questions on the form ive reposted under forms with some requests for help on doing the droupdown box.


hmm. odd i can't add image in this post, so go check the new thread in the form design forum.

 

[sdebo2]

To change your subform to be in Continuous instead of Datasheet view, you need to get to the form properties of the subform. It sounds like you were on the subform properties. In design view click the subform, if you have the properties window open it will show the subform properties. Now click the box at the intersection of the two rulers (a black box dot should appear there), now the properties window should show the form properties for the subform. The title in the Properties box should read “Form”. Then you should be able to find the Default View Property.

 

[Antimatter]

To change your subform to be in Continuous instead of Datasheet view, you need to get to the form properties of the subform. It sounds like you were on the subform properties. In design view click the subform, if you have the properties window open it will show the subform properties. Now click the box at the intersection of the two rulers (a black box dot should appear there), now the properties window should show the form properties for the subform. The title in the Properties box should read “Form”. Then you should be able to find the Default View Property.

got it, thanks, i thought the black dot was to adjust or change options on the ruler so i clicked the borlder of the subform. its now continious and it looks lots better now

 

[Antimatter]

- If that's the case, then you need to include PositionID as the third field in the primary key. That way you can have the same ISBN and AuthorID associated with different Positions - ie Author and Illustrator. PositionID serves the same purpose as BookingDate in my example.

that what i thought, when i did it i added in the position and now the whole record addition and deletation works, i just need to get the combo box on my form to work properly and then i probably can figure out how to parase the author name into seperate fields.

i posted over on the form subforum and didn't get much help there, i got some help geting the second colume to show up in my droupdown box but i can't assign or define new records for them because it will just overwrite the current one which is geting irrating, any ideas how? to fix that sitution, or am i missing something

basically what happens is when i'm editing a book already on record, and i select a different publisher, it will replace the current publisher stored in the publisher table so instead of having for example "wizards of the coast, and jove books" i instead would have "jove books, jove books" in my tables. which isn't what i want to happen, i just want it to reassign the publisher ID in the books table and if it's not in the list add it to the publisher table and assign the new id into the book tables. any ideas what i'm doing wrong here?

 

[Antimatter]

I prefer not to add lookup entries directly to the combo. I always add them in a separate process. There are numerous posts here on the NotInList event and how to use code there to add new records if that's what you want.

There is also code here to parse names, possibly even in the code forum. If not there, search for it.

by not adding directly you mean if i type in a new entery it will pop up a new form with request for me to add the new entery is that correct? not just add it if its not already there, i sort of did that i think, basically if it wasn't there it would pop up a dialong box asking if you wanted to add it or not, and for most part most of the combo box are single colume records, but for more complexer record for such as publisher it would probably be better to add it via a secondary form that pop up.

i haven't got to the actual code yet, i already got some protype code for notinlist event, but my problem is right now, i arleady got two publisher in my table for my test run. okay i get this record open w/ say publisher one, then i select publisher two, it won't change the ID in the book table it will just overwrite the publisher one field so i will end up with two copy of publisher two and no copy of publisher one, that is the problem i have right now, anyway to solve this problem without going into coding, i'm thinking i have some stucture in my form set up wrong?


onice i hammer that its probably smooth sailing from now on, i'm pretty familar w/ coding, but not with VBA but i can figure it out.

 

[Antimatter]

I looked at your example, and i played around with them and they made sense to me, atleast i thought it did, so i took the same concerpt in your example and applied it to my form, but no matter how i applied to it it still screwed up, so i'm uploading a copy of my database with my single book entery that ive been using to experment with. i haven't added the code for the notinlist event because i'm having other problems with the form, still having the same problems as i mentained in the above post.

basically what happen is when i applied some of the code idea from your example, it would appear to be working untill i save the field and come back then all i would see is a number and a blank, or the publisher name would be replaced with another publisher name.

So if someone would look at it and tell me if i'm entering the setting in the wrong spot or using the wrong setting on my form.

here's the file, its 118 kb, the attachment was refused so i uploaded it to my ISP file server.

Book List (118 Kb)


sorry if i'm being dense

 

[Antimatter]

I'm posting my solution to the problem if anyone else runs over the same problems as i did.

I spent several hours today trying to get it to work then i thought, hmm that control source seems interesting so i changed it from publisher to publisherID then access complained it was an auto number so i went hmm i might be on something here, so i changed it to publisherID in the book table, and bam it works, now i can change my publisher and it will not overwrite the other publisher now its down to just adding this to the other fields and adding in the notinlist code then i'll be on my way.

only thing remaining is figuring out how to do the author, but i'm thinking it will have something to do with the author ID, basically i select the author id then it will grab all 3 colume at the proper ID and merge them together for display on the form, but i just need to figure out how to code it.


and yep i was being dense but here's the solution above if anyone else is being dense like me