A no. of questions ...

[poulsotw]

Hello

I am constructing a small database to house details of candidates that are invited to attend interview, whether they pass the interview and if so when they start work and on which shift. Nearly all of the fields relate specifically to candidates - as a result most of the information resides in the candidate table.

I have attached a MS Excel spreadsheet listing the fields in the tables so far (not including the lookup tables for title, gender,

There is also a table used to store information about the shifts. This database will only ever hold up to 6000 records so it isn't critical that the design is perfect - even so I would like to construct it as well as I can. If anyone has any comments about using more than 2 main tables to store the info I would be pleased to hear.

I also have a couple of specific questions,

1. At the end of the candidate table there will be 2 yes/no fields and a memo field. I would like to be able to have a form based on a query that lists values with a yes in the first y/n (field1 in example attached) but field 2 and memo are blank. When the 2nd y/n is marked as y the 'required' property of the memo field turns from no to yes (and vice versa if possible) this would be based on an update event - but I am not sure what. Any ideas ? as these fields are filled in they will disseapear from teh query 1 result but a second query willpick them up - these values are held in a subform datasheet (is this possible).
2. The memo field is held in the candidate database (relates to clarification on why a placed candidate exited a job early). As such it will only be filled in by those candiates who do leave early and is not always required. Does this mean it is best to hold this info in another table so space is not wasted ? as I mentioned the DB will only house 6000 records tops so space is not a critical issue.
3. Do I need to worry about checking definitions of relationships between lookup tables and main table ? the relationship between shifts table (maybe 15 shifts) and candidates (several thousand) will presumably be 1 to many as a shift can have many candidates working on it ?


any comments appreciated,

thanks,

Toby.

 

[Tiro Cupidus]

1. Are you needing to know exactly how to do it or which event to use?

2. It seems to me that it would be best to put the memo field in a table along with a CandidateID to refer to the candidate.

3. Not certain what you mean. A one-to-many seems appropriate.

 

[poulsotw]

Hi Tiro

thanks fro your reply,

1. I wasn't sure if this was the best way to do it, I found a macro today that enables / disables controls so I am confident I can sort this.

2. I had read that it is not good practice to import info from 1 table to another just so they can be joined - I don't see how else I can link them though. If I did this, would the relationship be 1 to 1. (my main concern is that I get this wrong and something goes horribly wrong down the line as the 2 people using the database haven't used access and wont have time to learn to fix my mistakes).

3. good.

My design plan now is to have 2 main forms to detail shift info and candidate info. Also additional forms to add info to the lookup tables when necessary. With the views of various types of candiates being forms based on queries linking the shifts and candidate table. Is this an acceptable approach ?

cheers,

Toby.

 

[Tiro Cupidus]

2. You're saying your database is already populated and you would need to move the memo information into another table? I got the impression you didn't have any info in the tables yet. Yes, it would be a 1-to-1 relationship between the CandidateID fields.

Of course, if you really don't want to worry about database size and want to keep it simpler, you can leave the memo field in the Candidate table. =)