PSMM


SCENARIO

You are employed by PSMM as a member of the social media and digital events team. You have been asked to carry out the following activities using the information and the data files identified above.

  • You are required to create a database to record the information from the questionnaires which will be distributed during the event. The questionnaires will request the following information from delegates:
    • Business name
    • Business address
    • Town
    • County
    • Postcode
    • Business sector
    • Contact name
    • Telephone number
    • Email address
    • Website address
    • Use Social Media already (Y/N) if Y, which Social Media channel(s) is/are used?
    • Which Social Media channel(s) are of interest?

The database must include:

    • measures which can help prevent incorrect data being entered from the questionnaires. In the past there have been issues e.g. data missing, incorrect postcodes, telephone numbers and email addresses;
    • a switchboard and other controls to aid the end user navigate around the database without having access to the actual tables, queries and report layouts;
    • suitable data entry form(s);
    • a query which will allow the end user to enter a date range so that only those records for potential customers since the query was last run are selected;
    • a query that will allow the end user to input a specific business sector;
    • queries and reports which will group the data.

It is important that the teams who will be working with the customers know what Social Media channel(s) a business wants to use, so any report must include this information.
PSMM has also requested that each business is provided with a unique business reference. This is made up of the initial(s) of the business name and the month and year of the entry onto the database. For example, Progress Social Media Marketing is added to the database in October 2018, therefore the unique business reference would be PSMM102018.
In addition, PSMM wants a date stamp added to the database so that every time a record is added, a date will automatically be inserted.
Finally, PSMM has also requested that the database allows for multiple entries of contact with each business. Each contact record would include:

    • Date of contact
    • Time of contact
    • Initials of PSMM contact
    • Name of Business contact
    • Method of contact i.e. telephone, email, Social Media, site visit, Webinar
    • Notes of outcome of discussion.


ACCESS

Open Access

Unlike any other Microsoft product, you have to save your database before you can start. Access gives you the default name "Database1"...

... change this to be "PSMM". Then make sure that you are saving the database in your PSMM folder.

Check that you have the right name and that it is saving to the right folder then press "Create".

This is your new blank database.


MAKING A DATA TABLE

You can already see the data table. It is hardly appropriate and clearly needs changing.

Click on "View" or "Views" and then "Design View".

Rollover the image ...

Access will now as you to give the table a name.

There is a convention for database object naming. Traditionally any table starts "tbl_" and then its name, so a table about businesses could be tbl_business.

This is the table in design view. There are two columns that you should note. "Field name" and "Data Type".

There are 13 data types known to Access and you need to be able to use 7 of them. Access usually (but not always) selects AutoNumber for an ID field.

Data type Notes Example
Short text This is for normal text based data up to 255 characters. Strangely, you would use text for a phone number. Journalist or Eastrea Road
Long text This is for normal text based data more than 255 characters. This is sometimes called a "memo" field and is where you can insert a lot of text in a single field.
Number This is for numbers other than financial values that you need to be part of a calculation. 4702 or 98.4
Large number This is for numbers with a large number of digits, it is highly unlikely that you will need this, number is usually sufficient.  
Date/time The stores date or times 10 / 10 / 18 or 10th October 2018 or 15:00
Currency Any financial value £25.99
AutoNumber This is a field that automatically counts the number of records in a table and assigns a unique numerical value to each one. 1 or 99
Yes/No This is the Boolean data type, the values can only be true or false or yes or no. True. Yes
OLE object This relates to object linking and embedding and is for associating a document with e record.  
Hyperlink Includes a hyperlink to a website as part of the record. www.snb.guru
Attachment This can attach any document to a record whether it can be opened by the database or not.  
Calculated This stores the result of a calculation as a calculation and not the result  
Lookup wizard This links the table to another table, and only records from the other table can be included in this table.  

Type the name of the first field under ID and then press "Enter" and Access will select the data type that it thinks is best - in this case "Short Text" which is a good guess.

Repeat this with the other fields. Note that the business address is stored as street - town - county and the field about the social media channels is not included - there is a reason for this that will become apparent later.

All that is necessary is to change the data types for "website" and "social media used".

Save the data table by right clicking on the tab with the name of the data table ands select "Save".

We would be ready to close the table but we still have a few changes to make.

The scenario points out that

PSMM has also requested that each business is provided with a unique business reference. This is made up of the initial(s) of the business name and the month and year of the entry onto the database. For example, Progress Social Media Marketing is added to the database in October 2018, therefore the unique business reference would be PSMM102018.

