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
|
|||
|
|||
Newbee Access DB structure help
Hi All,
I am doing a project to get started on Access. I am a tinkerer, and have a lab and garage full of storage boxes, component chests, tackle boxes, plastic tubs and such, on shelves, in cupboards and on the floor. Each receptacle, be it a large plastic tub, or a single drawer of a 6x5 component chest has components in it. There may be more than one, and more than one type of component in each receptacle. Each receptacle has a location (room), position (shelf, floor) and a unique id. A receptacle is always singular, but may be a single carton, or a drawer within a multi-drawer chest. There may be several chests, some with 5x6 drawers, some with 8x4 drawers etc. Components each have attributes: Description, part #, genre, image, datasheet, # on hand, etc. A single type of component may have 1, 2 or more genres (OPTO, Discrete, Semiconductor). There may be the same type of component in different receptacles (Part of the reason for this project is to gather them and consolidate them!) Thus far, I have created tables for ReceptacleType (18 possibilities), Location(4 possible rooms), Component (20 fields incl location, receptacle type, #on hand, but mainly just attributes etc). I also have a Receptacle table which is the uniqueid for every single receptacle, its type, location, and component list field. I am struggling in several areas: 1) I am not sure how to build the relationships, such that some receptacles have 'parents' or hierarchy and some do not. For example, a drawer within a chest of 20, or a single large cardboard box. 2) How to construct forms that will change 'downstream' fields, dependent upon a certain choice made in a combo box. Say if I select a chest drawer, a field will open to describe which chest 3) How to set up forms for the most efficient data entry. I envisage visiting each receptacle with my laptop, entering the list of components in it (briefly), the location of the receptacle, physically labelling the receptacle with the uniqueid sticker, then moving to the next. Once that is done, I would go to each component, and flesh out the data for the attributes as necessary. Later on, it would be great to report all the 'OPTO' components, so I could gather them all up and consolidate them into grouped, or adjacent receptacles, updating the db as I did so. An important attribute for each component would be a synonym field, so I could query LED, led, LEDS, Light Emitting Diodes and find all of the relevant records. Frankly, any advice, especially on where to begin, would be most appreciated! Thanks in anticipation, Roy. |
#2
|
|||
|
|||
Newbee Access DB structure help
Roy, Wow, you have some project ahead of you. Both in Access and in physical inventory work. Access has a steep learning curve, but there are a lot of people on this forum who are they to help you so don’t let that intimidate you. Questions: Where to begin? Answer: I always like to begin at the end. That is, the first thing I do is figure out the objective of the project which you have done pretty well. I would recommend that you write them down. The main purpose for putting them on paper is it makes you think about everything you want to do. Realize that this might take a couple of days or weeks to create a complete list. Secondly, you should sketch out ALL of the reports and inquiry screens (including the fields) you would like to have someday. You will use the inquiry and reports fields as a check list to insure you have captured all of the required fields. If you did not enter the field or you can not calculate the field, you can not have it on a report. Also, once you have all of you reports and inquiry screens, you can then decide how to make them all work together. If you don’t know the final output, it is very hard to design the front end. Also, once you have all of you reports and inquiry screens laid out, you can check to make sure that you have met all of your objectives. If the reports do not meet the requirements, they you have some more work to do. Question 1) I am not sure how to build the relationships, such that some receptacles have 'parents' or hierarchy and some do not. For example, a drawer within a chest of 20, or a single large cardboard box. Answer: There are a couple of ways to do this. Here is one way. 1. Given a Receptacle table of: Key: Receptacle No: Data Description Location Position Multiple Drawer (Y/N) I would have a Receptacle Drawer table. This table would have a two part key: Receptacle No/Name, and drawer number. By definition, all receptacles have one “drawer” and that is itself. So there will always be a drawer no 1 for all receptacles. You could write you VBA code to automatically create drawer number 1 for all receptacles that do not have multiple drawers. Receptacle Drawer table Key: Receptacle No Drawer No Drawer Type (this is the key to the Drawer Type Desc Table) Drawer size The top half of the Receptacle data entry form would have the receptacle information that is stored in the Receptacle table and the bottom half of the form would be a sub-form (a form within a form) that is in continuous view (display one line after the other – like excel). The sub-form would have all of the drawers assigned to this receptacle. If there is not enough room, you could put tabs on the form. The first tab would have the receptacle information and the second tab would have the drawer information. In either case, you could make the sub-form or the second tab invisible if the receptacle had no drawers. Yet another reason for the Multiple Drawer question. On the parts table, you would have both a receptacle number and a drawer number fields. If the receptacle did not have multiple drawers, you could have your code automatically set drawer number to 1 on the inventory form. 2) How to construct forms that will change 'downstream' fields, dependent upon a certain choice made in a combo box. Say if I select a chest drawer, a field will open to describe which chest Yes you can do this. There are multiple ways to accomplish this. In the combo box, you can return multiple fields from the query / sql statement. You can then access those fields via the statement: Assuming a SQL stmt like Select InventoryTable PartNo, PartName, LocNo, RecNo, DrawerNo for the combobox, your VBA code to access that addition data would be: strPartName = me.cboPartNo.column(1) strLocNo = me.cboPartNo.column(2) strRecpName = me.cboPartNo.column(3) strDrawerNo = me.cboPartNo.column(4) You can then use that data to do whatever you wanted to do. 3) How to set up forms for the most efficient data entry. There are a LOT of different opinions on this. The best is keep it simple, don’t go crazy with different colors, adopt a standard and keep to that standard (ie. The save button is always green, the Delete button is always Red, etc.) However, think about how you are going to be entering the data when you are going from receptacle to receptacle. The main thing is enter the information is a sequent that makes sense. One thing, on you might wants to have a Part Genres table. That way you can have as many genres for a given part as you want. The table would look something like: Part Genres table: Key: Auto assigned number by access. Data Part No (index - duplicates ok) Genres code (the description to this code is in another table) I have a question. You said the same part could be in different receptacles. If that is the case, how will you handle that? Will you have different part numbers for the same part? Or will you use the same part number? If you use the same part number, then you will need a master part tables that holds the description of the part and another table – Part Loc table – that has the different locations of the part of the quantity on hand in each location. You might have to have a parts table that has a main part and two sub-forms. The first sub-form would be the Parts Genres sub-form. The second sub-form would be the Parts Location sub-form. So much to think about! It’s late and I need some sleep. Dennis |
#4
|
|||
|
|||
Newbee Access DB structure help
Roy,
I've read Steve's comments and I must disagree with some of his points. While I agree that you need a “a well designed set of normalized tables to hold your data”. However, before you can design your tables, you have to define all of your fields and relationships. If you have not defined all of your outputs, you can not possibly know all of your inputs to the system! This is why you always start with the outputs. If you have a report that has a field that you never defined on the input side, then don’t have a “well designed set of normalized tables”. Therefore, you must define all of your fields and relationships BEFORE you design your tables. Also, as I stated before define ALL of your goals, even if they seem far fetched. I’ve had projects where the customer has asked for some pretty wild stuff, but as the design unfolds they have turned out to be pretty easy to accomplish. On the other had, I’ve had request that were very difficult to accomplish and were not worth the development cost. However, if you don’t put EVERYTHING down, you don’t know what you can and can not do. I do agree that you need a list of Access Reserved Words to avoid. Here is a link to MS’s Office 2007 Access Reserved words: http://office.microsoft.com/en-us/ac...306431033.aspx Steve mentions to avoiding using spaces or underlines in you table names. However, he should have also told you not to use spaces or dashes in the data field names. As far as the primary key always being an auto number field, I think that is personal choice. Where there is a “natural key” (such as part State abbreviation) I use it. If there is not, I’ll use an auto number field for the key. Also, if you create a meaning key like LED instead of 24 (auto key) when you are testing your system it is easier to determine if data is in the right place if you have meaning code names instead of numbers. But, like I said I use both “natural” and auto numbers fields for my keys. One other suggestion I have instead of having a whole bunch of lookup description tables for: Receptacle type (box, drawer, plastic tub, chest, etc.) Room Position Genre Description I like to have two tables. The First table, tblDescType, describes the second table, tblDesc. The first table is called tblDescType and has the following structu Key: DescTypeId Text field and is code name for the Type of Description. (ie. RC for receptacle type, R for Room, P for position) Data: CodeDesc A text field which contains the description for description type – Receptacle Type, Room ShortDesc CodePrompt – Text field which contains the text for the Desc form’s Code Name label field. DescPrompt - Text field which contains the text for the code’s descriptions label prompt. ShortDesc – Text field which contains the text for the code’s short description label prompt. Parm1Promp – Text field which contains the text for an additional parameter prompt. Parm2Promp – Text field which contains the text for an additional parameter prompt. Parm3Promp – Text field which contains the text for an additional parameter prompt. Parm4Promp – Text field which contains the text for an additional parameter prompt. I realized that I could normalize this table, but this was a quick and dirty solution for having multiple description tables. At this time I have over 50 different types of descriptions and only two tables. It takes about 1 minute to setup a new description type. The second table is called tblDesc and contains the actually description records. Key: DescID This is a “manual” two part key. The first part is the DescTypeId and the second part is the code for the description record. I call it a manual two part key because the data entry form has to concatenate the DescTypeId field with a dash and then with the Desc code. So they key to a drawer might be R-D or R-C (chest). I create the manual key here once and all of the other tables that reference this table store the entire DescId (R-D). Data DescTypeId – FK to the tblDescType table. Yes, this is duplicate data, but I did not know a SQL way to break out the DescTypeId when I used the field in queries. So I duplicated the data to make life simple. DescCd – This is the second part of the key. It is here for the same reason the first part is above. Desc - The description text for the code. ShortDesc – Short (15 chars or less) description for the code. Parm1 Parm2 Parm3 Parm4 These fields contain whatever additional information you want for each description. Yes, each fields having a different meaning is a violation of database rules, however these fields are not “data” fields, they are more parameter fields for you to use to allows certain transaction type to be selected for a report, or to sort your drop down list of descriptions in a certain sequence other than numerical or alphabetical. I have one form that allows me to enter the parameters for a given Description Type and another form that the record for a Description Type and fills in the appropriate field labels for that Desc. Type and allows the user to enter the code’s description record. If you have any more questions, please post them. Good luck Dennis |
#5
|
|||
|
|||
Newbee Access DB structure help
Roy,
Once again Dennis has given you some very bad advise! It's your choice whether you want to heed it. Steve "Yoric" wrote in message ... Hi All, I am doing a project to get started on Access. I am a tinkerer, and have a lab and garage full of storage boxes, component chests, tackle boxes, plastic tubs and such, on shelves, in cupboards and on the floor. Each receptacle, be it a large plastic tub, or a single drawer of a 6x5 component chest has components in it. There may be more than one, and more than one type of component in each receptacle. Each receptacle has a location (room), position (shelf, floor) and a unique id. A receptacle is always singular, but may be a single carton, or a drawer within a multi-drawer chest. There may be several chests, some with 5x6 drawers, some with 8x4 drawers etc. Components each have attributes: Description, part #, genre, image, datasheet, # on hand, etc. A single type of component may have 1, 2 or more genres (OPTO, Discrete, Semiconductor). There may be the same type of component in different receptacles (Part of the reason for this project is to gather them and consolidate them!) Thus far, I have created tables for ReceptacleType (18 possibilities), Location(4 possible rooms), Component (20 fields incl location, receptacle type, #on hand, but mainly just attributes etc). I also have a Receptacle table which is the uniqueid for every single receptacle, its type, location, and component list field. I am struggling in several areas: 1) I am not sure how to build the relationships, such that some receptacles have 'parents' or hierarchy and some do not. For example, a drawer within a chest of 20, or a single large cardboard box. 2) How to construct forms that will change 'downstream' fields, dependent upon a certain choice made in a combo box. Say if I select a chest drawer, a field will open to describe which chest 3) How to set up forms for the most efficient data entry. I envisage visiting each receptacle with my laptop, entering the list of components in it (briefly), the location of the receptacle, physically labelling the receptacle with the uniqueid sticker, then moving to the next. Once that is done, I would go to each component, and flesh out the data for the attributes as necessary. Later on, it would be great to report all the 'OPTO' components, so I could gather them all up and consolidate them into grouped, or adjacent receptacles, updating the db as I did so. An important attribute for each component would be a synonym field, so I could query LED, led, LEDS, Light Emitting Diodes and find all of the relevant records. Frankly, any advice, especially on where to begin, would be most appreciated! Thanks in anticipation, Roy. |
#6
|
|||
|
|||
Newbee Access DB structure help
Yoric,
Basically, you need an inventory management system. Unfortunately, I have not seen a Data Model for one but there some other here that might help with *how to store your data*... http://www.databasedev.co.uk/data_models.html One thing to be careful of using Reserved Words for any field or table names, to help with that here's a list of all those words... http://allenbrowne.com/Ap****ueBadWord.html You didn't mention which version Access but Access 2007 has an inventory template you could start with and modify to suit your needs. Now, as to where to start... you didn't mention how much experience you have with Access, so I am going to suggest the beginning... Jeff Conrad's resources page... http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page... http://www.mvps.org/access/resources/index.html A free tutorial written by Crystal (MS Access MVP)... http://allenbrowne.com/casu-22.html MVP Allen Browne's tutorials... http://allenbrowne.com/links.html#Tutorials -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Yoric" wrote in message ... Hi All, I am doing a project to get started on Access. I am a tinkerer, and have a lab and garage full of storage boxes, component chests, tackle boxes, plastic tubs and such, on shelves, in cupboards and on the floor. Each receptacle, be it a large plastic tub, or a single drawer of a 6x5 component chest has components in it. There may be more than one, and more than one type of component in each receptacle. Each receptacle has a location (room), position (shelf, floor) and a unique id. A receptacle is always singular, but may be a single carton, or a drawer within a multi-drawer chest. There may be several chests, some with 5x6 drawers, some with 8x4 drawers etc. Components each have attributes: Description, part #, genre, image, datasheet, # on hand, etc. A single type of component may have 1, 2 or more genres (OPTO, Discrete, Semiconductor). There may be the same type of component in different receptacles (Part of the reason for this project is to gather them and consolidate them!) Thus far, I have created tables for ReceptacleType (18 possibilities), Location(4 possible rooms), Component (20 fields incl location, receptacle type, #on hand, but mainly just attributes etc). I also have a Receptacle table which is the uniqueid for every single receptacle, its type, location, and component list field. I am struggling in several areas: 1) I am not sure how to build the relationships, such that some receptacles have 'parents' or hierarchy and some do not. For example, a drawer within a chest of 20, or a single large cardboard box. 2) How to construct forms that will change 'downstream' fields, dependent upon a certain choice made in a combo box. Say if I select a chest drawer, a field will open to describe which chest 3) How to set up forms for the most efficient data entry. I envisage visiting each receptacle with my laptop, entering the list of components in it (briefly), the location of the receptacle, physically labelling the receptacle with the uniqueid sticker, then moving to the next. Once that is done, I would go to each component, and flesh out the data for the attributes as necessary. Later on, it would be great to report all the 'OPTO' components, so I could gather them all up and consolidate them into grouped, or adjacent receptacles, updating the db as I did so. An important attribute for each component would be a synonym field, so I could query LED, led, LEDS, Light Emitting Diodes and find all of the relevant records. Frankly, any advice, especially on where to begin, would be most appreciated! Thanks in anticipation, Roy. |
#7
|
|||
|
|||
Newbee Access DB structure help
Thanks to all for such prompt and detailed advice. A lot of it is a little
over my head at this stage, but I will plow (plough) through it all and see what I come up with. I don't want to come between Steve and Dennis, and am certainly not authorized to comment, however I always try to start a journey with the end in mind....it is the only way I know that I ever get there. Sage advice however, on labeling techniques, so thanks to both. I will continue to define what I want out of the system, then post more details of the tables as I develop them. And I will visit those tutorials! Thanks again to all, Roy. |
#8
|
|||
|
|||
Newbee Access DB structure help
Roy,
Thanks for making my point for me. You can not start a journey unless you have an end in mind - "it is the only way I know that I ever get there". Enough said. Gina has some good advice for you. You have a LOT to learn and she provided some good links. Don't worry about a lot of this stuff being over your head. Just keep the advice in the back of your mind as you plow through your project . As you get to the point where you have specific questions on how to do some, post it on this forum and people will be there to help. Good luck on your task. You have a lot to do. Dennis |
#9
|
|||
|
|||
Newbee Access DB structure help
On 31 Mar, 00:35, "Steve" wrote:
Roy, Once again Dennis has given you some very bad advise! It's your choice whether you want to heed it. Steve Given your track record, I know who I'd rather put my faith in. |
#10
|
|||
|
|||
Newbee Access DB structure help
Yoric
Some folks are willing to share their ideas and perspectives. Others here want to tell you where to go and what to do. Choose the approach that works best with you... By the way, if you don't know where you are going, it will be tough to, as one responder suggested, create your "well designed set of normalized tables"... I also tend to start out trying to figure out what I will need at the end to help me plan what I need to include. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Yoric" wrote in message ... Hi All, I am doing a project to get started on Access. I am a tinkerer, and have a lab and garage full of storage boxes, component chests, tackle boxes, plastic tubs and such, on shelves, in cupboards and on the floor. Each receptacle, be it a large plastic tub, or a single drawer of a 6x5 component chest has components in it. There may be more than one, and more than one type of component in each receptacle. Each receptacle has a location (room), position (shelf, floor) and a unique id. A receptacle is always singular, but may be a single carton, or a drawer within a multi-drawer chest. There may be several chests, some with 5x6 drawers, some with 8x4 drawers etc. Components each have attributes: Description, part #, genre, image, datasheet, # on hand, etc. A single type of component may have 1, 2 or more genres (OPTO, Discrete, Semiconductor). There may be the same type of component in different receptacles (Part of the reason for this project is to gather them and consolidate them!) Thus far, I have created tables for ReceptacleType (18 possibilities), Location(4 possible rooms), Component (20 fields incl location, receptacle type, #on hand, but mainly just attributes etc). I also have a Receptacle table which is the uniqueid for every single receptacle, its type, location, and component list field. I am struggling in several areas: 1) I am not sure how to build the relationships, such that some receptacles have 'parents' or hierarchy and some do not. For example, a drawer within a chest of 20, or a single large cardboard box. 2) How to construct forms that will change 'downstream' fields, dependent upon a certain choice made in a combo box. Say if I select a chest drawer, a field will open to describe which chest 3) How to set up forms for the most efficient data entry. I envisage visiting each receptacle with my laptop, entering the list of components in it (briefly), the location of the receptacle, physically labelling the receptacle with the uniqueid sticker, then moving to the next. Once that is done, I would go to each component, and flesh out the data for the attributes as necessary. Later on, it would be great to report all the 'OPTO' components, so I could gather them all up and consolidate them into grouped, or adjacent receptacles, updating the db as I did so. An important attribute for each component would be a synonym field, so I could query LED, led, LEDS, Light Emitting Diodes and find all of the relevant records. Frankly, any advice, especially on where to begin, would be most appreciated! Thanks in anticipation, Roy. |
Thread Tools | |
Display Modes | |
|
|