Test for new employees (1 Viewer)

[FLabrecque]

Hi Folks!

I've been asked to make a 5 questions test to evaluation potential new employees' knowledge of MS-Access. Honestly, I need your input on what to ask. The goal is not to make a very hard test, but rather to see if somebody as at least basic knowledge of SQL and Ms-Access.

Some background information of what we do with MS-Access
We use MS-Access as a central hub for different download from different systems. We make a lot of daily imports. We export queries in Ms-Excel. We open it from Access and format the report. We make hundreds of reports every day. We don't really use forms or reports.

What questions to ask?
I was thinking of having 2 questions about SQL. One simple query with a formula, and a query with 2 tables (perhaps a left join). On what should my other questions be?

 

[mhartman]

Hello:

For SQL, I would ask the most basic task of returning all records from a specific table. If they REALLY no SQL they would no the short cut to do this. For instance,
'
SELECT *
FROM tblEmployees;

would return all records from tblEmployees. The key is the Asterisk symbol.

Regards
Mark

 

[The_Doc_Man]

Ask a couple of "when" or "why" questions.

1. When ignoring forms and reports, when do you absolutely need a prime key on a table - and why?

When something else has a foreign key matching the PK of that table in a one/many relationship and a JOIN is involved, the ONE side must have a PK

2. When you open a query based on a single table and the query shows no records, but opening the table directly shows at least some records, what has happened? Fact 1 - the field names in the query are correct. Fact 2 - the user has permission to open the table or the query.

Probably, the criteria are so restrictive as to return the null set as an answer.

3. When interacting with external files, such as exporting data to a text file or driving a Word Mail Merge, what is the best way to assure that the items appear as you want them in the order that you want them - and why?

Use a query rather than a table so that (1) you can format the data using the Format$() function as one or more of the fields and (2) you can use Order By or Group By functions to tightly control the order of appearance of the data BEFORE it is fed to something that doesn't contain a good SORT option.

4. When designing anything that outputs data to an external file, non-updating form, or report, what is the difference between a table and a query?

For most functions, there is no effective difference. Both represent simple recordsets. HOWEVER - queries can impose order whereas export wizards cannot. (Forms and reports can impose order via implied sorts.)

5. Which of the following cannot be expressed in two tables? One to one relationship, one to many relationship, many to one relationship, many to many relationship.

Many/many takes three tables - the two tables that hold the data you wanted to JOIN and a JUNCTION table that expresses the many/many relationship by simultaneously being the "many" side of two one/many relationships.

6. Access security is affected by permissions on many layers even for a standard database for which only normal workgroup security has been implemented. Name two layers and the nature of any security effects.

a. Access workgroup security can protect individual objects in the database with permissions at the major object level. Tables, queries, forms, reports, etc. are controlled by a set of permissions for users and/or groups.

b. Windows permissions affect the folder and files of the database as whole. Though the Windows permissions cannot protect a table, they can make a database read-only.

c. Network permissions affect users who connect to the Access database remotely. Network permissions can be more restrictive than the permissions on the database when opened locally.