Textbook Review
I think that the textbook was what the title said. The Complete Reference. It was not a textbook for a structured learning approach. The text is more for a user familiar with Access Dababase and to be used as a reference. The text was complete in the aspect of covering all the bases in explaining all of the various types of queries and events and other such topics. There was really nothing that I could not find in the text, but when I wanted an example of a concept, maybe formatted in a step by step context to learn a specific procedure, there could have been clearer examples and/or margin notes highlighting such concepts.
I found the writing of the text very easily understood and complete. The author’s style of writing was not so technical that you were running for other sources to explain material, but was in depth enough to explain the topic in full. Even on more technical aspects, when I needed to reread a passage, it was to better grasp the concept rather than a non-understanding of the material.
In some of the more advanced concepts, besides simply stating the topic and giving a definition of the term or concept, an actual example presented as an explanation might have been helpful. It is here that I feel that the CD could have been vastly more valuable. When I initially opened the CD to look at the files I thought to myself, “This is all there is? Why waste the time making the CD?” Two database files with some associated pictures and graphics, not specifically referenced in the text to a great degree, is not my idea of a data disk to accompany a text/reference, especially of this size and completeness. A specific data file corresponding to each chapter would have been very helpful, especially when settings to achieve a specific task needed to be set. A user could have then accessed the file and the properties for such details.
Wednesday, June 20, 2007
Saturday, June 16, 2007
Access Security
Security
There are four basic items that you can do to secure your database.
1. Password protect – Somewhat effective but there are many programs out there that can be used to bypass passwords.
2. Encoding – This is somewhat more effective since the coding method is more random and harder to get by.
3. Groups and Access – You can create user groups and limit access by group. This can be effective but it can also be a nightmare in maintaining who is in what group and who has what access. It seems that there is always someone that overlaps user groups and needs full access in one group but only access to one thing in another group and then you have to decide how to grant that access.
4. Hiding – This can be from two approaches. On your network system, items in your database can be hidden from some users and not others. Sort of the out-of-sight-out-of-mind approach. For many users this will work just fine. Other users see this as a challenge to find the data.
The other part of this is hiding your computers, and thus your data from the internet as a whole so that hackers and other such slime cannot get a ping in response when they are seeking out vulnerable systems.
Why To Secure
Some times your data is proprietary and this information needs to be secured from your competitors. If your knowledge got out you might lose your competitive advantage.
Your data might sometimes be secret. Think of all the data that government agencies have that is secret, not the least to say at the CIA.
By law, only some data is to be released to certain people. Think of the forms you sign at the doctor’s office as to who can view your records and FRPA laws at our universities.
Sometimes data is out there to be viewed on systems, as public knowledge, but is not to be changed by the viewer. This could be public information such as property information at city hall and other public records.
Personal information is very important to keep secure. We have heard about Cendant being hacked into last year and thousands of people’s personal data was comprised. In this day and age of identity theft, keeping our names and SSN’s secure is important.
Data integrity is also important. We want to know that the information that we are looking at is accurate, up-to-date and reliable. We do so much of our research on-line now that we want to know that the information that we may be relying on to validate our suppositions is true and accurate.
Security Flaws
First and foremost, as with any computer program, there is always the dangers of viruses and worms that may be programmed to attack a specific program and Access is no different. There have also been instances of macros being programmed that either run automatically or when you have downloaded and installed them as part of your Access data.
Remote Code executions for Microsoft office seem to be the greatest occurrence of vulnerability of security flaws and need to be dealt with. There were many instances of this, mostly for Office itself, but occasionally for PowerPoint and some of the other applications, but for the most part they applied to the application as a whole.
In other words, someone at another location can access the vulnerability through your office program to gain access and even control over your computer and then do what they want with your data and/or your computer.
There are four basic items that you can do to secure your database.
1. Password protect – Somewhat effective but there are many programs out there that can be used to bypass passwords.
2. Encoding – This is somewhat more effective since the coding method is more random and harder to get by.
3. Groups and Access – You can create user groups and limit access by group. This can be effective but it can also be a nightmare in maintaining who is in what group and who has what access. It seems that there is always someone that overlaps user groups and needs full access in one group but only access to one thing in another group and then you have to decide how to grant that access.
4. Hiding – This can be from two approaches. On your network system, items in your database can be hidden from some users and not others. Sort of the out-of-sight-out-of-mind approach. For many users this will work just fine. Other users see this as a challenge to find the data.
The other part of this is hiding your computers, and thus your data from the internet as a whole so that hackers and other such slime cannot get a ping in response when they are seeking out vulnerable systems.
Why To Secure
Some times your data is proprietary and this information needs to be secured from your competitors. If your knowledge got out you might lose your competitive advantage.
Your data might sometimes be secret. Think of all the data that government agencies have that is secret, not the least to say at the CIA.
By law, only some data is to be released to certain people. Think of the forms you sign at the doctor’s office as to who can view your records and FRPA laws at our universities.
Sometimes data is out there to be viewed on systems, as public knowledge, but is not to be changed by the viewer. This could be public information such as property information at city hall and other public records.
Personal information is very important to keep secure. We have heard about Cendant being hacked into last year and thousands of people’s personal data was comprised. In this day and age of identity theft, keeping our names and SSN’s secure is important.
Data integrity is also important. We want to know that the information that we are looking at is accurate, up-to-date and reliable. We do so much of our research on-line now that we want to know that the information that we may be relying on to validate our suppositions is true and accurate.
Security Flaws
First and foremost, as with any computer program, there is always the dangers of viruses and worms that may be programmed to attack a specific program and Access is no different. There have also been instances of macros being programmed that either run automatically or when you have downloaded and installed them as part of your Access data.
Remote Code executions for Microsoft office seem to be the greatest occurrence of vulnerability of security flaws and need to be dealt with. There were many instances of this, mostly for Office itself, but occasionally for PowerPoint and some of the other applications, but for the most part they applied to the application as a whole.
In other words, someone at another location can access the vulnerability through your office program to gain access and even control over your computer and then do what they want with your data and/or your computer.
Exchange Limitations and Complications
Limitations to Exchanging Database Objects
First of all, there are some basics in exchanging data that are true for all circumstances. You should have your current programs updated so that you have the latest patches and tools available.
When importing from other applications you should also be aware that the other database programs that you are importing from may not be consistent in their labeling conventions as to what you are used to in Access. Be aware of these differences when labels are created and data is formatted.
If you are importing files from text or spreadsheet programs, how is the information formatted. Is it coming in a fixed-width format or a delimited format, and if in delimited format, what is the separating character.
Also if your field sizes are not bit enough in the fields you are importing to, your information may be truncated. You may also be importing one type of data into a field formatted as something different and you will get an error. Other errors that you may see are key violation, validation rule failure, null in required field and unparsable record. These could result from rules you have set in the formatting of the fields in the table you are attempting to import into.
While exporting, especially to older versions of programs, you need to be aware that file names may be truncated as older versions of programs often only allowed names eight characters long.
When exporting you also want to be careful that your data formats are common, as unusual formats may cause problems during the export process or the import process in the other program. It is also smart to use the export wizard since this will help guide you through the process and choose delimiting characters for a smooth export/import process.
Some restrictions that apply are:
1. Only tables can be exported to an Access 97 and earlier file.
2. The Link Table Wizard used the first row of data as the column headings. You may need to adjust this in the table you are wanting to import.
3. You cannot update a linked Microsoft Excel spreadsheet in the Access Database tables. This is by Microsoft’s design and you must either open the linked Excel workbook in Excel, make the changes, save, and then close the workbook. Or you can import the Excel workbook into Access, make your changes, then export back to Excel.
Sharing Complications
When databases are being used, there is invariably a competition for the data being used. Unless the settings are such, when one person has files open, others may be restricted for accessing them. The administrator needs to decide, do you want to just share the data tables or the queries and reports and forms as well? What levels of access are granted to the various users and user groups.
How is the data to be maintained and synchronized? Are there multiple users accessing the data at once so it is constantly updated, is each file blocked from another’s use while in use so that the changes are instant before access by the next user, or are changes merged during a nightly update utility run? How accurate would any of these methods be for your data? Is the data to be locked when one is using it so others cannot?
Another point that needs to be considered is when to backup the data, how and to where? How sensitive the data is could dictate these considerations.
First of all, there are some basics in exchanging data that are true for all circumstances. You should have your current programs updated so that you have the latest patches and tools available.
When importing from other applications you should also be aware that the other database programs that you are importing from may not be consistent in their labeling conventions as to what you are used to in Access. Be aware of these differences when labels are created and data is formatted.
If you are importing files from text or spreadsheet programs, how is the information formatted. Is it coming in a fixed-width format or a delimited format, and if in delimited format, what is the separating character.
Also if your field sizes are not bit enough in the fields you are importing to, your information may be truncated. You may also be importing one type of data into a field formatted as something different and you will get an error. Other errors that you may see are key violation, validation rule failure, null in required field and unparsable record. These could result from rules you have set in the formatting of the fields in the table you are attempting to import into.
While exporting, especially to older versions of programs, you need to be aware that file names may be truncated as older versions of programs often only allowed names eight characters long.
When exporting you also want to be careful that your data formats are common, as unusual formats may cause problems during the export process or the import process in the other program. It is also smart to use the export wizard since this will help guide you through the process and choose delimiting characters for a smooth export/import process.
Some restrictions that apply are:
1. Only tables can be exported to an Access 97 and earlier file.
2. The Link Table Wizard used the first row of data as the column headings. You may need to adjust this in the table you are wanting to import.
3. You cannot update a linked Microsoft Excel spreadsheet in the Access Database tables. This is by Microsoft’s design and you must either open the linked Excel workbook in Excel, make the changes, save, and then close the workbook. Or you can import the Excel workbook into Access, make your changes, then export back to Excel.
Sharing Complications
When databases are being used, there is invariably a competition for the data being used. Unless the settings are such, when one person has files open, others may be restricted for accessing them. The administrator needs to decide, do you want to just share the data tables or the queries and reports and forms as well? What levels of access are granted to the various users and user groups.
How is the data to be maintained and synchronized? Are there multiple users accessing the data at once so it is constantly updated, is each file blocked from another’s use while in use so that the changes are instant before access by the next user, or are changes merged during a nightly update utility run? How accurate would any of these methods be for your data? Is the data to be locked when one is using it so others cannot?
Another point that needs to be considered is when to backup the data, how and to where? How sensitive the data is could dictate these considerations.
Customizing Access
Customizing Menus and Toolbars
I like full menus to show all the time when I am working with the Office Suite. This is possibly the first thing that I customize right away.
I also sometimes add buttons to the tool bars for things that I do often so I don’t have to go to a menu all the time. An example is a macro that I program and assign to a print report button. I’ve done this often in Excel but not in Access yet. Good food for thought.
Something else, after reading this chapter, that I might do is to create several customized buttons and create a custom menu for common tasks that I use routinely or have programmed rather than adding many, many buttons. This might work for tasks that I perform on a fairly routine basis, but not enough for a button on a toolbar.
I also tend to like to keep my hands on the keyboard and not use the mouse when possible, therefore I like keystrokes and being able to assign ctrl-codes is nice as it can be part of my regular typing and I don’t have to remove a hand from the keyboard to operate the mouse.
I like full menus to show all the time when I am working with the Office Suite. This is possibly the first thing that I customize right away.
I also sometimes add buttons to the tool bars for things that I do often so I don’t have to go to a menu all the time. An example is a macro that I program and assign to a print report button. I’ve done this often in Excel but not in Access yet. Good food for thought.
Something else, after reading this chapter, that I might do is to create several customized buttons and create a custom menu for common tasks that I use routinely or have programmed rather than adding many, many buttons. This might work for tasks that I perform on a fairly routine basis, but not enough for a button on a toolbar.
I also tend to like to keep my hands on the keyboard and not use the mouse when possible, therefore I like keystrokes and being able to assign ctrl-codes is nice as it can be part of my regular typing and I don’t have to remove a hand from the keyboard to operate the mouse.
Events
Events
Basically a database uses all eight of the events that are a part of action when you are using your database. Some more than other obviously, but all eight are used at one time or another during normal database construction and use.
Data Event – High Use – This event occurs probably the most of all and I would be using it a great deal for the database that I am creating. I am dealing all the time with the registration data from the very beginning and enter, edit and delete data as registration details change about each contestant.
Error and Timer Events – Low Use – This event would only be used in my database while constructing the various components and possibly while I am running queries or other tasks when I may not get an answer. None of my data needs to be updated through a timed action.
Filter Events – Medium Use – Sometimes it is just quicker to go into the data table and run a filter. Once a database is constructed though and queries are developed, the use of filters would probably decrease. I would use these sometimes as I have described above. I would hope to get away from them more and use queries that are run by buttons from my switchboard.
Focus Events – High Use – Most of my data manipulation will occur through the used of forms whether input, editing or deleting. From these forms my queries are run which result in reports. These are the two focus events that are active often. I will be manipulating data often through forms and running numerous reports at the conclusion of the competition. I rely on the reports.
Keyboard Events – High Use – Much of my data is input through the keyboard and creating the queries is a high combination of keystrokes and mouse actions. The first and second most active input is through the keyboard. First when I input it and second after I am manipulating the data.
Mouse Events – High Use – Once my data is input through the keyboard, and other than some input of criteria for queries, much of my action in the keyboard is through the mouse. So, mouse events are second and then my first active input.
Print Events – Medium Use – Printing will occur at very specific times during the use of my database. When it is used it will be very important. During the manipulation and use of the database, print events will be of little importance.
Windows Events – Medium/High Use – When I am navigating through forms to input/edit/delete data or resizing such a window I will be using a window event. Likewise if I am working on reports, then I will be using a window event. Where I am at in the development of this database will determine if I am using a window event.
Basically a database uses all eight of the events that are a part of action when you are using your database. Some more than other obviously, but all eight are used at one time or another during normal database construction and use.
Data Event – High Use – This event occurs probably the most of all and I would be using it a great deal for the database that I am creating. I am dealing all the time with the registration data from the very beginning and enter, edit and delete data as registration details change about each contestant.
Error and Timer Events – Low Use – This event would only be used in my database while constructing the various components and possibly while I am running queries or other tasks when I may not get an answer. None of my data needs to be updated through a timed action.
Filter Events – Medium Use – Sometimes it is just quicker to go into the data table and run a filter. Once a database is constructed though and queries are developed, the use of filters would probably decrease. I would use these sometimes as I have described above. I would hope to get away from them more and use queries that are run by buttons from my switchboard.
Focus Events – High Use – Most of my data manipulation will occur through the used of forms whether input, editing or deleting. From these forms my queries are run which result in reports. These are the two focus events that are active often. I will be manipulating data often through forms and running numerous reports at the conclusion of the competition. I rely on the reports.
Keyboard Events – High Use – Much of my data is input through the keyboard and creating the queries is a high combination of keystrokes and mouse actions. The first and second most active input is through the keyboard. First when I input it and second after I am manipulating the data.
Mouse Events – High Use – Once my data is input through the keyboard, and other than some input of criteria for queries, much of my action in the keyboard is through the mouse. So, mouse events are second and then my first active input.
Print Events – Medium Use – Printing will occur at very specific times during the use of my database. When it is used it will be very important. During the manipulation and use of the database, print events will be of little importance.
Windows Events – Medium/High Use – When I am navigating through forms to input/edit/delete data or resizing such a window I will be using a window event. Likewise if I am working on reports, then I will be using a window event. Where I am at in the development of this database will determine if I am using a window event.
DDE, Documentation and Office Assistant
DDE (Dynamic Data Exchange)
DDE is part of the windows environment that allows two programs to exchange data between each other. This can either be text or graphics. The two programs are in effect “communicating”. The two programs can share information and in effect be linked so that when the information changes in one program it will be updated in the other. An exchange is initiated and a connection is made, the transfer of information is accomplished, and whether through a manual or scheduled time, the link is terminated.
We often have a lower level of data exchange within the Microsoft Office environment as we link a spreadsheet in a word document and other such examples in the Office suite. A higher level of this though would be an external program, such as one written in C, that would open and transmit data to an excel spreadsheet. This is more likely when a specific task is better accomplished in one program than another, and the resulting data is then provided to the secondary program.
Other examples could be bank statements that are downloaded for importing into Quickbooks or stock quotes being downloaded for use in Excel.
Office Assistant
This is one of those office components that I like and don’t like. I don’t usually have it turned on since it can be constantly annoying and always popping up. I do like it though for quick answers to simpler questions in the office suite. I find that if I am unsure of where to find a command or the exact terminology used in the office suite, using the office assistant can find or direct to the area where I will find my answer. I find it good for simple questions and how-to’s.
When I have a more complex question about something, like the formatting of an expression or how to create a more advanced query, I find it much better to go through the Microsoft Office Help and/or the on-line help.
Documenting a Database
Sometimes large database systems are being worked on by several persons. If so, the document report shows a lot of the settings and defaults and other conventions that are being used as standards in the development process of the database.
While it is very long and detailed, it does break the database down into its various components and provides the details about each of these. Graphical representations of the relationships and other connections were helpful to look at as well.
I was also able to trace my variables and queries and reports and see where the data was coming from and how it was being formatted.
Sometimes I may also have trouble with one set of data and when I finally get it all working and formatted just the way that I want it, I can’t remember all of the various settings that I may have used to be able to use the same settings on another table of data. By looking at the document report, all of the settings are listed and the report could be followed to similarly format the other tables of data. There is the format painter in word and excel, but this will have to do in Access.
DDE is part of the windows environment that allows two programs to exchange data between each other. This can either be text or graphics. The two programs are in effect “communicating”. The two programs can share information and in effect be linked so that when the information changes in one program it will be updated in the other. An exchange is initiated and a connection is made, the transfer of information is accomplished, and whether through a manual or scheduled time, the link is terminated.
We often have a lower level of data exchange within the Microsoft Office environment as we link a spreadsheet in a word document and other such examples in the Office suite. A higher level of this though would be an external program, such as one written in C, that would open and transmit data to an excel spreadsheet. This is more likely when a specific task is better accomplished in one program than another, and the resulting data is then provided to the secondary program.
Other examples could be bank statements that are downloaded for importing into Quickbooks or stock quotes being downloaded for use in Excel.
Office Assistant
This is one of those office components that I like and don’t like. I don’t usually have it turned on since it can be constantly annoying and always popping up. I do like it though for quick answers to simpler questions in the office suite. I find that if I am unsure of where to find a command or the exact terminology used in the office suite, using the office assistant can find or direct to the area where I will find my answer. I find it good for simple questions and how-to’s.
When I have a more complex question about something, like the formatting of an expression or how to create a more advanced query, I find it much better to go through the Microsoft Office Help and/or the on-line help.
Documenting a Database
Sometimes large database systems are being worked on by several persons. If so, the document report shows a lot of the settings and defaults and other conventions that are being used as standards in the development process of the database.
While it is very long and detailed, it does break the database down into its various components and provides the details about each of these. Graphical representations of the relationships and other connections were helpful to look at as well.
I was also able to trace my variables and queries and reports and see where the data was coming from and how it was being formatted.
Sometimes I may also have trouble with one set of data and when I finally get it all working and formatted just the way that I want it, I can’t remember all of the various settings that I may have used to be able to use the same settings on another table of data. By looking at the document report, all of the settings are listed and the report could be followed to similarly format the other tables of data. There is the format painter in word and excel, but this will have to do in Access.
Tuesday, June 12, 2007
Microsoft Graph
Personally, I find the Microsoft Graph program a double-edged sword. I like the aspect that you can take data that you may have in a table in Word or Access and from that construct a chart without having to go into Excel. Many of the controls are the same and editing your chart appears to be basically no different than if you were in Excel.
This is about where my frustration begins with this program. While you can make modifications to the chart using the menu commands and toolbar buttons provided by the graph program, which are much, but not completely like Excel, that is about where the similarity ends.
Your data, whether a table in Word or a data table in Access, are not linked. Once the chart is inserted a copy of the data from the table is stored independently in the embedded chart object. Changing the table data won’t affect the chart. When I tried to edit the data in my Access chart, I got this dummy chart and data that showed up with nothing to do with my data. It was basically hopeless to try to modify the data. To change the data you would have to manually enter the actual data, as well as the row and column headings. Isn’t that a waste of time when you could link a table from Excel into Word or PowerPoint?
In my opinion, Microsoft Graph is a quick and dirty program to create a pictorial representation of your data, especially from Access, and to a lesser amount from Word and PowerPoint. Basic modifications can be accomplished, but for greater control and manipulation of the data and concerning the appearance of the data, I would stick with Excel. I would also seriously consider exporting my data from Access to a format that could be imported to Excel. I think the extra steps would be greatly offset by the frustration you would be avoiding.
This is about where my frustration begins with this program. While you can make modifications to the chart using the menu commands and toolbar buttons provided by the graph program, which are much, but not completely like Excel, that is about where the similarity ends.
Your data, whether a table in Word or a data table in Access, are not linked. Once the chart is inserted a copy of the data from the table is stored independently in the embedded chart object. Changing the table data won’t affect the chart. When I tried to edit the data in my Access chart, I got this dummy chart and data that showed up with nothing to do with my data. It was basically hopeless to try to modify the data. To change the data you would have to manually enter the actual data, as well as the row and column headings. Isn’t that a waste of time when you could link a table from Excel into Word or PowerPoint?
In my opinion, Microsoft Graph is a quick and dirty program to create a pictorial representation of your data, especially from Access, and to a lesser amount from Word and PowerPoint. Basic modifications can be accomplished, but for greater control and manipulation of the data and concerning the appearance of the data, I would stick with Excel. I would also seriously consider exporting my data from Access to a format that could be imported to Excel. I think the extra steps would be greatly offset by the frustration you would be avoiding.
SubReports
Subreports
In my database I could have several reports for each contest. A subreport could show more detail such as the actual score of each contestant and the time they took to take the test. This is information that is not on the reports that are distributed to the individual advisors. It is information that only I, as the competition organizer, may need access to so that I can answer questions advisors may have as to why their students did not place in an event, why one student placed above another, etc.
Another report is the overall listing of the contest and placings. A subreport could be the top five placings by contest for each school. This would be useful for each advisor for determining who is eligible for competition at the national level. Bump-ups occur when someone can’t attend so having such a listing is useful.
I also have placement listings (reports), for each of the open contests. This usually shows just the names in order from first on down. A sub-report could show the details, points correct and the times if a tie-breaker is needed.
In my database I could have several reports for each contest. A subreport could show more detail such as the actual score of each contestant and the time they took to take the test. This is information that is not on the reports that are distributed to the individual advisors. It is information that only I, as the competition organizer, may need access to so that I can answer questions advisors may have as to why their students did not place in an event, why one student placed above another, etc.
Another report is the overall listing of the contest and placings. A subreport could be the top five placings by contest for each school. This would be useful for each advisor for determining who is eligible for competition at the national level. Bump-ups occur when someone can’t attend so having such a listing is useful.
I also have placement listings (reports), for each of the open contests. This usually shows just the names in order from first on down. A sub-report could show the details, points correct and the times if a tie-breaker is needed.
Forms and Bound/Unbound Controls
Forms
First of all, forms look immensely more professional than typing in to a data table. Secondly, typing into a form is much easier to type information into, especially if there are many fields and you would end of scrolling off to the side to continue entering your information. With a form you would most likely be able to position all data fields on one page and could see all of the information as it was typed into the table. An important point to remember is your tab order. Reset this if need be to proceed sequentially through the form fields. You can see the order of the data easier and it is much easier to read all of the data in each record when viewed in the form.
It is also easier to see the data for verification and changing it needed.
Bound Control – This gets its value from a field in the table and as the data changes so does the value of the found control. The data fields that I added to my form to enter all of the contestant information are examples of bound controls.
Positives are that you can scroll through all of the data and check each as needed, and some of these controls on your forms can be drop down menus for easier selection of the data to fill the field. One must be careful though if you are moving through the data quickly and changing information as you could inadvertently change data by mistake. You also have to be careful as to what data you are looking at since in a bound control the data changes from one record to the next. Be sure you are on the right data record. Also, are the bound controls of data correctly represented by any unbound controls, graphics and other identifying photos, correct.
Unbound Control – These are items that have no connection to the data that is being entered in the table about the contestants. On my form the example would be the line that I drew to separate the field names from the fields where the data is being entered. If I wanted to put the BPA logo on the input form, that would be another example of an unbound control.
With unbound controls, since the data does not change as the data record changes, you will have to be sure that any graphics and/or photos that are placed as an unbound control correctly correlate to the bound controls on the same form. This could take a lot of time in any placements that you may have to do. While unbound controls can also improve the looks of your forms, such as lines and logos and graphics, too many can also clutter your form and may actually diminish the positive aspect of a form for readability and data input.
First of all, forms look immensely more professional than typing in to a data table. Secondly, typing into a form is much easier to type information into, especially if there are many fields and you would end of scrolling off to the side to continue entering your information. With a form you would most likely be able to position all data fields on one page and could see all of the information as it was typed into the table. An important point to remember is your tab order. Reset this if need be to proceed sequentially through the form fields. You can see the order of the data easier and it is much easier to read all of the data in each record when viewed in the form.
It is also easier to see the data for verification and changing it needed.
Bound Control – This gets its value from a field in the table and as the data changes so does the value of the found control. The data fields that I added to my form to enter all of the contestant information are examples of bound controls.
Positives are that you can scroll through all of the data and check each as needed, and some of these controls on your forms can be drop down menus for easier selection of the data to fill the field. One must be careful though if you are moving through the data quickly and changing information as you could inadvertently change data by mistake. You also have to be careful as to what data you are looking at since in a bound control the data changes from one record to the next. Be sure you are on the right data record. Also, are the bound controls of data correctly represented by any unbound controls, graphics and other identifying photos, correct.
Unbound Control – These are items that have no connection to the data that is being entered in the table about the contestants. On my form the example would be the line that I drew to separate the field names from the fields where the data is being entered. If I wanted to put the BPA logo on the input form, that would be another example of an unbound control.
With unbound controls, since the data does not change as the data record changes, you will have to be sure that any graphics and/or photos that are placed as an unbound control correctly correlate to the bound controls on the same form. This could take a lot of time in any placements that you may have to do. While unbound controls can also improve the looks of your forms, such as lines and logos and graphics, too many can also clutter your form and may actually diminish the positive aspect of a form for readability and data input.
Monday, June 11, 2007
SQL statement
Here is the SQL statement for Lesson 5.
SELECT *
FROM [COSTS]
WHERE [COST>10]
ORDER BY [PROJECT]
SELECT *
FROM [COSTS]
WHERE [COST>10]
ORDER BY [PROJECT]
Custom Calculated Field
There are six tests that all of our BPA member students can take in addition to the two specialized events they enter. The students can take none or all of theses "Open" events. They are typically multiple choice in nature and usually consist of 50 questions. We often have ties, for example two or more students may get 46 questions correct.
No ties are allowed in any contest, and considering that we give plaques to the top three in each contest, but recognize the top ten with certificates, it becomes important to break all ties. That is why we have all student write the start and stop time at the top of their test sheet. If we have a tie we break it by who finished the test quickest.
We are going to a computer based testing system this next year for these six tests. I will be able to download the results into Excel, which I will then be able to import into Access. The computer keeps track of the start and stop time. I've added a custom calculated field that subtracts these two times. I've additionally set the query to sort first by score and second by time. This creates a table which can be run into a report for documentation of the contest. See Financial Math in the Queries portion in my database.
No ties are allowed in any contest, and considering that we give plaques to the top three in each contest, but recognize the top ten with certificates, it becomes important to break all ties. That is why we have all student write the start and stop time at the top of their test sheet. If we have a tie we break it by who finished the test quickest.
We are going to a computer based testing system this next year for these six tests. I will be able to download the results into Excel, which I will then be able to import into Access. The computer keeps track of the start and stop time. I've added a custom calculated field that subtracts these two times. I've additionally set the query to sort first by score and second by time. This creates a table which can be run into a report for documentation of the contest. See Financial Math in the Queries portion in my database.
Saturday, June 9, 2007
Lesson 3 Indexes and Referential Integrity
Indexes
Indexes are almost like an automatic sort built into the column of data in a table. For example, if you have a lastname column indexed, it would be in order, and any new records added would be put in order per the index.
An index can provide for faster access to data in a query as it is already indexed. It is also helpful if you are sorting or searching by two or more columns at a time.
On the negative side, indexes take up disk space, and slow the process when adding, deleting and updating of rows.
Referential Integrity
Referential Integrity is a set of rules within the database between linked tables that helps to keep the database complete and without loose ends or straggling data. No related records can exist without a parent record. In addition, when referential integrity rules are enforced on the link, when you make a change in one table it is updated in the other. Also, if referential integrity is attempted to be enforced and the related fields are not the same type of data, the rule will be rejected and an error message will be displayed.
Indexes are almost like an automatic sort built into the column of data in a table. For example, if you have a lastname column indexed, it would be in order, and any new records added would be put in order per the index.
An index can provide for faster access to data in a query as it is already indexed. It is also helpful if you are sorting or searching by two or more columns at a time.
On the negative side, indexes take up disk space, and slow the process when adding, deleting and updating of rows.
Referential Integrity
Referential Integrity is a set of rules within the database between linked tables that helps to keep the database complete and without loose ends or straggling data. No related records can exist without a parent record. In addition, when referential integrity rules are enforced on the link, when you make a change in one table it is updated in the other. Also, if referential integrity is attempted to be enforced and the related fields are not the same type of data, the rule will be rejected and an error message will be displayed.
Friday, June 8, 2007
Database Types
Where to start. After reading the article about the different database types, it appears to me even more that the type of database to be used is extremely dependent on the task(s) to be performed and what the designer and/or use hope to accomplish.
For the average user, a basic relational database is probably all that is needed. I think of at school and the students primary key is their ID number that links the counseling office and the front office. The front office deals more with the students contact information, while the counseling office deals with schedules. The two tables of data are linked by the student ID when questions arise that each of the two offices may need to look up.
I can see insurance companies using a hierarchical form of a database since there is the primary covered person, “parent”, and the secondary covered person(s) “child(ren)” that are linked to the parent. All records lead back upward to the parent.
A network form of a database seems like it could get out of hand and become extremely complex in the links and connections that could be formed. What comes to mind here is an airline reservation site like Expedia or Orbitz. The cross-links that could be formed between airlines and cities and times and all of the other variables could create a web of connections that could be very complex.
In the areas of science and engineering for example, it seems very logical that an object-oriented database would make a great deal of sense. There is often a great deal of data that is stored, but often needs to be manipulated in some manner by a specific program. The database can store the data while the application program can process as needed for the necessary reports or other results.
This is a link that I found on the web that is a brief PowerPoint in pdf format explaining the basic types of databases followed by some basic database terminology and some in depth explanations specific to Access. Very useful for a high school level class.
http://www.slais.ubc.ca/COURSES/arst593b/03-04-wt2/7_TheRelationalDatabaseModel.pdf
For the average user, a basic relational database is probably all that is needed. I think of at school and the students primary key is their ID number that links the counseling office and the front office. The front office deals more with the students contact information, while the counseling office deals with schedules. The two tables of data are linked by the student ID when questions arise that each of the two offices may need to look up.
I can see insurance companies using a hierarchical form of a database since there is the primary covered person, “parent”, and the secondary covered person(s) “child(ren)” that are linked to the parent. All records lead back upward to the parent.
A network form of a database seems like it could get out of hand and become extremely complex in the links and connections that could be formed. What comes to mind here is an airline reservation site like Expedia or Orbitz. The cross-links that could be formed between airlines and cities and times and all of the other variables could create a web of connections that could be very complex.
In the areas of science and engineering for example, it seems very logical that an object-oriented database would make a great deal of sense. There is often a great deal of data that is stored, but often needs to be manipulated in some manner by a specific program. The database can store the data while the application program can process as needed for the necessary reports or other results.
This is a link that I found on the web that is a brief PowerPoint in pdf format explaining the basic types of databases followed by some basic database terminology and some in depth explanations specific to Access. Very useful for a high school level class.
http://www.slais.ubc.ca/COURSES/arst593b/03-04-wt2/7_TheRelationalDatabaseModel.pdf
Subscribe to:
Posts (Atom)
Posts
