NSC Computer Application Technology Information management: 5.1 Edit the tblArtists table as

Question

5.1 Edit the tblArtists table as follows:

5.1.1 Remove the ID field.
5.1.2 Change the data type of the Name field to a more suitable data type.
5.1.3 Set the field properties of the Cellnumber field so that the user is forced to enter data in this field.
5.1.4 Set the field properties of the StreetName field so that it will not accept more than 25 characters.
5.1.5 Insert a field named PostalCode between the City field and the E-mail field.
5.1.6 Change the format of the E-mail field to display the address in lower case.
5.1.7 The Genre field allows the user to choose only one option from a list of options.
Change a property of the Genre field to allow the user to choose more than one genre.

NOTE: If a warning appears, select 'Yes'.

5.1.8 Create an input mask on the ArtistCode field to ensure that the user inserts a code for the artist in the following format:

- ONE compulsory letter
- Followed by ONE compulsory letter or digit
- Followed by at least ONE digit, but possibly TWO digits

Example of a valid ArtistCode include: x35, A3a, gg952

Save and close the tblArtists table.

5.2 Create a new form called frm5_2 based on the tblArtists table.

- Include ALL the fields.
- Add your examination number in the form header.
- Change the font colour of the Name field so that ALL names display in red.

Save and close the frm5_2 form.

5.3 Open the qry5_3 query based on the tblData table.

- Modify the query so that it displays the total amount of each TypeDescription, as shown below.
- Sort the values from the highest to the lowest on the ArtistPrice field to display as follows:

TypeDescription	SumOfArtistPrice
Drawing	R 73 441.00
Watercolour	R 70 690.00
Architecture	R 67 203.00
Sculpture	R 59 750.00
Crafts	R 54 985.00
Painting	R 52 200.00
Design	R 49 680.00
Printmaking	R 45 900.00

Scultping	R 37 141.00

Save and close the qry5_3 query.

5.4 An error was made in the database when dates were captured incorrectly.

Open the qry5_4 query and do the following:

- Add a calculated field NoDays, which will determine for how many days the work of art has been exhibited, using the DatelIn field and the DateOut field. Errors will show as a negative value.
- Display only the error records where the DatelIn value is after the DateOut value.
- Display only the DatelIn field, the DateOut field and the new calculated field, NoDays.

Save and close the qry5_4 query.

5.5 Open the qry5_5 query and do the following:

Display only the records of those artists whose price (ArtistPrice) was more than R2 500 AND whose art pieces were sold (Sold).

Save and close the qry5_5 query.

5.6 Open the rpt5_6 report and turn on 'Group & Sort', if it is not already displayed.

- Add the current date and time in the report footer.
- Insert a function to determine the total ArtistPrice for art pieces per TypeDescription.
- Change the grouping of the DateOut field to display per month instead of per year.
- Ensure that the text box in the DateOut header displays the month.

Save and close the rpt5_6 report.

Save and close the 5ArtData database.

5.1 Edit the tblArtists table as follows: 5.1.1 Remove the ID field - NSC Computer Application Technology - Question 5 - 2018 - Paper 1

Worked Solution & Example Answer:5.1 Edit the tblArtists table as follows: 5.1.1 Remove the ID field - NSC Computer Application Technology - Question 5 - 2018 - Paper 1

5.1.1 Remove the ID field.

5.1.2 Change the data type of the Name field to a more suitable data type.

5.1.3 Set the field properties of the Cellnumber field so that the user is forced to enter data in this field.

5.1.4 Set the field properties of the StreetName field so that it will not accept more than 25 characters.

5.1.5 Insert a field named PostalCode between the City field and the E-mail field.

5.1.6 Change the format of the E-mail field to display the address in lower case.

5.1.7 The Genre field allows the user to choose only one option from a list of options. Change a property of the Genre field to allow the user to choose more than one genre.

5.1.8 Create an input mask on the ArtistCode field to ensure that the user inserts a code for the artist in the following format.

5.2 Create a new form called frm5_2 based on the tblArtists table.

5.3 Open the qry5_3 query based on the tblData table.

5.4 Open the qry5_4 query and do the following: Add a calculated field NoDays.

5.5 Open the qry5_5 query and do the following: Display only the records of those artists whose price (ArtistPrice) was more than R2 500 AND whose art pieces were sold (Sold).

5.6 Open the rpt5_6 report and turn on 'Group & Sort'.

Join the NSC students using SimpleStudy...