interview questions

[G]

I know this is 'off the wall'. I have to come up with a couple interview
questions for a client to determine if an applicant is qualified technically
to do the job in ACCESS. The task will be to combine several databases and
datasources into one usable database. Preferably with a frontend/backend.
Do any of you have any suggested questions that you would pose to an
applicant?

 

[mscertified]

I think you need more than a 'couple of questions'. I'd suggest more like
15-20. And there is not much point in us suggesting questions unless you know
the answers too.

 

[Jamie Collins]

I think you need more than a 'couple of questions'. I'd suggest more like
15-20. And there is not much point in us suggesting questions unless you know
the answers too.

TIC: Don't forget the 'qualitative' ones e.g.

Q. What would you name a table that models <anything you like here>?
A. <anything they like here so long as the first three letters are
tbl>.

Q. An Access database has a TEXT column that is constrained by
validation rule to be one of three possible values, the widest being
17 characters. What is the defined width of the column as seen in
Table Design view?
A. 50 characters wide.

etc etc

Jamie.

--

 

[G]

ok, thanks anyway

 

[Roger Carlson]

Well, I'll give it a go.

To me, the most important thing is that they understand database DESIGN.
Access as an application generator is easy enough to learn on their own if
they have a good understanding of table design. Design your tables right,
and you have an easy time with your app. Design them wrong, and you are
constantly creating work-arounds to fix things that should have been done
right the first time.

Several questions I use:

1) Q: What is a Primary Key.
A: It is a special kind of index whose value cannot be Null and must be
unique for each record. Primary keys can be either single fields or
composed of multiple fields. If composed of multiple fields, it is still a
single primary key. There is no such thing as multiple primary keys in a
table.

2) Q: Should an Autonumber field be used as a Primary Key? Why or why not?
A: There is no single correct answer for this, but I listen for the
reasoning. Opinions vary on this topic, but if they say No, they'd better
have a pretty convincing answer for me. In most cases, autonumber fields
make perfectly good primary keys. However, I know experienced developers
(including MVPs) who do not advocate autonumber fields for primary keys.
Valid reasons, IMO, include the fact that autonumber field sometimes get
confused and assign values previously assigned, and a primary key in Access
creates a clustered index, which may not be the best indexing scheme for
your table. Invalid reasons are dogmatic like: "my college prof said to
only use natural keys".

3) Q: How do you define Relationships in Access?
A: Relationships are defined in the Relationship window by clicking a
dragging the primay key field of one table to the corresponding foreign key
of another table. The Referential Integrity checkbox MUST be clicked,
although the Cascade options do not.
Note: I've had people proudly tell me that they never use the Relationship
Window or never set up Referential Integrity. If you don't do those things,
you don't HAVE relationships and are not using the power of a relational
database.

4) Q: What is a one-to-many relationship?
A: This is where a single record in a table is related to one or more
records in another table based on a primary-key/foreign-key.

5) Q: How do you create a Many-to-Many relationship in Access?
A: You cannot create a M:M relationship directly in Access. You must first
create a "linking" table (it can be called other things as well), which has
as foreign keys, the primary keys of the other two tables. Then you create
a One-to-Many relationship between each of the tables and the linking table
where the linking table is on the "many" side of both.

6) Q: What is Normalization?
A: Normalization is a methodology or process for removing as much redundant
data from the database as possible, while at the same time insuring that the
data will be able to be reassembled as needed. There are many stages of
normalization (call Normal Forms). Most developers agree that the minimum
level of normalization is the Third Normal Form (3NF) and that level is
acceptable for most database applications.
Note: Many people may not have a text-book definition of normalization
memorized, but have a pretty firm grasp of how to create a normalized
database.

7) Q: Given a new project, how would you go about developing it.?
A: Again, there is no one right answer here, but the answer they give will
tell you whether they've actually ever developed a database. One thing I
look for is an indication they sit down and develop their table structure
*first*.

There are no doubt more, and you could ask a LOT more about Access
specifically, but these questions, to me, are key to whether the person
either can develop (or can be trained to develop) Access database
applications.

 

[Tom Lake]