Thus we need to change the "ID" to be "Business reference". This has to be done carefully as this field is the primary key. Do you notice the little key that is next to ID, this indicates that this is the primary key of the table; every table has one. A primary key is the unique identifier of a table, there can be no duplicated data in this field.

The next thing to do is to add an "input mask" to the "Post Code" field. We will use the same technique later for the Business Reference.

Click on the field "Post Code" and then click on the row for "Input Mask". Finally click on the three dots at the end of the row.

Access will insist on saving the table, so click "Yes".

You will now see the input mask wizard. The first mask is for a phone number - which is not quite right - and the second is for the Postal code; click on it.

When you have selected Postal code click "Next".

Now comes the tricky part. As you can see from the table below, the 0 in the mask means a user must enter a digit.

Not all post codes have two digits at the start, PE7 1XB being a case in point.

Character

Explanation

0

User must enter a digit (0 to 9).

9

User can enter a digit (0 to 9).

#

User can enter a digit, space, plus or minus sign. If skipped, Access enters a blank space.

L

User must enter a letter.

?

User can enter a letter.

A

User must enter a letter or a digit.

a

User can enter a letter or a digit.

&

User must enter either a character or a space.

C

User can enter characters or spaces.

. , : ; - /

Decimal and thousands placeholders, date and time separators. The character you select depends on your Microsoft Windows regional settings.

>

Coverts all characters that follow to uppercase.

<

Converts all characters that follow to lowercase.

!

Causes the input mask to fill from left to right instead of from right to left.

\

Characters immediately following will be displayed literally.

""

Characters enclosed in double quotation marks will be displayed literally.

Therefore, change the second 0 to be a 9 so that >LL00 0LL becomes >LL09 0LL.

Press "Finish" and the input mask is applied to the field.

Now add the mask to "Business Reference".

Click on the "Business Reference" field, then click on the "input mask" and then the three dots.

Save the table when prompted.

This is the Input Mask Wizard, then click on "Edit list" to add a new mask to the list.

Click the little right arrow until the form is empty.

Now type in the details of the new mask for the Business ID.

The name is Business_ID.

The input mask is >L???-00-0000

You can use PSMM 10 2018 as the example. (Incidentally, work out what the mask means and what would be the business ID for Vodafone entered in November 2018?)

Press "Close" when you have all the data in the form.

The definition of the mask is now part of the database.

Scroll down to find it in the list and press "Next".

Now press "Finish".

Here is the mask when it is part of the Business Reference field.

You can now enter some test data. Add 5 records to your database table, remembering to add a suitable business reference for each one.

Click on "View" to see the data table so that you can add data.

Type in the records, one to each row. I have added Vodafone as you can see, it is up to you to add more of your of own.

Like a spreadsheet you can double click between the column titles to make the columns auto fit to the content.

I have shown the 5 records that I have created in the image below.


VALIDATION

According to the BBC validation "checks ensure that data entered into the computer is sensible. Data is checked in accordance with a set of rules. The computer's software can validate data while it is being entered into the computer. The main purpose of data validation is to spot an error. This can be done quickly and easily as the process is automated."

For the purposes of this database we will add data validation to the table rather than the form but in later projects you will see validation in forms as well.

There are 6 different types of validation according to the BBC website (spell check is not part of a database really.)

Validation type How it works Example usage
Check digit The last one or two digits in a code are used to check the other digits are correct Bar code readers in supermarkets use check digits
Format check (sometimes called a picture check) Checks the data is in the right format A National Insurance number is in the form LL 00 00 0 L where L is any letter and 9 is a compulsory number
Length check Checks the data isn't too short or too long A password which needs to be six letters long
Lookup table Looks up acceptable values in a table There are only seven possible days of the week or one table can only accept values that exist in another table of days.
Presence check Checks that data has been entered into a field In most databases a key field cannot be left blank
Range check Checks that a value falls within the specified range Number of hours worked must be less than 50 and more than 0

You have already seen one form of validation, which if these is it and where did you add it to improve the quality of your database?

The Post code is a Format Check, as is the Business Reference. You have to take care when applying a validation check to a data table that already has data in it, as any errors could result in data being removed from the database.

We are not going to implement the check digit as it is unnecessary in the context of this scenario, but we can apply all of the others.

Format check.

You have used this already for the Business Reference and Post Code. You can use this technique for any data field that has a consistent structure such as a post code.

Length check.

This checks that the right number of characters have been entered. Before you begin this process, take a copy of the data that may be modified so that you can put it back in case there is a disaster.

