Viewing some information - mostly about Access databases

(It is hoped to expand this listing in due course...)

General area Topic Comments

Macros in Access Creating a new macro Macros are very useful for when a particular set of actions are carried out a lot, and these actions can be done by a keystroke on the keyboard or after a particular triggering event. Go to the macros area of your database, and click on new. You will now see a column to the left marked 'Action' and one marked 'Comments'. There are a number of actions that can be chosen, for various things that can be done. For now, lets start with a command that is useful for all macros, which is 'Set Warnings'. This is best set to 'No' in the area at the bottom left, and this will mean that when the macro runs, normal warning messages won't be shown; otherwise there would be tedious messages of the nature of - 'query records being updated', etc. - which aren't necessary. If the macro fails then error warnings to help with this will still show up. At the end of this macro there would be the instruction ' Stop Macro'. So what can be put in the middle of these instructions? A simple macro would be to put the word 'maximise' there. If this macro is saved, perhaps as mcrMaximize, and it is put in the form's properties in 'On Load', then when that form is opened, it will automatically become maximized.

Macros in Access Using Autokey macro It is very useful to assign a set of values to a macro, and this is done by assigning an AutoKeys macro group. Go to the macro area and click on New. Click Macro Names on the toolbar and in the Macro Name column type the key or key combination to which you want to assign the action or set of actions. Usually you would want to run a macro and put in 'RunMacro', and below at the left, enter the name of the macro you wish to run.