TIC: Don't forget the 'qualitative' ones e.g.

Q. What would you name a table that models <anything you like here>?
A. <anything they like here so long as the first three letters are
tbl>.

I don't use that convention and I have designed quite a few complex
databases.

Q. An Access database has a TEXT column that is constrained by
validation rule to be one of three possible values, the widest being
17 characters. What is the defined width of the column as seen in
Table Design view?
A. 50 characters wide.

How do you know the table designer (or Access Administrator) didn't change
the
default length of a text field or the length of that particular field in the
table?

Tom Lake

 

[Jamie Collins]

I don't use that [tbl] convention and I have designed quite a few complex
databases.

How do you know the table designer (or Access Administrator) didn't change
the
default length of a text field or the length of that particular field in the
table?

And you call yourself an Access developer?!

Note, as before, I have my T firmly embedded I my C.

Jamie.

--

 

[Tom Lake]

I don't use that [tbl] convention and I have designed quite a few
complex
databases.

How do you know the table designer (or Access Administrator) didn't
change
the
default length of a text field or the length of that particular field in
the
table?

And you call yourself an Access developer?!

Nah, I call myself Tom.

Note, as before, I have my T firmly embedded I my C.

Sorry! I must've had my H firmly over my E (hands over eyes). I missed it.

Tom Lake

 

[Jamie Collins]

Q: What is a Primary Key.
A: It is a special kind of index whose value cannot be Null and must be
unique for each record.

When you ask this question, please then make it clear you mean a
physical PRIMARY KEY rather than a logical key, primary or otherwise.

Q: Should an Autonumber field be used as a Primary Key?
I know experienced developers
(including MVPs) who do not advocate autonumber fields for primary keys.
Valid reasons, IMO, include the fact that autonumber field sometimes get
confused and assign values previously assigned

Erm, but if the Autonumber column contains duplicates then it cannot
have the PRIMARY KEY designation i.e. the PRIMARY KEY must have been
dropped. If there was indeed a bug (and I am in no way suggesting
there is) where a PRIMARY KEY could contain duplicate values then I
don't think Jet's credibility would survive the incident.

Q: How do you define Relationships in Access?
A: Relationships are defined in the Relationship window by clicking a
dragging the primay key field of one table to the corresponding foreign key
of another table. The Referential Integrity checkbox MUST be clicked

No it doesn't! Again, it's down to carefully wording your questions
(in my land you have to be carefully about such things i.e. "We didn't
hire you because we fudged the question" is no defence at the
tribunal). You asked how to define "Relationships" (the capital R
suggests you meant Access Relationships rather than logical
relationships -- I'm assuming these aren't verbal questions), you
didn't specify "Relationships with Referential Integrity enforced" or
"FOREIGN KEYs", did you?

Note: I've had people proudly tell me that they never use the Relationship
Window or never set up Referential Integrity. If you don't do those things,
you don't HAVE relationships and are not using the power of a relational
database.

I never use the Relationship window yet I do have referential
integrity (small R this time: are we still talking about Access
Relationships?). My answer to your question would be, "Put the Access
interface into ANSI-92 Query Mode, create a Query object, open it SQL
VIEW, type

ALTER TABLE ThisTable ADD
CONSTRAINT fk__ThisTable__ThatTable
FOREIGN KEY (key_col1, key_col2)
REFERENCES ThatTable (key_col1, key_col2)
ON DELETE CASCADE
ON UPDATE NO ACTION;

then click the Execute button. Alternatively, navigate the VBE, open
the Immediate Window, type CurrentProject.Connection.Execute "<above

4) Q: What is a one-to-many relationship?
A: This is where a single record in a table is related to one or more
records in another table based on a primary-key/foreign-key.

Relationships in the logical sense (and FOREIGN KEYs for that matter)
do not need to be based on a PRIMARY KEY. This is often the case when
you've used PRIMARY KEY to influence clustering (see you own answer to
an earlier question) and used NOT NULL UNIQUE constraints for the keys
on which FOREIGN KEYs will reference.