Open the table in design view (click on "View" in the top left corner).

Next add the "Validation Rule". In this case we are using the property len() of the field to calculate the number of characters in the field. You have to tell Access which field is being evaluated, so we have len([Telephone Number]). You have to match the field name exactly to the name that you have given the field.

Importantly note the use of the square brackets [] inside the round brackets(). The square brackets indicate the name of a field and the round brackets the parameters to a function.

Finally we have to set the condition that len([Telephone Number]) = 12 i.e. there are 12 characters in a phone number including the space.

When you click "View" again to get back to the data sheet view, Access will ask you to save the table again: say "Yes".

Access will then tell you that the data integrity rules have changed. This is normal, say "Yes" to accept that this new rule will be applied to your data. At this point be prepared to lose some so make a note of what the data was so that you can put it back if necessary.

Access asks again just to make you aware that this might go horribly wrong. It shouldn't if you have done it right.

Have a look at the telephone numbers and make sure that they are still there. They should be.

Now delete a digit from one of the numbers and press the <enter> key. When you do Access should complain. Press "OK" to remove the message and then the <ESC> key to put the data back.

That message was not too helpful, however we can write our own. Go back to the "Validation Rule" and look at the next property. this is the "Validation text".

Add a suitable message such as A telephone number must be 12 digits long including the space as I have done.

Save the table and return to the data and make a change again and you will see the nicer message.

A new validation rule completed.

Lookup table

This is where you use one table to check values against another table. Not tricky to do but involved and it is easy to make a mistake.

Open the business table, and click on the row "Business Address County". Copy the row with <CTRL> + C

From the "Create" menu select "Table Design"

From the "Home" menu click on "Paste"

This will make an identical row in a new table. It is essential that the two rows that will be connected are identical. When you are fetching data from one table to another, the fields being compared must be defined identically.

With the row still selected, click on "Design" and the "Primary Key" This will make the new field the primary key of the new table. The reason for this will be explained later. Suffice to say, every table needs a field that is the primary key for the table.

Right click on the tab for the new table and select "Save"

By default Access will use the name "Table1" change this to be something sensible, following the convention it will be tbl_County.

This is the saved table.

Swith to datasheet view and add the counties to the table, remember that these values must be unique so you can ignore duplicates. My main table has 5 records but only the 3 counties, Yorkshire, Hampshire and Berkshire.

Click on the tab for the business table and click on the row for "Business Address County". In the property section click on "Lookup"

This is the result.

Click on the drop down at the end of "Text Box" and select "Combo Box".

Select the drop down for "Row/Source" ...

... and choose "tbl_County"

Look further down and you will see the property "Limit to list" which is set to "No". Change this to be "Yes". Now only values in the list can be in the table. So if you want to include a business record in a new county then this county must be added to the list of counties in the tbl_County.

Save everything and test it. Now there will be a drop down for Business Address County and the counties in your list will populate the drop down.

Done.

Presence check

A presence check makes sure that the field has a value, i.e. the field is not empty. Note. It does not check that the value is sensible.

There are two ways to do this, the really easy way that traditionally examiners ignore and the slightly more tricky way that examiners are really keen on. Imagine that you are asked to make the "Customer name" a compulsory field, i.e. you should add a presence check to the field. You could do t like this.

Open the table in design view and click on the field to which you want to add the presence check. Find the "Required" property and change "No" to be "Yes"

Now try and save a record without a customer contact name in it and you will get an error message from Access.

It is not possible to edit this message, so the better way is the way the examiner likes.

Open the data table in design view again and select "contact name".

This time add the validation rule Is Not Null to the validation rule property and some useful validation text such as Please give this business a contact name. in the validation text property.

Save the table and test it and you will see a more useful error message for your user.

It is always best to use the examiner's preferred technique.

Done.

Range check

A range check makes sure that a value lies between two set limits, usually numeric. For example, if this were a purchasing system and the customer was buy hats then a reasonable range would be 1 to 10 as no-one would buy 0 or -1 hats and 10 hats is a little much.

Our table does not have a numeric field in it so to show that we can create a range check we will need to add one.

Re-open the table in design view and add a new field "Number of contacts" to the end of the list of fields. Change the field type to be "Number".

Before we can set the range check you need to go back and add "1" to the number of contacts field for each record that you have created so far.

Next, we can add the validation rule Between 1 And 10 to the validation rule property and There must be at least one contact to the validation text property.