Macros in Access Sendkey property in a macro In a macro, one of the Actions is the Sendkey property. These are very useful, and should be used as much as possible. For instance, you might want to enter a certain letter of the alphabet a number of times. Thus you could create a macro with contains the command 'Sendkeys' For this command, (imagining that you wish to enter the letter 'y' and for the cursor to move down one, and this to be done five times: Enter: y{DOWN}y{DOWN}y{DOWN}y{DOWN}y{DOWN} Note: I usually start off the macro with the command 'Setwarnings' (this defaults to no warnings to be given) and the macro to finish with 'Stopmacro'. Try attaching the macro to a key combination, using Autokeys. (See elsewhere, or look this up in the help directory of Access).

Macros in Access Putting a postcode field into capitals automatically, using Sendkeys Action. You may want some fields to be always in capitals, but it may be tedious to change the Caps Lock field each time. Therefore, on the form that this is to take place on, first create a text box and note the name of it. Say the form is called frmEntry and the field in question is called 'PostCode' and the textbox is called 'Text31'. Create a new macro and introduce two rows of the SetValue function. The first 'SetValue' command would have the top line (Item) as: [Forms]![frmEntry]![Text31], and the bottom line (Expression) as: [Forms]![frmEntry]![PostCode]. (Thus the postcode information, in lowercase, is copied to text box Text31.) The second 'SetValue' command would have the top line (Item) as: [Forms]![frmEntry]![PostCode], and the bottom line (Expression) as: StrConv([Forms]![frmEntry]![Text31],1), so that the StrConv function is invoked, where the '1' setting is where it converts text to Upper Case, and the information is changed to Upper Case and put back into the Post Code field. In Design View, this macro may now be put in the properties of the control - 'PostCode' in, say, 'On Exit'. Thus when a postcode in ordinary text is put in, and you press tab to go on to another field, then the capitalised version of that postcode appears in the control.

Macros in Access Creating a concatanated field from 2 others You may want to create a new field from two others, e.g. if you have a christian name field and a surname field, with John Smith in each respectively, then you may want to create a field that contains John Smith, or Smith, John. The first steop is to go to the design of table, and create a new text field, to be called 'wholename' say. Put this field as a control onto the form that this is to take place on. Say the christian name field is called 'chrisname' and the surname one is 'surname'. Create a new macro and introduce a row for the SetValue function. The top line will reference the new control of 'wholename', with the bottom for what is sent there. Send the concatanated field to the 'wholename' one with an instruction on the bottom line like this: [chrisname] & " " & [surname], for the John Smith format, or [surname] & ", " & [chrisname] for the Smith, John format. This macro may now be put in the properties of the control - 'surname' in, say, 'On Exit', assuming that a christian name is put in the christian name field first. Thus when the surname field is left, in the example above, either John Smith or Smith, John is put into the wholename field in the underlying table.

Queries in Access Select query This is so important that it is mentioned briefly - select queries are used to select chosen records. The wild card operator * can be used as well.

Queries in Access Make table query The output of a query is usually shown on the screen. However it is possible to make the output go to a table, and this is done by clicking on the Query tab at the top of the Design View and if you click on 'Make Table' then you can give it a name, and when the exclamation mark is clicked the output of the query goes to this table.

Queries in Access Code for searching Rather than input values directly into a code each time, some basic code can be put into a query's field as follows: [enter number value: ]. If this is put in a numeric field then when the query is opened the words - 'enter number value:' appear and if a number is input, then records with that value in that field are selected. If the field is a text one, then the following will select items with particular characters appearing anywhere in the strings in that field - Like "*" & [Enter letters from the code: ] & "*".

Queries in Access Aggregate query These are very useful. Imagine you have records consisting of no of items bought, cost of items bought, date when bought, etc. It is possible to develop a query to get the total cost of particular items bought, for instance. Start with a normal select query, and bring down the cost of items bought field, and the type of item field. click on the icon with a capital sigma sign, (like a large E), and you will notice that in one row of the query, everything changes to the word 'Group'. Leave that word in the type of item field, and change it to 'sum' in the costs field. Run the query (exclamation mark) and you will see that there is information on the cost of each item for all the items.

Queries in Access Update query The update query is very useful. For example you might have a field for the surname, and a field for the christian name of people's names, and you might want to combine them into an extra field. If you had a surname of Smith, and a christian name of James, then you could populate a new field with, say, Smith, James. Say the christian name field is Chrisname, and the surname field is Surname. Create a new field in the table to be called, say, Wholename. The query is created by going to the query area in Access, and by clicking on 'New'. Then 'Design View', and add the table that is holding the data. Just bring down the one field, - 'Wholename'. Click on the Query button at the top of the screen. Select 'Update'. This will change the format, so that there is a row called 'Update to'. Enter on this line the following: [Surname]&", "&[Chrisname]. Click on the exclamation mark to run it. Note that there is a 'Criteria' row just below the 'Update To' one. This can be used so that the update can only be done in certain circumstances, according to what is put in that row. It could depend on the values in the field already brought down, or a new field one could be brought down, and updates only carried out according to the values in that field and the value put into the criteria field.

Queries in Access SQL view If a query is created visually in Access, it is still possible to see the SQL code, and this can be done by clicking on the View button and choosing that format. It is useful to be able to see the sql code because sometimes there are things that can be done visually which are difficult to undo visually, but can be done in sql, e.g. once you've made a table to output the data to. Also it is possible to do things in sql which can't obviously be done visually, e.g. adding a output field dynamically.

Queries in Access Importing data to a table when there's data already there. This is something that I have had to do and I thought I would write it down whilst its still fresh - for items in locations in a warehouse. Imagine the data is to come from an excel spreadsheet and to go into a table called 'Items' in a field called 'Code'. Have a second field called 'Miscinfo' in that table. Bring the spreadsheet data into a new table called 'Incoming'. Both tables have an index field which comprises all locations in 'Items' and some of the locations in 'Incoming'. Create a query that joins up the two tables by the indexed field, and change the query to an update one so that a value, say y, is input into the 'MiscInfo' field if there is a value in the 'Incoming' table for a location. Now start a 2nd query so that the actual data can be brought into the 'Items' table according to when the value of the 'MiscInfo' field is 'y' or not. Now check for duplicate values!

Forms and controls in Access Combo field control Instead of entering data directly into a field on a form, replace the field by a combo. Before you do this, however, create a summing query that takes the original data of the field in question, and which sums it (click on the Sigma sign to the top, right of the design form.. (In other words, this means that the query has as its value each individual value appearing in the field's entries). Now create the combo, and have it take its values from that query. Now when data is entered into the combo control, it will only be necessary to put in one or two of the first letters of the value, (providing that value has been put in before), and the rest of that particular value will be entered automatically. (Default values can be entered as usual). It should be mentioned that combo boxes are designed to take their information from a table, and in practice that is a very useful thing to do. The values in the table are those that are to be allowed to go into that field, and it changes can be allowed by changing the values in the table. In some circumstances it can be arranged that a set of words appears in the combo box, but that a code goes into the field when that is chosen, e.g. when the code numbers can't really be remembered but the words can. The word list can be ordered alphabetically. Another useful thing to do is to arrange that the list of values appears whenever one goes into that combo box. This can be done by cloing to the properties of the combo, and finding 'On Entry'. Go to the second box to the right, (VBA code), and enter 'download' followed by the name of that combo box, which can be seen above the line where download ... is being entered.

Forms and controls in Access Default values If you are entering values into a form, and there is a particular value that often comes up, then it is possible to set that value up as a default value. Click to the far upper left, where there is a button shaped like a trigonometry set. This will take you to the design of the form. Left click on the field that you are concerned with, and right click. Go to the properties part of the list, and click on that. A few entries down there will be the 'Default' row. Enter the information that occurs often. Leave the design of the form, and go into the usual format. You will find that new entries will then show this value automatically. When you enter a field there is a choice as to whether the whole field is selected, or whether the cursor goes to the front or the back of the field. (Tools, Options, Keyboard). If the whole field is selected, then a particular field may show a default value which will be deleted as soon as any other information is entered into that field. Another trick is to use the text box button to create a text box on the form. If a value is entered in this box the value will stay the same until it is changed. Thus you could set up a few text boxes into which you could enter a few commonly occuring values. Set up macros using the 'SetValue' command, so that values in one of the text boxes can be entered into a particular field. Give the macros shortcut keys using the Autokeys macro. Thus one of the commonly occuring values can be put into a field just by invoking the macro that copies from the text box to the field in question. (Something of this nature can be set up to copy information from fields in the present record, to the same fields when the next record information is entered - (Note: info is copied into text boxes, the next record is entered 'Gotorecord', and info from the text box is copied to the field(s).

php and mysql Overview of php and mysql A very natural way to do this is to set up a MySQL database. Often your webspace provider will charge extra for this, so it is as well to check. If you already have the data in a Microsoft Access database then you would want to export it - probably as a comma delimited file. Also make sure that no fields have null values in them. Most webspace providers provide access/administration to the MySQL space with a piece of software called phpMyAdmin which is fairly easy to understand - the main thing is to set up tables with the correct kinds of fields to hold the data. PHP is a programming language and the combination of this with a MySQL database is a popular combination. It would probably be a good idea to get some books to practice with and use, and I have found - 'PHP in easy steps' by Mike McGrath - very useful. Another site - www.sfsdir.org.uk uses these systems extensively. Vist there to see how it works.

General area	Topic	Comments
Macros in Access	Creating a new macro	Macros are very useful for when a particular set of actions are carried out a lot, and these actions can be done by a keystroke on the keyboard or after a particular triggering event. Go to the macros area of your database, and click on new. You will now see a column to the left marked 'Action' and one marked 'Comments'. There are a number of actions that can be chosen, for various things that can be done. For now, lets start with a command that is useful for all macros, which is 'Set Warnings'. This is best set to 'No' in the area at the bottom left, and this will mean that when the macro runs, normal warning messages won't be shown; otherwise there would be tedious messages of the nature of - 'query records being updated', etc. - which aren't necessary. If the macro fails then error warnings to help with this will still show up. At the end of this macro there would be the instruction ' Stop Macro'. So what can be put in the middle of these instructions? A simple macro would be to put the word 'maximise' there. If this macro is saved, perhaps as mcrMaximize, and it is put in the form's properties in 'On Load', then when that form is opened, it will automatically become maximized.
Macros in Access	Using Autokey macro	It is very useful to assign a set of values to a macro, and this is done by assigning an AutoKeys macro group. Go to the macro area and click on New. Click Macro Names on the toolbar and in the Macro Name column type the key or key combination to which you want to assign the action or set of actions. Usually you would want to run a macro and put in 'RunMacro', and below at the left, enter the name of the macro you wish to run.
Macros in Access	Sendkey property in a macro	In a macro, one of the Actions is the Sendkey property. These are very useful, and should be used as much as possible. For instance, you might want to enter a certain letter of the alphabet a number of times. Thus you could create a macro with contains the command 'Sendkeys' For this command, (imagining that you wish to enter the letter 'y' and for the cursor to move down one, and this to be done five times: Enter: y{DOWN}y{DOWN}y{DOWN}y{DOWN}y{DOWN} Note: I usually start off the macro with the command 'Setwarnings' (this defaults to no warnings to be given) and the macro to finish with 'Stopmacro'. Try attaching the macro to a key combination, using Autokeys. (See elsewhere, or look this up in the help directory of Access).
Macros in Access	Putting a postcode field into capitals automatically, using Sendkeys Action.	You may want some fields to be always in capitals, but it may be tedious to change the Caps Lock field each time. Therefore, on the form that this is to take place on, first create a text box and note the name of it. Say the form is called frmEntry and the field in question is called 'PostCode' and the textbox is called 'Text31'. Create a new macro and introduce two rows of the SetValue function. The first 'SetValue' command would have the top line (Item) as: [Forms]![frmEntry]![Text31], and the bottom line (Expression) as: [Forms]![frmEntry]![PostCode]. (Thus the postcode information, in lowercase, is copied to text box Text31.) The second 'SetValue' command would have the top line (Item) as: [Forms]![frmEntry]![PostCode], and the bottom line (Expression) as: StrConv([Forms]![frmEntry]![Text31],1), so that the StrConv function is invoked, where the '1' setting is where it converts text to Upper Case, and the information is changed to Upper Case and put back into the Post Code field. In Design View, this macro may now be put in the properties of the control - 'PostCode' in, say, 'On Exit'. Thus when a postcode in ordinary text is put in, and you press tab to go on to another field, then the capitalised version of that postcode appears in the control.
Macros in Access	Creating a concatanated field from 2 others	You may want to create a new field from two others, e.g. if you have a christian name field and a surname field, with John Smith in each respectively, then you may want to create a field that contains John Smith, or Smith, John. The first steop is to go to the design of table, and create a new text field, to be called 'wholename' say. Put this field as a control onto the form that this is to take place on. Say the christian name field is called 'chrisname' and the surname one is 'surname'. Create a new macro and introduce a row for the SetValue function. The top line will reference the new control of 'wholename', with the bottom for what is sent there. Send the concatanated field to the 'wholename' one with an instruction on the bottom line like this: [chrisname] & " " & [surname], for the John Smith format, or [surname] & ", " & [chrisname] for the Smith, John format. This macro may now be put in the properties of the control - 'surname' in, say, 'On Exit', assuming that a christian name is put in the christian name field first. Thus when the surname field is left, in the example above, either John Smith or Smith, John is put into the wholename field in the underlying table.
Queries in Access	Select query	This is so important that it is mentioned briefly - select queries are used to select chosen records. The wild card operator * can be used as well.
Queries in Access	Make table query	The output of a query is usually shown on the screen. However it is possible to make the output go to a table, and this is done by clicking on the Query tab at the top of the Design View and if you click on 'Make Table' then you can give it a name, and when the exclamation mark is clicked the output of the query goes to this table.
Queries in Access	Code for searching	Rather than input values directly into a code each time, some basic code can be put into a query's field as follows: [enter number value: ]. If this is put in a numeric field then when the query is opened the words - 'enter number value:' appear and if a number is input, then records with that value in that field are selected. If the field is a text one, then the following will select items with particular characters appearing anywhere in the strings in that field - Like "" & [Enter letters from the code: ] & "".
Queries in Access	Aggregate query	These are very useful. Imagine you have records consisting of no of items bought, cost of items bought, date when bought, etc. It is possible to develop a query to get the total cost of particular items bought, for instance. Start with a normal select query, and bring down the cost of items bought field, and the type of item field. click on the icon with a capital sigma sign, (like a large E), and you will notice that in one row of the query, everything changes to the word 'Group'. Leave that word in the type of item field, and change it to 'sum' in the costs field. Run the query (exclamation mark) and you will see that there is information on the cost of each item for all the items.
Queries in Access	Update query	The update query is very useful. For example you might have a field for the surname, and a field for the christian name of people's names, and you might want to combine them into an extra field. If you had a surname of Smith, and a christian name of James, then you could populate a new field with, say, Smith, James. Say the christian name field is Chrisname, and the surname field is Surname. Create a new field in the table to be called, say, Wholename. The query is created by going to the query area in Access, and by clicking on 'New'. Then 'Design View', and add the table that is holding the data. Just bring down the one field, - 'Wholename'. Click on the Query button at the top of the screen. Select 'Update'. This will change the format, so that there is a row called 'Update to'. Enter on this line the following: [Surname]&", "&[Chrisname]. Click on the exclamation mark to run it. Note that there is a 'Criteria' row just below the 'Update To' one. This can be used so that the update can only be done in certain circumstances, according to what is put in that row. It could depend on the values in the field already brought down, or a new field one could be brought down, and updates only carried out according to the values in that field and the value put into the criteria field.
Queries in Access	SQL view	If a query is created visually in Access, it is still possible to see the SQL code, and this can be done by clicking on the View button and choosing that format. It is useful to be able to see the sql code because sometimes there are things that can be done visually which are difficult to undo visually, but can be done in sql, e.g. once you've made a table to output the data to. Also it is possible to do things in sql which can't obviously be done visually, e.g. adding a output field dynamically.
Queries in Access	Importing data to a table when there's data already there.	This is something that I have had to do and I thought I would write it down whilst its still fresh - for items in locations in a warehouse. Imagine the data is to come from an excel spreadsheet and to go into a table called 'Items' in a field called 'Code'. Have a second field called 'Miscinfo' in that table. Bring the spreadsheet data into a new table called 'Incoming'. Both tables have an index field which comprises all locations in 'Items' and some of the locations in 'Incoming'. Create a query that joins up the two tables by the indexed field, and change the query to an update one so that a value, say y, is input into the 'MiscInfo' field if there is a value in the 'Incoming' table for a location. Now start a 2nd query so that the actual data can be brought into the 'Items' table according to when the value of the 'MiscInfo' field is 'y' or not. Now check for duplicate values!
Forms and controls in Access	Combo field control	Instead of entering data directly into a field on a form, replace the field by a combo. Before you do this, however, create a summing query that takes the original data of the field in question, and which sums it (click on the Sigma sign to the top, right of the design form.. (In other words, this means that the query has as its value each individual value appearing in the field's entries). Now create the combo, and have it take its values from that query. Now when data is entered into the combo control, it will only be necessary to put in one or two of the first letters of the value, (providing that value has been put in before), and the rest of that particular value will be entered automatically. (Default values can be entered as usual). It should be mentioned that combo boxes are designed to take their information from a table, and in practice that is a very useful thing to do. The values in the table are those that are to be allowed to go into that field, and it changes can be allowed by changing the values in the table. In some circumstances it can be arranged that a set of words appears in the combo box, but that a code goes into the field when that is chosen, e.g. when the code numbers can't really be remembered but the words can. The word list can be ordered alphabetically. Another useful thing to do is to arrange that the list of values appears whenever one goes into that combo box. This can be done by cloing to the properties of the combo, and finding 'On Entry'. Go to the second box to the right, (VBA code), and enter 'download' followed by the name of that combo box, which can be seen above the line where download ... is being entered.
Forms and controls in Access	Default values	If you are entering values into a form, and there is a particular value that often comes up, then it is possible to set that value up as a default value. Click to the far upper left, where there is a button shaped like a trigonometry set. This will take you to the design of the form. Left click on the field that you are concerned with, and right click. Go to the properties part of the list, and click on that. A few entries down there will be the 'Default' row. Enter the information that occurs often. Leave the design of the form, and go into the usual format. You will find that new entries will then show this value automatically. When you enter a field there is a choice as to whether the whole field is selected, or whether the cursor goes to the front or the back of the field. (Tools, Options, Keyboard). If the whole field is selected, then a particular field may show a default value which will be deleted as soon as any other information is entered into that field. Another trick is to use the text box button to create a text box on the form. If a value is entered in this box the value will stay the same until it is changed. Thus you could set up a few text boxes into which you could enter a few commonly occuring values. Set up macros using the 'SetValue' command, so that values in one of the text boxes can be entered into a particular field. Give the macros shortcut keys using the Autokeys macro. Thus one of the commonly occuring values can be put into a field just by invoking the macro that copies from the text box to the field in question. (Something of this nature can be set up to copy information from fields in the present record, to the same fields when the next record information is entered - (Note: info is copied into text boxes, the next record is entered 'Gotorecord', and info from the text box is copied to the field(s).
php and mysql	Overview of php and mysql	A very natural way to do this is to set up a MySQL database. Often your webspace provider will charge extra for this, so it is as well to check. If you already have the data in a Microsoft Access database then you would want to export it - probably as a comma delimited file. Also make sure that no fields have null values in them. Most webspace providers provide access/administration to the MySQL space with a piece of software called phpMyAdmin which is fairly easy to understand - the main thing is to set up tables with the correct kinds of fields to hold the data. PHP is a programming language and the combination of this with a MySQL database is a popular combination. It would probably be a good idea to get some books to practice with and use, and I have found - 'PHP in easy steps' by Mike McGrath - very useful. Another site - www.sfsdir.org.uk uses these systems extensively. Vist there to see how it works.