Most developers agree that the minimum
level of normalization is the Third Normal Form (3NF) and that level is
acceptable for most database applications.
Note: Many people may not have a text-book definition of normalization
memorized, but have a pretty firm grasp of how to create a normalized
database.

With respect, perhaps you should consult a textbook. 3NF is a
historical anomaly; BCNF is credited with being what 3NF was supposed
to be had someone not messed up the definition. And the textbook I've
got says 5NF is always achievable: my question would be, "What reasons
could exist for not attempting to achieve 5NF in every case?"

Jamie.

--

 

[Tony Toews [MVP]]

However, I know experienced developers
(including MVPs) who do not advocate autonumber fields for primary keys.
Valid reasons, IMO, include the fact that autonumber field sometimes get
confused and assign values previously assigned,

I'd disregard that reason as that is due to some kind of bug somewhere
in Jet. I've never seen that happen at any of my clients and I don't
see it often in the newsgroups. Also AFAIK that was fixed several
Jet 4.0 SPs ago.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Klatuu]

7) Q: Given a new project, how would you go about developing it.?
A: Again, there is no one right answer here, but the answer they give will
tell you whether they've actually ever developed a database. One thing I
look for is an indication they sit down and develop their table structure
*first*.

Table design is third
Business Rules are first
Logical Data design is second

 

[Klatuu]

You started one of those threads that exites a lot of people. But, to answer
your question, Roger gave some decent advice. Also, there are companies that
provide technical testing in multiple disiplines. Sorry I can't tell you who
they are, but a couple of times I have had to take them. The Access
evaluation I took was very thorough. You would have to know Access very well
to be able to get a good score.

 

[Klatuu]

Also TIC

--
Dave Hargis, Microsoft Access MVP

TIC: Don't forget the 'qualitative' ones e.g.

Q. What would you name a table that models <anything you like here>?
A. <anything they like here so long as the first three letters are
tbl>.

Depends on the language. In spanish it would be mes

Q. An Access database has a TEXT column that is constrained by
validation rule to be one of three possible values, the widest being
17 characters. What is the defined width of the column as seen in
Table Design view?
A. 50 characters wide.

Depends on whether I looked at it before I changed it.

Good to mess with you again, Jamie

 

[Jamie Collins]

You started one of those threads that exites a lot of people. But, to answer
your question, Roger gave some decent advice.