Now when you try to make a new record without a recorded contact you will get a suitable message.

All of the validation required for this table is now complete.

On your own, add a lookup table for the Business Sector field.


DATA ENTRY FORM

Design the questionnaire that PSMM would have used to collect their data. Research what makes a good questionnaire, include terms such as "rationale" and "rubric".

They are collecting information on: Business name, Business address, Business sector, a contact name, phone number and email address as well as the website address of the business and whether or not they use social media. You should aim to get your design to fit on a single sheet of A4 if possible.

Making a data entry form in Access is actually quite easy.

Close all of the tables and then re-open the tbl_business in data view.

Click on "Create" and then "Form Wizard".

When the wizard launches, just check that you have the right table selected. If not then cancel the wizard and try again.

Click the >> button to select all of the fields in the table.

All of the fields will move from the left pane to the right pane.

Press "Finish".

Access will spend a few moments creating your form for you. Be patient at this stage.

This is the form that it makes.

Its OK but it can be improved.

This is a little trickier than the previous exercises but can be done if you are careful read everything and do not rush.

Click on "View" drop down at the top right of the screen and select "Design View"

This is the same form in design view. Now you can move, resize and colour the various parts of the form.

Firstly select all of the title...

...and change it to be more sensible. PSMM Business contacts Form might be considered better.

Then click on one of the fields, the outline will go orangey yellow.

Using the pull tabs in the corners and on the sides you can resize the box that the field contents fit in. At this point it is a bit of a guess, use the grid to help you. I would make the box the same height as the label next to it.

Make all of the fields smaller and neater and move them up so that they are nearer and take up less space. Ideally a form should fit on a single screen. It is possible to move one field to be next to another if you need to make room.

Right click open the background of the header click on the arrow next to "Fill/Back Color" and choose a colour.

Repeat this with the main body of the form as well. You can now change the colours of the text in the same way that you would with Word. Click on a text item and then look for "Format" in the menu bar.

Now the trickier bit.

Scroll down to the bottom of the form and you will see the bar called "Form Footer".

You can move the bar up to remove the blank space created when you re-arranged the rest of the form.

You can also pull down the footer a little - it will be white.

Select the "Design" menu and look for the "XXX" in a box. This is the tool for making buttons.

Click on the "XXX"

Draw a box in the footer and when you release the mouse the wizard will start.

Select the action "Go to First Record" and press "Next".

Leave the picture/text option at "Picture" and leave the settings as they are. Press "Finish".

The box you drew will resize itself and Access will create a button that will go to the first record.

Repeat this process in this order.

Go to Previous Record.

Go to Next Record.

Go to last Record.

This will create 4 buttons that you can arrange in a row, like this.

Click on "Form View" to see the effect. You will be able to use the buttons that you have made to move from record to record. Try it for yourself.

Finally the trickiest bit.

We are going to make the form a Modal pop Up form.

Right click on any object and look right at the bottom of the menu and you will see "Form Properties". Click on this to see the property sheet for the form on the right hand side of the screen.

Make sure that the selection says "Form" at the top. Click on the "All" tab to see all of the properties of the form.

Now carefully change a few of these properties and you will be impressed by the result.

Caption - Change it to be PSMM Business

Pop up - Change it from "No" to "Yes"

Modal - Change it from "No" to "Yes"

Border Style - Change it from "Sizeable" to "Dialog"

Record selectors - Change it from "Yes" to "No"

Navigation buttons - Change it from "Yes" to "No"

Dividing lines - Change it from "Yes" to "No"

Scroll bars - Change it from "Both" to "Neither"

Click on "Form View" to be impressed by the result.

I think that you will agree that this is not half bad.

Right click on the form in "All Access Objects" and choose "Rename". Rename the form to "frm_business" as the convention suggests that all forms be called frm_something sensible.


MAIN MENU

Every system that you create will need a main menu to launch all of the features that are required by the end user.

This is how a main form is made.

On the "Create" menu select "Blank Form".

From the "Design" menu choose the "Aa" tool - this is the tool that let you write text.

Draw a box with the "Aa" tool selected and then add the text PSMM.

Now use the various colouring and formatting tools that you have used before to make the letters PSMM into a heading. Colour the background of the form to match the colour scheme that you have already established with your previous form.

Now, from the "Design" menu choose the "XXX" button icon again and draw a box somewhere in the form.

When the wizard begins change the option in "Categories" from "Record Navigation" to "Form operations" and select the "Open Form" option. Press "Next".

