If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
ACCESS DVD Database
Hello folks,
I have a HUGE Excel 2007 DVD spreadsheet with a nice front end, and a section to cover my purchased library. If anyone is interested in looking, I can post a screenshot in a binary Misc pictures group to show you the front end I have made for it using lookups and a little visual basic code to paste the disc cover images. What I would like to do is make a nice Access version of this database. Particularly since it currently carries ove3r 110,000 records, which makes it hard for excel. I currently have several sheets in one workbook, all of which have an ID column and then a column for the data, such as DVD Title, and MPAA Rating, and where I bought it, MSRP Price, sound, Disc Aspect ratio, etc. So, essentially, I have a series of two column sheets, and one at the end that carries all of my purchased data, and the pointers to the image files. The first sheet in the workbook is my "master view panel", which is a vertically oriented pane of all the details for a single title. That sheet has a tough time importing into access, since the orientation makes it hard for access to garner what would be fields, etc. That sheet is also where all the VLOOKUPS, and VB Code is at. All of my attempts at constructing an ACCESS front end fail to get me there. I was used to "Paradox" years ago where user view panes were "forms". Is this paradigm still true? Do I bring in the tables, make the relationships, and then create a form for viewing/stepping though/searching the data? Or is that now a "report" of some kind? None of the templates I found online even come close to approaching what I am looking to do, and my nil experience with access makes it even harder. I felt I could import all of the sheets, one by one, and then create the indexed relationships to each. The problem is that access wants to put in its own ID field every time. It would seem, I got "issues". :-] I have posted the screenshot in alt.binaries.pictures.misc though the volume of posts there make it a nightmare to haul in headers from. The post is DVD Database screen shot, for anyone interested. Thanks for any help you can provide. I am a total Access newbie. :-[ |
#2
|
|||
|
|||
ACCESS DVD Database
Have you looked at the template he
http://office.microsoft.com/en-us/te...CT102144001033 The above is by no means complete, but if you take a look at the table designs, you can see how ms-access performs without the need for vlookups.... And, there also some sample table designs here to give you some ideas: http://www.databaseanswers.com/data_models/index.htm -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#3
|
|||
|
|||
ACCESS DVD Database
On Mon, 28 May 2007 03:47:02 -0600, "Albert D. Kallal"
wrote: Have you looked at the template he http://office.microsoft.com/en-us/te...CT102144001033 Yes, thanks but that DVD database app was the one I was describing as pretty sad. It is an empty database. I have a 31MB full database that I want to build a front end around. Since I have never seen a really powerful Access database or front end, I don't know how or where to start. The above is by no means complete, but if you take a look at the table designs, you can see how ms-access performs without the need for vlookups.... Yes. I know that databases typically perform these functions in a much more native manner. And, there also some sample table designs here to give you some ideas: http://www.databaseanswers.com/data_models/index.htm I will browse through and try to find the most complicated one I can, so that I can see what some of its features are, and how they might apply to my development. Thanks. |
#4
|
|||
|
|||
ACCESS DVD Database
In addition to what I posted, the question becomes how normalized you want
your data to be. Further, do you have provisions for the actors in movie? Do you want to be able to search by actors? The normal approach here is to import the data, and then decide if you want to re-form the data to multiple tables. or, if you just want to keep your simple data as you have. It is not clear if you have "repeating" data for a single dvd (by repeating, I mean "many" actors can be attached to one movie). if you just have a plain Jane flat file without any repeating data, then the import is easy. If you have "repeating" data for a single dvd, then more efforts in design will be needed. As for the link to the image field, I would actually just leave that as a normal text field. You can then place a image control on the form..and it will display the image for you. Take a look at the sample. And, if you want to get fancy, you could have the data base pull the data from the IMDB (so, you can have a list of actors etc. for your DVD's, but don't have to type them in). -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#5
|
|||
|
|||
ACCESS DVD Database
On Mon, 28 May 2007 03:57:06 -0600, "Albert D. Kallal"
wrote: In addition to what I posted, the question becomes how normalized you want your data to be. Further, do you have provisions for the actors in movie? Do you want to be able to search by actors? The normal approach here is to import the data, and then decide if you want to re-form the data to multiple tables. or, if you just want to keep your simple data as you have. My data is not simple. The main view sheet looks at about twelve sheets for its compilation. Each "table" (sheet) is comprised of a single ID column (field) and some data, like title or such. That would be quite normalized if I were to import each of those tables into access as separate tables, no? All I would have to do presumably is tie them all together with the ID field, which is the unique identifier for each. It is not clear if you have "repeating" data for a single dvd (by repeating, I mean "many" actors can be attached to one movie). I intend on incorporating an actors list for each title as well. In that table, each actor would have a list of ID numbers associated with each film he or she has appeared in. So, it would be a "many to one". Problem is that I knew how to set up such relationships in Paradox quite easily, as I was used to the API, and interface for construction of the database app. In Access, I am lost on this. I tried once to create some relationships and got a crash, so I am thinking of making a spreadsheet that is just a small sample of my current massive workbook, and getting it going with that, then filling in the data later. if you just have a plain Jane flat file without any repeating data, then the import is easy. I had no problem importing excel sheets into tables in access. I did have a problem with my main view sheet because it was oriented vertically on a field by field basis, instead of across like most sheets, and access puked on that import. The thing I am having a problem with is that access wants to put its own ID field in front of mine, and making the relationships between tables, and finally, form construction to view all the data one "film" record at a time. I can add the search features later. If you have "repeating" data for a single dvd, then more efforts in design will be needed. No. Each title has a single ID number, and several pieces of data to go with it, like UPC code, sound, aspect, etc. The actor's table will be the only place where a given ID number will be repeated many times. As for the link to the image field, I would actually just leave that as a normal text field. You can then place a image control on the form..and it will display the image for you. Yes. Something that in excel required a visual basic macro as the images float over fields (cells), not in them. Take a look at the sample. And, if you want to get fancy, you could have the data base pull the data from the IMDB (so, you can have a list of actors etc. for your DVD's, but don't have to type them in). I have a complete database. I have a complete actors list and directors list. I just need to piece them together in access, and am having a hard time getting started with it. The crash/lockup was one thing, so I'll start with a concatenated set of films instead of my entire spreadsheet when I go to establish all the relationships, which is where it crashed last time. |
#6
|
|||
|
|||
ACCESS DVD Database
My data is not simple. The main view sheet looks at about twelve sheets
for its compilation. Each "table" (sheet) is comprised of a single ID column (field) and some data, like title or such. That would be quite normalized if I were to import each of those tables into access as separate tables, no? Well, the "trick" or concept here is that you DO NOT want to create (well, import) a table for just one piece of information. Normalizing your data simply means you build a correct data model that models your needs. So, if you have a field that is the description for the movie, then you do NOT want to add the extra complexity of creating a extra table. If you ONLY have one field, and it not repeating data (like many actors), then that field should be in your main table. So, to answer should you import this data into "many" tables, the answer is only going to be yes if that data is repeating. So, if you have a table that has "many" actors, then that would continue to be another table. So, we would have two tables so far tblDVD --- tblActorsInMovie So, simply making lots of tables is generally not a good idea for the sake of making more tables. You ONLY create related tables if the related data is repeating. In your case, repeating data could be in these extra lookup tables, or even in the main table (eg: actor1, actore2 is two fields, but they should be like the above setup, and this I can have as many actors for a single dvd movie as I need. (no hard coding of how many actors with fields named actor1, actor2 etc.). All I would have to do presumably is tie them all together with the ID field, which is the unique identifier for each. You main tblDVD would use autonumber id. For child tables, they need a standard long number field that will be used to point back to the main tblDVD id. So, the child tables don't really need autonumber fields (but, you can and should consider just leaving the default "id" as the primary key for those child tables. All of those child tables will need a plane Jane standard long number field for the relation. When you add a child record YOU MUST SET this value. (if you use a sub-form,t hen ms-access will set this value for you if you setup the relational correctly). It is not clear if you have "repeating" data for a single dvd (by repeating, I mean "many" actors can be attached to one movie). I intend on incorporating an actors list for each title as well. In that table, each actor would have a list of ID numbers associated with each film he or she has appeared in. Well, don't confuse the actors table with that of "tblActorsInMove". In our above design so far, we don't have a table of actors. however, that table could be created, but it not really going to be a enforced relationship. It just a table of actors. So, we have now: tblDVD --- tblActorsInMovie tblActorsInMoive will be: fields: id dvd_id actor_id Now, it going to be very hard to remember the actor_id, and have to type in the id (primary key) of the tblActor table (the pk of all of our tables is assumed to be id). So, in our form for tblActorsInMove, we likely use a combo box that lets us view/search by actor name, but stores the id. So, we have to crate a table for the across (since then we only every type in the actor name once). tblDVD --- tblActorsInMovie tblActors Note how I not really set tblActores up in the relation. It not really an enforced relation. If I add a new DVD, or delete a DVD, we not going to do anything to the tblActors. It just a simple table of actors that we have. However, we will MOST certainly delete the list of actors that belong to that dvd in the tblActoresInMove table when we delete a dvd. Problem is that I knew how to set up such relationships in Paradox quite easily, as I was used to the API, and interface for construction of the database app. The above setup should get you started. I suppose, you could relate the tblActoresInMove to the tblActores (you can do this, but it would really only be for documenting purposes - will not actauly do anything for you) No. Each title has a single ID number, and several pieces of data to go with it, like UPC code, sound, aspect, etc. Well, if you don't have any repeating data, then just place all of the fields in one main table. Extra tables will be just extra work, and not benefit you in any way. IF you still need, or use the "id" you currently have, then have it exposed in a field, but I would continue to use the internal autonumber id for the relationships stuff (you, or your end users will NEVER even see this internal autonumber...as it just for the relationship, and not for humans to see, or use as some identification for the dvd. I have a complete database. I have a complete actors list and directors list. Great, so now we have: tblDVD --- tblActorsInMovie tblActors tblDirectoors So, we have ONE standard long number field in the tblDVD that will store the tblDriecots (that means we will have only ONE director for each DVD). You could as mentioned type in this value by memory, but it going to be hard to know the directors "id" number to type into that field in tblDVD (once again, we use a combo box on the form. And, once again, this is not really relationship, but more a lookup value for our easy sake. So, would consider the diagram like: tblDVD --- tableDirctors --- tblActorsInMovie---tblActors -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#7
|
|||
|
|||
ACCESS DVD Database
So for all of the "only appears as one info item per DVD entry/title
data", I should incorporate all such data into a single table? That makes sense. That way, my purchase info would be a second table, and the actors a third, and the director table another as some films do have more than one, and each director and actor are associated (usually) with more than one film/title as it were. So I should be able to do this with only about three or four tables, one of which will be huge, and the others relatively small. Thanks for your help. I will re-merge all of the once per title data into a single table, and try to wade through my old paradigm thoughts about tables and relationships, and hopefully will become more familiar with access. The DVD template online was fairly confusing. I'd rather make the database, and main view form, and then make the "main switchboard" paradigm stuff. That is one of the things that confused me, but I am muddling through. On Mon, 28 May 2007 10:28:13 -0600, "Albert D. Kallal" wrote: My data is not simple. The main view sheet looks at about twelve sheets for its compilation. Each "table" (sheet) is comprised of a single ID column (field) and some data, like title or such. That would be quite normalized if I were to import each of those tables into access as separate tables, no? Well, the "trick" or concept here is that you DO NOT want to create (well, import) a table for just one piece of information. Normalizing your data simply means you build a correct data model that models your needs. So, if you have a field that is the description for the movie, then you do NOT want to add the extra complexity of creating a extra table. If you ONLY have one field, and it not repeating data (like many actors), then that field should be in your main table. So, to answer should you import this data into "many" tables, the answer is only going to be yes if that data is repeating. So, if you have a table that has "many" actors, then that would continue to be another table. So, we would have two tables so far tblDVD --- tblActorsInMovie So, simply making lots of tables is generally not a good idea for the sake of making more tables. You ONLY create related tables if the related data is repeating. In your case, repeating data could be in these extra lookup tables, or even in the main table (eg: actor1, actore2 is two fields, but they should be like the above setup, and this I can have as many actors for a single dvd movie as I need. (no hard coding of how many actors with fields named actor1, actor2 etc.). All I would have to do presumably is tie them all together with the ID field, which is the unique identifier for each. You main tblDVD would use autonumber id. For child tables, they need a standard long number field that will be used to point back to the main tblDVD id. So, the child tables don't really need autonumber fields (but, you can and should consider just leaving the default "id" as the primary key for those child tables. All of those child tables will need a plane Jane standard long number field for the relation. When you add a child record YOU MUST SET this value. (if you use a sub-form,t hen ms-access will set this value for you if you setup the relational correctly). It is not clear if you have "repeating" data for a single dvd (by repeating, I mean "many" actors can be attached to one movie). I intend on incorporating an actors list for each title as well. In that table, each actor would have a list of ID numbers associated with each film he or she has appeared in. Well, don't confuse the actors table with that of "tblActorsInMove". In our above design so far, we don't have a table of actors. however, that table could be created, but it not really going to be a enforced relationship. It just a table of actors. So, we have now: tblDVD --- tblActorsInMovie tblActorsInMoive will be: fields: id dvd_id actor_id Now, it going to be very hard to remember the actor_id, and have to type in the id (primary key) of the tblActor table (the pk of all of our tables is assumed to be id). So, in our form for tblActorsInMove, we likely use a combo box that lets us view/search by actor name, but stores the id. So, we have to crate a table for the across (since then we only every type in the actor name once). tblDVD --- tblActorsInMovie tblActors Note how I not really set tblActores up in the relation. It not really an enforced relation. If I add a new DVD, or delete a DVD, we not going to do anything to the tblActors. It just a simple table of actors that we have. However, we will MOST certainly delete the list of actors that belong to that dvd in the tblActoresInMove table when we delete a dvd. Problem is that I knew how to set up such relationships in Paradox quite easily, as I was used to the API, and interface for construction of the database app. The above setup should get you started. I suppose, you could relate the tblActoresInMove to the tblActores (you can do this, but it would really only be for documenting purposes - will not actauly do anything for you) No. Each title has a single ID number, and several pieces of data to go with it, like UPC code, sound, aspect, etc. Well, if you don't have any repeating data, then just place all of the fields in one main table. Extra tables will be just extra work, and not benefit you in any way. IF you still need, or use the "id" you currently have, then have it exposed in a field, but I would continue to use the internal autonumber id for the relationships stuff (you, or your end users will NEVER even see this internal autonumber...as it just for the relationship, and not for humans to see, or use as some identification for the dvd. I have a complete database. I have a complete actors list and directors list. Great, so now we have: tblDVD --- tblActorsInMovie tblActors tblDirectoors So, we have ONE standard long number field in the tblDVD that will store the tblDriecots (that means we will have only ONE director for each DVD). You could as mentioned type in this value by memory, but it going to be hard to know the directors "id" number to type into that field in tblDVD (once again, we use a combo box on the form. And, once again, this is not really relationship, but more a lookup value for our easy sake. So, would consider the diagram like: tblDVD --- tableDirctors --- tblActorsInMovie---tblActors |
#8
|
|||
|
|||
ACCESS DVD Database
That makes sense. That way, my purchase info would be a second table,
and the actors a third, and the director table another as some films do have more than one, and each director and actor are associated (usually) with more than one film/title as it were. Well, the purchase info should ONLY go in a 2nd table if there going to be repeating data, or more then on "set" of purchase information. If each dvd only have non repeating fields (purchase date, purchase amount etc), then that belongs in the main table. There is ZERO reason to create and maintain anther table for fields that simply attached to the DVD and are not repeating values. So, as far as I can tell/guess based on your information, those fields should be in the main table. So I should be able to do this with only about three or four tables, one of which will be huge, and the others relatively small. Yes, that sounds good to me.... Just keep in mind the two cases we have: tblDVD will have a field (long number) that lest you enter the directors id. This is not a one to many relational, but a simply field that looks up the director value. The 2nd case is the "many" actors. This is repeating data that belongs to one record (is a classic one to many relationship). So far with your given design, we have 3 tables...... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#9
|
|||
|
|||
ACCESS DVD Database
soory,...I mean 4 tables.....
|
#10
|
|||
|
|||
ACCESS DVD Database
|
|
Thread Tools | |
Display Modes | |
|
|