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 |
#11
|
|||
|
|||
Is this a Sound Database Design? -- storing data efficiently
Hi Curtis,
Normalizing Data * storing data efficiently |--- ...When you are planning your database... ---| Instead of thinking about what you want out ... ....think about what you have to put in and structure it well. Drawing an analogy to a building: Data structure is the foundation. Forms are walls. Reports are paint. Organize your data so the structures are strong. You want what you build on that foundaton to be stable and flexible. The best solution is simple... but it is the hardest to find. It takes thought and foresight to design your structures well. And the simplest solution is rarely easy to see. Get the structure right before building anything else. If you have already built forms, queries, or other objects, fix the structure before building any more. Just as you wouldn't build a house on sand without a foundation, your data structure is the foundation you will build on for Access. It takes time to plan your database. Here are some ideas for you: Think of the different "nouns" that you will track: Customers Addresses Cities Phones Notes Products Purchases Payments Campaigns For each "noun", you describe it with "adjectives", or fields. Each table should have a primary key. It is common to use the autonumber field and "ID", such as CustomerID, , ProductID. By convention, when "ID" is used in a fieldname, it indicates that field was originally created by an autonumber and it is a LONG INTEGER data type. Do not use "ID" as a fieldname as it is ambiguous and not descriptive. Don't repeat data structures If you see that you have address information in more than one table, create an Addresses table and use an autonumber AddressID to relate the data. Key Fields Key fields are used to link related tables together and long integers are commonly used. When you create an autonumber field in access in one table, create a long integer field in another table to match up to it. The field names in the different tables should be the same when they represent the same piece of information. DefaultValue Change default value of all numeric foreign key fields to Null -- Microsoft default is 0, which will never match with an autonumber field -- not changing it will prevent you from being able to enforce referential integrity if it is not specified. To hammer that in... Access sets the DefaultValue of numeric fields to 0 -- this is not good for a foreign key because there won't be a key field to match it to : It is okay if it is not filled out, but it is NOT okay if it is filled out with a value that doesn't match. Indexes You can also create indexes on tables. For instance, you may have a combination of fields that should be unique, such as TestID and QuestionID, where you would not want the same question to appear on a test more than once. You can create a unique index on the combination. Think of which fields you will be doing lookups on and build indexes, but do this judiciously as indexes are updated when records are changed, so they take extra time to maintain, and there is a limit to how many you can create. Generally, you will have several Lookup tables. For instance, Products would be a lookup table when you want to collect a ProductID in another table, such as Purchases. Do not store names, store the IDs. For instance, do not store "Sue Smith", but use the PID (PersonID) that identifies her (* see Combobox Example on how to do this). Lookup Fields in the table design Another thing Microsoft allows you do is define comboboxes as part of the table design. I do not recommend this. Data should be entered via forms, which is where you would set up comboboxes. Data Type for Key Fields Don't use ANYTHING but text or integer, or long integer for key fields -- double-precision numbers are not accurate for keys. If you do use a text field for a key, keep in mind that it needs to be short to be efficient. It takes about 1 byte to store each character whereas long integers only require 4 bytes to store. Normally, long integers are used for key fields. Names DON'T use anything but letters, numbers, and underscores in fieldnames and tablenames. Don't use special characters in names (%, &, /, etc). Personally, I don't even use spaces. Start all names with a letter not a number. Using numbers usually indicates that the data is not normalized anyway. If you start a fieldname with a number, you WILL have problems, so don't ever do it. Microsoft allows you to do many things that kick you in the butt later. Think about how long text fields will be and set the Field size to something other than the default of 50 characters. For instance, 30 is usually long enough for cities, 10 long enough for zips, 14 for phone numbers, 15 or 20 for last or first name (I let firstname be longer in case there are 2 of them). Keep names concise yet descriptive. "Date" is a bad name for a field since that is a reserved word. Qualify fieldnames, like CourseDate and SaleDate. One reason to keep fieldnames short is consideration of the length of the field that the column heading will be over since column headings can't wrap when you open a table. Another is less to type when you are coding. Name your fields and tables well. When you do create forms and reports, name the controls to something logical before building any code. Names like Text34 and Combo68 make it frustrating to code and even more so if you need help and others have to decipher what those names really mean. Personally, I like to make the Name property of the control the same as the ControlSource property whenever possible. When you have command buttons, name them cmdClose, cmdOpenReportMenu, etc. *** some more considerations: Although convention tells us to prefix tables with "tbl", I don't like to do that. Takes a split second longer to scan for values. If you do want to group your tables, use something short, like T_ Which is easier to read? tblCustomers t_ Customers Customers If you are going to use a long table prefix such as "tblABC" ...make it quicker to read the important part -- the actual table name. Which can you read faster ... tblABCLocation tblABCLevel or tblABC_Location tblABC_Level always keep Names concise yet descriptive Numbers that aren’t numbers Unless you plan to do math, set "numbers" to text data type. Especially if they have symbols such as "(", "-" such as phone numbers. It is more efficient, however, to store numbers as long integers than text because the number of bytes of storage is less. Personally, I give that up for keeping the symbols. InputMask When you use the InputMask property on a text field, choose to store symbols in the field so when you do an export, they will be there. For instance, without storing symbols, a phone number would be 1234567890 when exported as opposed to 123-456-7890 or (123) 456-7890. Allow Zero Length Make sure Allow Zero Length is set to Yes for text fields if you are planning on importing or using APPEND queries to add data and fields may be empty strings. Captions Don't use captions for your fieldnames in your table designs. Personally, I think this is a bad idea. Users should never enter information directly into a table. Therefore, the only people that should be opening the table directly are administrators and using captions hides the real field name. If you want to call "ItemID" something else like TaskID, then NAME it that. Descriptions Fill out your field Descriptions! This is what the status bar text will be set to when you slide that field onto a form. The StatusBar text shows up in the lower left corner of the screen on the StatusBar when you are in that field. Tracking date record was created or updated Add these 2 fields to all your tables (except lookups) and make them the last 2 fields. DateCreate, date, DefaultValue = Now() DateUpdate, date – set on the form BeforeUpdate event the best way to use the DateCreate field is to set a default value of =Now() in the table design. For DateUpdate, make sure it is on your form (I put it in the form footer and LOCK it. Then, use the Form BeforeUpdate event to set the value me. DateUpdate = now() Combobox Example * Under no circumstances should you store names in more than one place. For instance, if you have a People table, define a PID (or PeopleID) autonumber field. Then, in other tables, when you want to identify a person, you can use the key field. One way to do this… Create an autonumber field in the People table -- PID, autonumber then, in the other tables... PID, long, DefaultValue = Null Then, when you want to put data in (which should be done from a form), you can set it up to pick names from a list but store the PID. create a combobox control Name -- PID ControlSource -- PID RowSource -- SELECT PID, LastName & ", " & Firstname AS Fullname, BirthDate FROM People ORDER BY LastName, Firstname BoundColumn -- 1 ColumnCount -- 3 columnWidths -- 0;2;1 (etc for however many columns you have -- the ID column will be hidden) ListWidth -- 3 (should add up to the sum of the column widths) if you have a listbox, make the width .01 MORE than the sum of the columns to prevent the horizontal scrollbar. PID will be stored in the form RecordSource while showing you names from another table... a MUCH better and more reliable method. If you want to show other information from your combobox in other controls, you can use calculated fields. For instance textbox: Name -- BirthDate ControlSource -- = PID.column(2) The reason that column 2 is referenced instead of column 3 is that column indexes start with 0, not 1, in Access '~~~~~~~~~~~~~~~~~~~~~~~~ "You mean I would have to get the autonumber manually for each ..." no, everything would be done in your code '~~~~~~~~~~~~~~~~~~~~~ "But why have so many tables for, what are the reasons you say that for? " You need to look down the road, Curtis. One of the reasons for using Access to track your data is so that you can take advantage of its incredible power -- as you learn more about Access, you will be glad if you take the time now to structure your data well because you will have more flexibility to do what you want. Once you have historical data in your tables, you can see what your database can tell YOU that perhaps you never thought of... Warm Regards, Crystal * (: have an awesome day * MVP Access Remote programming and Training strive4peace2006 at yahoo.com * Curtis Stevens wrote: "...so I can't just go splitting everything up." yes, you can ... just because your data comes to you in a flat format does not mean that is how yu should store it... you can have one table where you store the stuff to transfer to other tables first append the info to the main table then get the max autonumber key value then append data to the other tables where it really belongs You mean I would have to get the autonumber manually for each or easy as creating an append query, hit a button a bamb? Also, when you say append the info in the main table, you are talking about pasting the data right? But why have so many tables for, what are the reasons you say that for? Performance wise, etc? Is it not good to have a table with a whole bunch of fields? Because that screenshot I show above, just shows a fraction of them.... But a lot are just features, like account paid (yes/no), taking Discover & Amex, etc.... |
#12
|
|||
|
|||
Is this a Sound Database Design? -- procedurenames must be uniquein same module
Hi Curtis,
do not paste the second procedure into your code if you are only going to use the first one -- both are named the same and you should decide which one you want to use. You cannot have 2 prcedures with the same name on the same module sheet. Since these are PRIVATE functions, it is ok to have another Private function with this name in ANOTHER module sheet. If you email me (and anyone else reading this post too), I will send the first 3 chapters of a book I am writing on programming with VBA (email address in my siggy) -- I can also send you a document called "Access Basics for Programming", which is 30 pages -- make sure and tell me what you want in the email message. Warm Regards, Crystal * (: have an awesome day * MVP Access Remote programming and Training strive4peace2006 at yahoo.com * Curtis Stevens wrote: Hi Crystal, It looks like you had three sets of code, but there are only two versions? Sorry, but I'm still learing Access, always and I tried your very first set of code. Created a form with a subform in it, named it TabSubform and then created a command button, named it SwitchTabs. I then changed the names of the forms in the code, like "Address_Sub" to the names of my forms, like Potential, etc. I tried that and it said: The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives. You have to be sorta straight with me to know what you are talking about, like create a form called, create command button called and use this coding for on click, etc... :-) Curtis tab control vs replace SourceObject of subform I have 2 versions -- the first hardcodes formnames to swap in the code. The second uses a table (usys_tabs: the usys prefix makes it a "system" object) to store the swapping information The "command buttons" (put in quotes because I actually used label controls) to switch subforms are colored so that you can see which one is active The NAME property for the labels to switch are (ie Tab1 Tab2 Tab3 etc '~~~~~~~~~~~~~~~~~~~~~~~~ Private Function SwitchTabs(pIndex As Integer) On Error GoTo SwitchTabs_error '9 is Notes -- no code, not launched 'crystal 'strive4peace2006 at yahoo.com Me.TabNumber = pIndex Dim mNumTabs As Integer, i As Integer, mboo As Boolean Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long Fore1 = 16777215 Back1 = 11220286 Fore2 = 8388608 Back2 = 16644084 mNumTabs = 14 For i = 1 To mNumTabs If pIndex = i Then Me("tab" & i).BackColor = Back1 Me("tab" & i).ForeColor = Fore1 Else Me("tab" & i).BackColor = Back2 Me("tab" & i).ForeColor = Fore2 End If Next i Select Case pIndex Case 1, 2, 3, 4, 5, 14 Me.TabSubform.Visible = True Me.TabSubform.SetFocus End Select Select Case pIndex Case 1 Me.TabSubform.SourceObject = "Address_Sub" Case 2 Me.TabSubform.SourceObject = "Phone_Sub" Case 3: Me.TabSubform.SourceObject = "eAddresses_Sub" Case 4: Me.TabSubform.SourceObject = "Websites_sub" Case 5: Me.TabSubform.SourceObject = "Products_sub" Case 14: Me.TabSubform.SourceObject = "FindPeople_sub" Case Else Me.TabSubform.SourceObject = "" Me.Name1.SetFocus Me.TabSubform.Visible = False End Select Proc_Exit: Exit Function Proc_Err: MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs" 'press F8 to step through lines of code to see where problem is 'comment next line after debugged Stop : Resume resume Proc_Exit End Function '~~~~~~~~~~~~~~~~~~~~~~~~ TabID is an unbound control -- it is set when the form loads an example of the Onclick event for the Tab2 label is =SwitchTabs(2) I like this much better and it is prettier too! Dummy is an unbound control -- really tiny because I use labels instead of command buttons and a label does not get the focus Sometimes, the subforms do not have linking fields, like a lookup subform In those cases, I define a calculated control on the subform and set it equal to the specified control on the main form for LinkMasterFields (since the number of controls in LinkMasterFields and LinkChildFields cannot be changed at runtime) ie, in this example, I have set up an AddressBook application with PID (people ID) as the main key as the first form to do switching. The second form to do switching (below) is an Admin form to import and export information. unbound subform : textbox control name -- PID ControlSource -- =forms!AddressBook!PID visible -- no and now I am taking this one step further: table -- usys_Tabs frmID TabID btnCaption frmName IsActive IsSib 1 0 Find FindPeople_sub Yes Yes 1 1 Address Address_Sub Yes Yes 1 2 Phone Phone_Sub Yes Yes 1 3 Email eAddresses_Sub Yes 2 1 Export Admin_Export Yes Yes 2 2 Import Admin_Import Yes Yes 2 3 Import Brio Admin_Import_Brio Yes Yes 2 4 Reports REPORTMENU_A Yes No 2 5 Utilities Admin_Utilities Yes Yes '~~~~~~~~~~~~~~~~~~~~~~~~ Private Function SwitchTabs(pTabID As Integer) On Error GoTo Proc_err Dim mCurrentTab As Integer, i As Integer, mBoo As Boolean Dim mform As String Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long Dim mLastTab As Long Dim BackNotSub As Long Dim r As dao.Recordset, S As String 'crystal 'strive4peace2006 at yahoo.com 'needs reference to: 'Microsoft DAO Library mLastTab = 5 BackNotSub = 16112075 ' If Me.Dirty Then Me.Dirty = False mCurrentTab = Nz(Me.TabID) Me.TabID = pTabID 'since I have this code behind the form it applies to 'the formID is hardcoded S = "SELECT Tab.TabID, Tab.btnCaption, " _ & " Tab.frmName, Tab.IsActive, Tab.IsSub " _ & " FROM usys_Tabs AS Tab " _ & " WHERE frmID=2 ORDER BY TabID;" 'don't need to modify record, just look them up 'that is why we use dbOpenSnapshot Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot) r.MoveLast r.MoveFirst r.FindFirst "TabID = " & pTabID Me.dummy.SetFocus If (r.NoMatch) Or IIf(r.NoMatch, False, Not r!IsActive) Then MsgBox Nz(r!btnCaption) & " is not active", , _ "Under construction" Else If r!IsSub Then Me.TabSubform.SourceObject = r!FrmName Fore1 = 16777215 Back1 = 11220286 Fore2 = 8388608 Back2 = 16644084 For i = 1 To mLastTab If i 4 Then If pTabID = i Then Me("tab" & i).BackColor = Back1 Me("tab" & i).ForeColor = Fore1 Else Me("tab" & i).BackColor = Back2 Me("tab" & i).ForeColor = Fore2 End If End If Next i Me.TabSubform.SetFocus Else On Error Resume Next DoCmd.OpenForm r!FrmName End If End If Proc_exit: On Error Resume Next r.Close Set r = Nothing Exit Function Proc_err: MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs" 'press F8 to step through lines of code to see where problem is 'comment next line after code is debugged Stop: Resume Resume Proc_exit End Function '~~~~~~~~~~~~~~~~~~~~~~~~ Warm Regards, Crystal * (: have an awesome day * MVP Access Remote programming and Training strive4peace2006 at yahoo.com * Curtis Stevens wrote: Hi Crystal, Your awesome, it's great to have people out there willing to help! Access is so darn cool as it can do endless things & still learning over the years the power of this one program... He http://www.gotmerchant.com/dbrelationships.jpg I know, I need to get the spaces out of the names, but will fix that one day.... Any way possible to view the VB for the entire database in one screen, so you can change all the references to a particular name, use the replace tool & get it all fixed very easily? Got an example of what you mentioned below? You have one subform and command buttons, which will change the subform, which form it displays? If so, got an example I can see the code & see how that it is done & use it? I only have one main table and a few others that include different info tied to the main one, you see it in my pic above. The other tables are mere pull down info tables, etc.... Its hard to make it look pretty & still have all the cool features & simply all the stuff you want on one screen..... Curtis |
#13
|
|||
|
|||
Is this a Sound Database Design? -- storing data efficiently
There are some good points there, thanks for all the info.
1. The only problem I have is you seem to create so many different tables, seems too excessive, like you need one main table that everything relates to. This should have all your customers or subject's main information like name, addr, city, st, zip, phone, email, etc... Now I do need another one for merchants as there can be more than one owner, like 3, etc. But in your previous examples, you split it all up phone numbers, etc. Seemed to excessive to me in that respect. 2. I'm not sure how you take one big table with every field in there, like pasted all the info from the online app and then APPEND the data into the multiple tables I have setup. I tried to setup a Append query and it only allows you to append to one table, not a bunch of different ones. It asks you what table do you want, you must pick in this db or some other database... 3. I do like the datecreate & dateupdate idea, would be a fart trying to display those in the forms for every table, etc. Room wise, but it would be there in case you need or want the data, etc. Very good. You could probably go even further and have an author field as if you have multiple employees and instead of overwriting updatefield, that would be a separate table it self, so you can see whole all updated it historically, etc... Thanks Curtis |
#14
|
|||
|
|||
Is this a Sound Database Design? -- procedurenames must be uni
Sure, you can email it to me at sshost at Google's email address.
Also, I tried pasting just the first set of code & got the error I mentioned above, anything you see wrong? I gave up on it after a while... do not paste the second procedure into your code if you are only going to use the first one -- both are named the same and you should decide which one you want to use. You cannot have 2 prcedures with the same name on the same module sheet. Since these are PRIVATE functions, it is ok to have another Private function with this name in ANOTHER module sheet. If you email me (and anyone else reading this post too), I will send the first 3 chapters of a book I am writing on programming with VBA (email address in my siggy) -- I can also send you a document called "Access Basics for Programming", which is 30 pages -- make sure and tell me what you want in the email message. |
#15
|
|||
|
|||
Is this a Sound Database Design? -- storing data efficiently
Crystal,
If I'm not mistaken, you can't have all these different forms, add them all to the same query and use one main form, right? You will have to use subforms for the additonal tables right? If so, then I can't go splitting it up as I can't go spliting up the form that much, just wouldn't work. Curtis |
#16
|
|||
|
|||
Is this a Sound Database Design? -- storing data efficiently
I meant to say you can't have all these different tables in the same query,
as I tried that. Then selected certain text boxes for those particular fields from the other tables, which have the relationships in place, doesn't work, guess all those fields must be in subforms, and that beats the whole purpose? Thanks Curtis If I'm not mistaken, you can't have all these different forms, add them all to the same query and use one main form, right? You will have to use subforms for the additonal tables right? If so, then I can't go splitting it up as I can't go spliting up the form that much, just wouldn't work. Curtis |
#17
|
|||
|
|||
Is this a Sound Database Design? -- one form for each table
Hi Curtis,
Rather than using just one form that gets its data from many tables (or globbing all the data into one table when it should be seperated), most database solutions use a mainform/subform scenario. In my opinion, it is best to have just one form or subform to fill data in each table. So, if you have 10 tables, you would have 10 data forms -- then perhaps a "switchboard" form to decide which form to open and a ReportMenu form for processing report requests. Warm Regards, Crystal * (: have an awesome day * MVP Access Remote programming and Training strive4peace2006 at yahoo.com * Curtis Stevens wrote: Crystal, If I'm not mistaken, you can't have all these different forms, add them all to the same query and use one main form, right? You will have to use subforms for the additonal tables right? If so, then I can't go splitting it up as I can't go spliting up the form that much, just wouldn't work. Curtis |
#18
|
|||
|
|||
Is this a Sound Database Design? -- one form for each table
So is it even possible to have one form with text boxes in it pointing to
fields from multiple tables? It isn't right? Rather than using just one form that gets its data from many tables (or globbing all the data into one table when it should be seperated), most database solutions use a mainform/subform scenario. In my opinion, it is best to have just one form or subform to fill data in each table. So, if you have 10 tables, you would have 10 data forms -- then perhaps a "switchboard" form to decide which form to open and a ReportMenu form for processing report requests. Warm Regards, Crystal * (: have an awesome day * MVP Access Remote programming and Training strive4peace2006 at yahoo.com * Curtis Stevens wrote: Crystal, If I'm not mistaken, you can't have all these different forms, add them all to the same query and use one main form, right? You will have to use subforms for the additonal tables right? If so, then I can't go splitting it up as I can't go spliting up the form that much, just wouldn't work. Curtis |
#19
|
|||
|
|||
Is this a Sound Database Design? -- procedurenames must be uni
Crystal,
I'm still not able to get this code to work! Here is what I have exactly! Created a form called TEST. Inserted a command button named SwitchTabs. Inserted a subform named TabSubform. I pasted this code as an OnClick for the command button. It comes up with that same error. What do I do???? Potential & Current are the names of my forms I want to be displayed in TabSubform. ==================CODE============== Private Sub SwitchTabs_Click() Private Function SwitchTabs(pIndex As Integer) On Error GoTo SwitchTabs_error '9 is Notes -- no code, not launched 'crystal 'strive4peace2006 at yahoo.com Me.TabNumber = pIndex Dim mNumTabs As Integer, i As Integer, mboo As Boolean Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long Fore1 = 16777215 Back1 = 11220286 Fore2 = 8388608 Back2 = 16644084 mNumTabs = 14 For i = 1 To mNumTabs If pIndex = i Then Me("tab" & i).BackColor = Back1 Me("tab" & i).ForeColor = Fore1 Else Me("tab" & i).BackColor = Back2 Me("tab" & i).ForeColor = Fore2 End If Next i Select Case pIndex Case 1, 2, 3, 4, 5, 14 Me.TabSubform.Visible = True Me.TabSubform.SetFocus End Select Select Case pIndex Case 1 Me.TabSubform.SourceObject = "Potential" Case 2 Me.TabSubform.SourceObject = "Current" Case 3: Me.TabSubform.SourceObject = "Potential" Case 4: Me.TabSubform.SourceObject = "Current" Case 5: Me.TabSubform.SourceObject = "Potential" Case 14: Me.TabSubform.SourceObject = "Current" Case Else Me.TabSubform.SourceObject = "" Me.Name1.SetFocus Me.TabSubform.Visible = False End Select Proc_Exit: Exit Function Proc_Err: MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs" 'press F8 to step through lines of code to see where problem is 'comment next line after debugged Stop: Resume Resume Proc_Exit End Function End Function ==================CODE============== Thanks Curtis |
#20
|
|||
|
|||
Is this a Sound Database Design? -- one form for each table
Curtis Stevens wrote:
So is it even possible to have one form with text boxes in it pointing to fields from multiple tables? It isn't right? It is if the RecordSource of the form is a query that joins multiple tables. The potential problem is that multi-table queries are often not editable unless built just right and on rare occassions can even send the updates to the wrong table. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combo Box AfterUpdate Help | Harry Thomas | Database Design | 21 | January 9th, 2006 12:16 AM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Encrypt AccesS File? | milest | General Discussion | 2 | February 9th, 2005 07:58 PM |
Access Error Message when opening database | eah | General Discussion | 3 | January 26th, 2005 10:04 AM |
Exclusive access to the database | Steve Huff | General Discussion | 17 | December 24th, 2004 06:23 PM |