At the moment there is only one form but at this point you select the form that you want the button to launch, in our case "frm_business". Press "Next".

Make sure that the radio button for "Open the form and show all the records" is selected. Press "Next".

Finally change the "Picture" option to be the "Text" option and change the text to be "Open Business Questionnaire Form". Press "Finish".

When you switch to "Design View" this should be the result. A form with a heading and a button that will open the first form that you made. Try it for yourself and see that it works.

Save the form as "frm_main"

Access the form properties as before by right clicking on any element of the form and choosing "Form Properties" from the menu. Carefully change a few of these properties and check the result.

Caption - Change it to be PSMM

Pop up - Change it from "No" to "Yes"

Modal - Change it from "No" to "Yes"

Border Style - Change it from "Sizeable" to "Dialog"

Record selectors - Change it from "Yes" to "No"

Navigation buttons - Change it from "Yes" to "No"

Dividing lines - Change it from "Yes" to "No"

Scroll bars - Change it from "Both" to "Neither"

Click on "Form View" to be impressed by the result. You should see a modal form with the System title (PSMM) and the button that will launch the first for that you made.

Any new forms, or reports can be added to your main form.

If the form is open as a "Modal Pop-up" then you can close it or return to design view by right clicking on the title bar and selection the appropriate option.

The final step is to make the form load automatically when you open the Access database.

From the "Create" menu choose the "Macro" option.

As soon as you select the macro option this screen will appear. You have only one action to add to the manu - you want the form "frm_main" to be opened as soon as Access is run. From the list of actions select "OpenForm". You may have to scroll down a little to find it.

This is the "OpenForm" action. All you have to do here is select the right form to open from the drop down on the right.

Now that the action includes the form name, you can save the macro.

The important factor here is the name of the macro. You have to give it the name AUTOEXEC. This is the macro that Access looks for when it is powered up.

Here you can see in "All Access Objects" that the macro has been saved as autoexec.

The final thing to do is to make sure that everything is saved and close Access. Open it again and you will be presented with the main form as soon as Access is loaded.

Done.


QUERIES

This is the power of all databases. Once the data is in the database, you can write a query to interrogate the database. The scenario asks for us to create a query that will "allow the end user to enter a date range so that only those records for potential customers since the query was last run are selected"

The first thing to do is to make the query to select records, then establish the date range, then apply the data range to the query and then make sure that records that have been included in one run of the query are not included in any further runs. Easy really.

The first part is the simple query, the other parts are the advanced query section.

Open the "Create" menu and choose the "Query Wizard".

The wizard offers 4 choices and we want the one that is already highlighted so press "OK".

The next part of the wizard shows the table that is to be interrogated, just check that you have "Table: tbl_business" selected.

Use the single chevron > to select the fields that are required in the query. Press "Next".

The next part of the wizard suggests a name for the query based on the database table. Change this to be a suitable query name such as qry_businesses as the convention suggests that all queries should begin qry.

Press "Finish" and Access will run the query.

At this stage there is no selection involved in the query, it is just presenting a collection of records, showing only the fields that have been selected.

Now begins the tricky bit. From the "Home" menu click on the "View" drop down and choose "Design View".

This is the same query in design view. You can see the table and the fields that have been selected.

In the field section there is a row called "Criteria"; this is where we can add the criteria to select the records that we need.

Right click on the "Criteria" row on the "Business Reference" column. From the menu that appears select "Build".

This is the "Expression Builder". If you work a lot with Access you will see this dialogue quite often.

Click on the + next to "Functions" and then "Built-in Functions" then in the next column click on "Text".

Scroll down through the values in the third column (Expression Values) until you find "Right"; Double click on "Right" and the details will appear in the Expression Builder itself.

Double click on <string> to select it and then click on "qry_businesses" then "Business Reference" and then double click on <value>.

Double click on <length> and then type 6.

We are looking for the 6 right hand most characters in the business reference, the date part.

Now add the rest of the selection criteria, I have chosen September 2018 (092018) but you could choose any month that would have values in your table, so the expression becomes

Right([Business Reference], 6) = 092018

Press "OK".

If you make the Business Reference column a little wider you can see all of the selection criteria that you have created.

Click on "Run" ...

... and the query will just show the businesses with an 09-2018 date. In my case I have 3 businesses with an 09-2018 date, yours probably will be different.

Easy isn't it.


ADVANCED QUERIES

The next question is to work out how to present the query in its best light and then how to connect the query to the main form where you can select the search criteria?

More of this later.


REPORTS

