Hello, some background before my question… I’ve been a long time excel user, I’ve always found a reason to go to excel when I probably should have been using access. So I have a tendency to think in flat single table methods…Well I’m trying to come out of my shell per say and I figured I would do it with something that should be small and somewhat easy to start with. I’ve been collecting comic books since I’ve been a small kid, I’ve inherited a few and yes I still do collect some. (What can I say, I’m a kid at heart… and a giant nerd) Anyway since my collection is getting rather large I figured it would be nice to have an accurate inventory for collection purposes as well as if I ever decide to sell anything. That said, while having read a lot about how access works and knowing a lot of the theory… I primarily learn by doing… I could literally learn more in 1 hour by doing something then I could by reading 100 books on the subject, so I’m probably going to ask some stupid questions and for that I apologize.
To start off with before I ask my questions I should probably outline what I’m trying to achieve with my database as well as the general way I’m setting up the db…. Typically I think in very flat terms due to my background with excel so I’m trying to force myself to set this up in a very nonflat way… so if I overdid it please feel free to speak up.
Table 1: Publishers… this is a very simple table that I designed to combat one of my biggest problems in anything… spelling. It’s simply a list of every publisher of every comic that I collect. That way I can make drop down menus later so I don’t have to spell the publisher many times.
Table 2: Series List… This is similar to table 1 in that it’s simply a list of comic books series’ that I collect…walking dead, spawn, batman, etc. This part is for the same reason table 1 exists… so I don’t screw up spelling of just a few entries which throw off things later. In addition I also added a few fields in table 2 to make things easier on myself later. The first field for this was which publisher published each series… I did it here so I only would have to enter it once for each series instead of possibly 10s if not hundreds of times for each comic book issue. The other convenience item I added to this table was to add a field for current volume… this is one of the areas I have issues with so I’ll address this in more detail later.
Table 3: Issue List… This is the table where I want each individual issue that is collected, I want the comic series, the issue, the volume, and Quality… The catch here which I think is just a personal issue is I also kind of want to add all the details I’ve established in other tables, such as publisher, and price, and current value etc. However I think I may just be trying to make this table flatter so it’s something I know… again I’ll get to this later when I ask my questions.
Table 4: Storage Location… This table is pretty obvious, I have a large collection so I have many storage boxes… I have them numbered and I want to have a field in this table that has series, volume, and issue from table 2 and then another field that has storage location… that way I can find any comic somewhat quickly in the future.
Table 5: Value: In this table again I want comic series, issue, and volume, along with fields for cover price, then here I get a lil iffy....I want a price for each year from here on out… so Ill enter 2014 value, then next year I’ll probably just add a 2015 value field and so on… that way I can see trends and graph them if I really felt the urge.
Table 6: Grading… This is a pretty pointless table simply there to make it easier in table 3 where I use the quality field… there is a bunch of options for grading a comic book so instead of trying to remember them I’m using a pull-down menu again.
Ok there is the basic structure Im figuring on using… if you see any major structural issues please speak up about them since there may be an issue I don’t know to ask a question about…. You guys have been at this longer then I have so you may anticipate some questions or problems I’ll have before I have them.
Question 1: Is my structure ok? Pretty simple question there lol.
Question 2: I would like to use the current volume I discussed earlier in table 2 to make my life easier… Finding the volume isn’t always easy on a comic book so once I get my collection all entered in as I buy new current comics instead of having to lookup the current volume of each series every time I would like a default value in that field of whatever the latest volume for that series is. Problem is I don’t know how to do this, Id imagine it would be with dlookup however I don’t know that it has the capability to do what I would like. Basically once I get everything set up and make a form for entering new comic books I would like to have it set up so that when I enter say the walking dead, it cross references table 2 for that series then it looks at the field for current volume and it uses that entry as the default value for the volume field… so my question is… is that possible? And if not is there some other way to achieve what I’m looking for?
Question 3: for table 4 and 5 since I need a list of all the comics I have which means I need to copy a few of the fields from table 2 to complete table 4 and 5 (namely the issue, series, and volume) how would you accomplish this in the most efficient manner? To copy a field into 1 table from another table.
Question 4: Is there any reason to want the things such as publisher, which is in table 2, as well as the storage location and values into table 2 other then just to make table 2 look nice and flat so I can look at it like it was an excel sheet? I keep trying to do this and Im starting to think the only reason I am is to make it more excel like again… I assume the proper way would to keep most the data in separate tables then use reports and forms to just look at the various tables and bring all the data up there…. Am I correct in this assumption?
Thanks for the help… it’s amazing how set in your ways you get when trying to learn this after excel… I wish I would of learned this before excel Id imagine it would be easier to go the other way in learning.
Thanks again!
To start off with before I ask my questions I should probably outline what I’m trying to achieve with my database as well as the general way I’m setting up the db…. Typically I think in very flat terms due to my background with excel so I’m trying to force myself to set this up in a very nonflat way… so if I overdid it please feel free to speak up.
Table 1: Publishers… this is a very simple table that I designed to combat one of my biggest problems in anything… spelling. It’s simply a list of every publisher of every comic that I collect. That way I can make drop down menus later so I don’t have to spell the publisher many times.
Table 2: Series List… This is similar to table 1 in that it’s simply a list of comic books series’ that I collect…walking dead, spawn, batman, etc. This part is for the same reason table 1 exists… so I don’t screw up spelling of just a few entries which throw off things later. In addition I also added a few fields in table 2 to make things easier on myself later. The first field for this was which publisher published each series… I did it here so I only would have to enter it once for each series instead of possibly 10s if not hundreds of times for each comic book issue. The other convenience item I added to this table was to add a field for current volume… this is one of the areas I have issues with so I’ll address this in more detail later.
Table 3: Issue List… This is the table where I want each individual issue that is collected, I want the comic series, the issue, the volume, and Quality… The catch here which I think is just a personal issue is I also kind of want to add all the details I’ve established in other tables, such as publisher, and price, and current value etc. However I think I may just be trying to make this table flatter so it’s something I know… again I’ll get to this later when I ask my questions.
Table 4: Storage Location… This table is pretty obvious, I have a large collection so I have many storage boxes… I have them numbered and I want to have a field in this table that has series, volume, and issue from table 2 and then another field that has storage location… that way I can find any comic somewhat quickly in the future.
Table 5: Value: In this table again I want comic series, issue, and volume, along with fields for cover price, then here I get a lil iffy....I want a price for each year from here on out… so Ill enter 2014 value, then next year I’ll probably just add a 2015 value field and so on… that way I can see trends and graph them if I really felt the urge.
Table 6: Grading… This is a pretty pointless table simply there to make it easier in table 3 where I use the quality field… there is a bunch of options for grading a comic book so instead of trying to remember them I’m using a pull-down menu again.
Ok there is the basic structure Im figuring on using… if you see any major structural issues please speak up about them since there may be an issue I don’t know to ask a question about…. You guys have been at this longer then I have so you may anticipate some questions or problems I’ll have before I have them.
Question 1: Is my structure ok? Pretty simple question there lol.
Question 2: I would like to use the current volume I discussed earlier in table 2 to make my life easier… Finding the volume isn’t always easy on a comic book so once I get my collection all entered in as I buy new current comics instead of having to lookup the current volume of each series every time I would like a default value in that field of whatever the latest volume for that series is. Problem is I don’t know how to do this, Id imagine it would be with dlookup however I don’t know that it has the capability to do what I would like. Basically once I get everything set up and make a form for entering new comic books I would like to have it set up so that when I enter say the walking dead, it cross references table 2 for that series then it looks at the field for current volume and it uses that entry as the default value for the volume field… so my question is… is that possible? And if not is there some other way to achieve what I’m looking for?
Question 3: for table 4 and 5 since I need a list of all the comics I have which means I need to copy a few of the fields from table 2 to complete table 4 and 5 (namely the issue, series, and volume) how would you accomplish this in the most efficient manner? To copy a field into 1 table from another table.
Question 4: Is there any reason to want the things such as publisher, which is in table 2, as well as the storage location and values into table 2 other then just to make table 2 look nice and flat so I can look at it like it was an excel sheet? I keep trying to do this and Im starting to think the only reason I am is to make it more excel like again… I assume the proper way would to keep most the data in separate tables then use reports and forms to just look at the various tables and bring all the data up there…. Am I correct in this assumption?
Thanks for the help… it’s amazing how set in your ways you get when trying to learn this after excel… I wish I would of learned this before excel Id imagine it would be easier to go the other way in learning.
Thanks again!
out of curiosity I was looking at a neat site that was giving some tips on things I coudl do... one of them was linking 2 combo boxes would allow me to set it up so I could set a form that would allow me to have 1 combo box or list box that would have the publishers, and a second that would show all comics until one of those publishers was picked, then show only those comics.
