I Need a database for work

[mrdata]

I need a database that will contain employee names and three job titles they will choose.

When a Job comes open I want to search the entire database and pull together a report that will show every employee that has chosen that job as one of his \ her three choices sorted by hire date.

I also would like a seperate table with the job titles and position codes that can be filled in automaticaly on the main form by typing the position code.

I have a database started with some of this but I can't get it to work right!
First I haven't got a clue how to make the job choice combo boxes link to the three textboxes on the form where the job titles chosen will be displayed.

Can someone please Help me?

Thanks
Charles

 

[Studentos]

Suppose you have 2 tables: JOBS (job_id, job_name),
EMPLOYEES (emp_id, emp_name, emp_hire_date, emp_job1_id, emp_job2_id, emp_job3_id)

Suppose also you have an EMPLOYEE single form where you edit current employee

To see all employees selected particular job (identified by job_id) is this query:

PARAMETERS p_job_id Long;
SELECT emp_name, emp_hire_date
FROM EMPLOYEES
WHERE emp_job1_id = p_job_id OR emp_job2_id = p_job_id OR emp_job3_id = p_job_id
ORDER BY emp_hire_date

But be prepared to use some programming to supply this p_job_id automatically (for example to make a button that would display results for selected employee's job). You can also link your query to form and pass form parameter:

SELECT emp_name, emp_hire_date
FROM EMPLOYEES
WHERE emp_job1_id = [Forms]![EMPLOYEE].[emp_job1_id]
OR emp_job2_id = [Forms]![EMPLOYEE].[emp_job1_id]
OR emp_job3_id = [Forms]![EMPLOYEE].[emp_job1_id]
OR emp_job1_id = [Forms]![EMPLOYEE].[emp_job2_id]
OR emp_job2_id = [Forms]![EMPLOYEE].[emp_job2_id]
OR emp_job3_id = [Forms]![EMPLOYEE].[emp_job2_id]
OR emp_job1_id = [Forms]![EMPLOYEE].[emp_job3_id]
OR emp_job2_id = [Forms]![EMPLOYEE].[emp_job3_id]
OR emp_job3_id = [Forms]![EMPLOYEE].[emp_job3_id]
ORDER BY emp_hire_date

Another solution would be to use UNION query such as

UNION_QUERY:

SELECT emp_id, emp_job1_id AS job_id
FROM EMPLOYEES
UNION
SELECT emp_id, emp_job2_id AS job_id
FROM EMPLOYEES
UNION
SELECT emp_id, emp_job3_id AS job_id
FROM EMPLOYEES

and then make another query based on this one

LEFTJOIN_QUERY:

SELECT job_id, emp_name, emp_hire_date
FROM UNION_QUERY
LEFT JOIN EMPLOYEES ON UNION_QUERY.emp_id = EMPLOYEES.emp_id
ORDER BY emp_hire_date

...and after that you can make a table or list form OTHER_EMPLOYEES with RecordSource = LEFTJOIN_QUERY

... and place this form as a subform to your employee single form, make Link Master Field = "emp_job1_id" for example, Link Chield Field = "job_id"

... and see that you have a list of other employees who selected job as currently edited employee did.

 

[mrdata]

Studentos:Thats sounds like it may work only

Idon't have a clue how to set this up.
I have very limited experience with access queries can you give me something to get started with.
I'm not asking you to do it for me but I don't know how to setup the tables.
And I don't know how to put the code you mentioned in a query.
Does it go on the criteria line of the query

I have never setup a multiple table database could never figure it out so I just put everything in one table which worked but with limitations.

If you can give me a sample database I sure would appreciate it.
All the samples on the sight are way to complicated for my needs I would not know where to begin modifying them.

Thanks so much for the quick reply

Charles

 

[Studentos]

Reading inbuild Access Help is very useful. Try start reading from "Creating and Working with Databases".
Start learning with books and tutorials on Microsoft Access, VBA (Visual Basic For Applications) and SQL.

Look through this tutorial:
http://www.fgcu.edu/support/office2000/access/

Read book "Access Database Design & Programming" by Getz & Co
http://www.amazon.com/gp/product/0596002734/ref=si3_rdr_bb_product/002-0178801-4939270?ie=UTF8

 

[mrdata]

Studentos: I have tried to use your example still stuck

I'm not getting anywhere can you please explain in more detail I think if I could see the way the tables link to each other I might be able to get somewhere.
All this relationship Query stuff is confusing me.

Please
Charles

 

[Studentos]

Here you go