Allow me to elaborate on a reply I gave to Roger directly (he didn't reply
to me: I guess I didn't get the job, too much of a smart arse, wouldn't fit
in, fair enough <g>). In the UK if the interviewee requests it we are
required to provide the interview questions (including technical assessment
questions) and how the interviewers assessed the candidate based on the
answers given; they can ask for all documentation relating to their
application, including any notes (and doodles <g>) that may get taken down
during the interview. So if Roger's notes say, "I rate the answer as
incorrect because they didn't check the RI box when creating an Access
Relationship" then the interviewee could argue the toss (we're hiring geeks,
remember <g>) that they *did* give a correct answer because the question as
stated did not ask for RI. I'm guessing that Roger only ever uses Access
Relationship with RI enforced, thus forget that "Access Relationship" has a
meaning much more vague than RI implies e.g. the former can be based on
columns of differing data types whereas the latter requires the same data
type and a unique constraint (again guessing that Roger's are always based on
PKs rather than UNQIUEs). And here's my point: if you ask objective question
to which you think you know the answer, aren't you merely trying to hire
someone with a subset of your (or your technical expert's) knowledge? Call me
an optimist but I wouldn't mind end up with someone who will bring new
knowledge to the team (you know the old chestnut, "Aim to be the least
experienced person in the room" etc). I find subjective questions to be more
revealing e.g. "here's a scenario, what would you approach be?" That question
should kick off a discussion lasting half an hour or more, which is good but
if you have limited time at least aim for something where there isn't one
correct answer e.g. calendar table or a UDF (or something else) to calculate
holidays? For a collection class's container object what are the relative
pros/cons of an array, VBA.Collection, Dictionary object and fabricated ADO
recordset? (any other candidate objects?) For the objective questions, give
them a pen and paper and get them to write some code (VBA, SQL, etc). If
using a GUI is acceptable, print off some screen shot and get them to mimic
mouse clicks, drag and drop, etc with a pencil e.g. they click a menu button
and you switch the paper to show the appropriate screen etc.

Jamie.

--

 

[Klatuu]

Although not an interview question, the principle would apply to a technical
test question I was had to take as the final exam for a course on a radio
transmitter. The instructor had told us there would be only one question on
the exam. The question was:

Completely describe the APS-36.

 

[Jamie Collins]

Although not an interview question, the principle would apply to a technical
test question I was had to take as the final exam for a course on a radio
transmitter. The instructor had told us there would be only one question on
the exam. The question was:

Completely describe the APS-36.

I presume this is the equivalent of, "Completely describe the
Microsoft Jet Engine."

[PS I'm still waiting for my copy of the documentation e.g. details
such as this -- http://msdn2.microsoft.com/en-us/library/aa258274(SQL.80).aspx
-- but for Jet. It must exist, who has it...?]

Jamie.

--

 

[Klatuu]

Yes
--
Dave Hargis, Microsoft Access MVP

Although not an interview question, the principle would apply to a technical
test question I was had to take as the final exam for a course on a radio
transmitter. The instructor had told us there would be only one question on
the exam. The question was:

Completely describe the APS-36.

I presume this is the equivalent of, "Completely describe the
Microsoft Jet Engine."

[PS I'm still waiting for my copy of the documentation e.g. details
such as this -- http://msdn2.microsoft.com/en-us/library/aa258274(SQL.80).aspx
-- but for Jet. It must exist, who has it...?]

Jamie.

 

[Roger Carlson]

Actually, I had no idea you had replied to my answer as I've long since put
you on my Block Senders list. You are always more interested in picking
nits than answering the original poster's question. Not sure how you got
through this time. Oh well, add it again.

Certainly there are different answers. I thought I made it clear (though
perhaps I did not) that I was more interested in the reasoning behind the
answer than specific "correct" answers. Creating relationships in Access
via a SQL statement is, of course, legitimate, and if someone gave that
anwser, I would certainly know a great deal about their abilities.
(However, I would also wonder why they would choose to use a SQL statement
when they have a perfectly good UI option available.) The real purpose of
that question is to ascertain that the interviewee knows 1) what
relationships are and 2) how to create them. Too often I've interviewed
candidates who said: "What's a relationship?" or "Oh, I never use them. I
create them in the queries."

 

[Jamie Collins]

You are always more interested in picking
nits than answering the original poster's question.

I was not trying to be rude but on refelction I see I made my point at
your expense for which I apologize.

I was trying to stress the importance of the wording of your questions
and answers. I'm sorry to hear that you feel that is nit picking but,
again, I stress that the topic here is about hiring *geeks* i.e. the
kind of people who are interested in the fine detail.

I would also wonder why they would choose to use a SQL statement
when they have a perfectly good UI option available

Don't just wonder, ask! And stick around to here the answer; perhaps
we'll all learn something

I think your "perfectly good" assessment of the UI is ill founded.
Continuing the Relationships example, how do you create a "fast
foreign key" (see http://support.microsoft.com/kb/275561) or use the
SET NULL referential action (same article) using the UI?

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_tblInvoices
FOREIGN KEY NO INDEX (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE SET NULL;

Jamie.

--

 

[G]

I would like to thank all of you who responded to this question. You all gave
excellent answers.
The final questions I submitted were as follows:

Question: List the steps necessary to design a database.

Question: Name two analysis tools used in ACCESS

Question: Describe normalization

Question: What is the maximum size for an ACCESS database and what happens
when you exceed that size? What can you do to prevent this and what are your
options if you do exceed the maximum limit?

Question: Your task is to import data from another source, like Excel or a
Text File. This needs to be done on a regular basis and is very detailed.
What tool would you use in ACCESS to automate this:

Question: Can you use nested If-Then-Else statements in a query?

Question: Describe when the OnPrint property occurs and give an example of
when you used this property

I provided the answers to the non-technical interviewer. Again, Thanks to
all of you for your input!