A report is a way of printing out the results of a query; it is customary to make a report of a query rather than making the query part of the report as it is easier to edit a query than it is to recreate a report.

Close all of your objects and have the business query that we have just made that I have called "qry_Business_Contacts_Month" selected but not open.

From the "Create" menu click on "Report Wizard" to launch the wizard that will help to make your report.

As before with the query wizard, check that the correct query has been selected by the wizard. Then click the double chevron ">>" to move all of the fields from available to selected. Press "Next".

You will now see the part of the wizard that helps with grouping records in the report. We are going to group the records by "Business Sector", so double click on "Business Sector" and you will see it move on the right hand side of the wizard.

Press "Finish".

Access will now create the report that you have designed and show it to you in "Print Preview".

Click the cross to close "Print Preview" and see the report in design view. You will note that the report groups the results by Business sector. You will also note that some of the pieces of text do not fit correctly.

To an extent, this stage is up to you but there are a few guidances that I would pass on to you before you make a multicolored dog's breakfast of a report.

  • Make it easy to read.
  • Make it simple.
  • Make it colourful but not gaudy.
  • Stick to 2 or 3 colours that complement each other - colour wheel ideas.

This is the report in design view. Note that I have coloured the report header in the same way as the form, right click and select a suitable background colour.

I have also changed the text and the colour and the font of the heading.

The highlighted section is the page header - the section that appears on every page - that has the field titles and the group heading (Business sector).

This highlighted section is wherethat data that matched the query is presented.

This is the footer, the code =Now() adds todays date to the report.

Cut the title Business Sector from the page header and paste it in to the Business Sector Header and then adjust its position as shown. Click on the field title to get the orange order and stretch it slightly so that all of Business Sector can be seen. I then made the field bold to make it stand out.

Colour the report to match your previous style. Make the headings distinctive but not dominating the page.

If you want to see the effect, click on "Print Preview"

Save the report with a suitable name. The convention states that reports should start rpt_

Add a button to the main form and test that it works.

Here's a tricky one to try; make a form of the query.

Access the form properties as before by right clicking on any element of the form and choosing "Form Properties" from the menu. Carefully change a few of these properties and check the result.

Caption - Change it to be PSMM

Pop up - Change it from "No" to "Yes"

Modal - Change it from "No" to "Yes"

Default view - Change ot to be "Datasheet"

Border Style - Change it from "Sizeable" to "Dialog"

Record selectors - Change it from "Yes" to "No"

Navigation buttons - Change it from "Yes" to "No"

Dividing lines - Change it from "Yes" to "No"

Scroll bars - Change it from "Both" to "Neither"

Click on "Form View" to be impressed by the result. You should see a modal form with the System title (PSMM) and the button that will launch the first for that you made.

Make a button to launch the form that you have just made. Test it to see that it works.

Finally the trickiest bit.

You are going to make a new table of months, then a drop down menu on the main form showing the list of months and then change the query so that it uses the value in that menu as the criterion for the search.

Make a table the same way that you did previously, call it tbl_Months and add the data that covers the months that you have used in your Business References.

Open the main form in design view and from the "Design" menu select the combo box tool. Press "Next".

Select the table of months, press "Next".

Move the field "Month" from the left hand pane of available fields to the right hand pane of selected fields with the > button. Press "Next".

Press "Next".

Press "Next".

Press "Finish".

This is the combo box. Make sure that you can see the properties and note that the name is something like "Combo9" (in my case, in your case the number will be different).

Change the name of the combo box to be "Choose_Month".

Arrange the combo box neatly on the main form. Save the main form but do not close it.

Open the query in design view (right click on the query to see the options on open).

You will see that the query has moved the search criterion to its own column. The criteria will be the month that you had selected.

Right click on the criteria row in the Expr1: column.

Open the database by clicking on the + next to PSMM.accdb.

Keep pressing + to open the database further to open the forms and then loaded forms and then frm_main.

Then click on Choose_Month (now you know why its name was changed).

Double click on <value>. Press "OK".

Save and close the query.

Open the form in Form view, select a month and run the query from the button and you will see the selected data only. (Fingers firmly crossed!!)

Done!!!


RELATIONSHIPS

Theory

 


DEPENDENT TABLES

Theory

 


FINISHING OFF

Use the form to add a new record and then check that you can find it with the query run from the main form.

snb contact details

If leaving a message is important ...

Phone

(+44) 1733-0000

Address

1234 Clive Sullivan Way,
Millward
Yorkshire
Great Britain