Some advice on how to approach this problem? (1 Viewer)

[andreas_udby]

I'm building a database to allow me to store and draw reports from Equal Employment Opportunity information needed for government-required reports. I have three tables:

tblJob:
1. jobSPN (a number attached to the Civil Service description of the position)
2. jobTitle

tblCandidate:
1. candLastName
2. candFirstName
3 through 12. [various Federally-required designators about the candidate)

tblReferral:
1. refReferralSource

I envision being able to enter the job specific information in one form and candidate information in another form.

Now, when candidates apply for a job, they detach and submit an EEO form giving me all the information above. In addition, some candidates may be applying for multiple jobs, and some jobs have multiple openings (i.e., we may be recruiting for five Clerk 3's in different departments).

I need to be able to group candidates under the job they have applied for and run a report showing the EEO percentages among that job's candidates. I'm pretty good on reports, so I'm not worried about setting that up. What I need help with is setting up the tables and relationships.

I had thought I would use [jobSPN] plus the date that specific position opened up for recruiting as a compound-key. What I would like to do is open up a form and select (as combo boxes):

[jobSPN]
[candLastName]
[candFirstName]
[refReferralSource]

and have the [jobTitle] populate itself automatically (so I can be sure I entered the appropriate [jobSPN]). Then I would like to enter two new fields: the date the job was opened, and the additional information about the referral source (such as the newspaper, website, or employee that informed the candidate about the job), which I assume would have to be stored in a new table. I set up a table called [tblMatrix] and added the four fields outlined above, plus the two new fields ([dateAdded] and [referralInfo]).

My questions are:
1. Should I set up a table or a query to store the combined information about jobs, candidates, and referrals?
2. How can I have an unbound text box ([jobTitle]) update itself based on the contents of another control (the [jobSPN] combo box)?
3. Should I be trying to combine all this data at the table/query level, or should I do that at report time?
4. How do I give a combo box a control source that's different from the record source of the form?

Thanks for whatever help or advice you can provide!
Andreas

 

[wazz]

My questions are:
1. Should I set up a table or a query to store the combined information about jobs, candidates, and referrals?

i haven't read the details above but, real quick:
you need separate tables for jobs, candidates etc. use queries to combine info.

2. How can I have an unbound text box ([jobTitle]) update itself based on the contents of another control (the [jobSPN] combo box)?

in the control source for jobtitle enter: =jobSPN (this will read and display the value of the other field, not store it)

3. Should I be trying to combine all this data at the table/query level, or should I do that at report time?

base your reports on a queries

4. How do I give a combo box a control source that's different from the record source of the form?

enter the source in the control source line

 

[andreas_udby]

in the control source for jobtitle enter: =jobSPN (this will read and display the value of the other field, not store it)

I guess I should clarify. If I choose job number 285 in the first combo box, I want the text box next to it to display the job title, "Senior Traffic Engineer." I though I could use a select statement in the text box's data source (something like SELECT [tblJob].[jobTitle] FROM [tblJob] WHERE [tblJob].[jobSPN] = Me![jobSPN]), but I just get an error. Is there some way to do this?

base your reports on a queries

Right. But say I'm entering those two new fields I mentioned (date posted and referral info); where would they be stored? Should I bind the form to a new table and then use that to drive my queries for the reports?

enter the source in the control source line

That's what I've tried, but I just keep getting an error message. Would you be able to post an example of the syntax?

Thanks,
Andreas