Prev: Table Structure Help
Next: Web Database
From: Yoric on 29 Mar 2010 23:51 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.
From: Dennis on 30 Mar 2010 01:20 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
From: Steve on 30 Mar 2010 13:49 Roy, Dennis has given you some bad advise! Start at the beginning and not at the end. The first thing you need is a well designed set of normalized tables to hold your data. To avoid building in headaches, find a reference to Access Reserved Words and don't name any table or field with a reserved word. Part two of avoiding building in headaches is to name all your tables starting with Tbl and don't use any spaces or underlines in the names. Part three is to use an autonumber field for all primary keys, end the primary key field name with ID and make all foreign key names the same as its related primary key. If you achieve this, coding will be simple and easy to read and creating any forms and reports you want will be easy. Basically your data consists of components, receptacles for components and location of each receptacle. Spend time with a pencil and paper and work out a preliminary design of your tables. Then post back what you have for a critique and suggestions. The typical way to post tables is: TblComponent ComponentID ComponentDesc <other fields describing attributes of a component> Steve santus(a)penn.com "Yoric" <Yoric(a)discussions.microsoft.com> wrote in message news:727153E4-4A3F-476A-9827-E3BFD82D2956(a)microsoft.com... > 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.
From: Dennis on 30 Mar 2010 17:43 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/access/HA100306431033.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 structure: 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
From: Steve on 30 Mar 2010 19:35
Roy, Once again Dennis has given you some very bad advise! It's your choice whether you want to heed it. Steve "Yoric" <Yoric(a)discussions.microsoft.com> wrote in message news:727153E4-4A3F-476A-9827-E3BFD82D2956(a)microsoft.com... > 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. |