Multiple Table Search

[Jen]

Hi Guys,
I've got 3 separate tables for 3 separate sections of the workplace. Each
table however is the exact same in structure. The fields are

Name
Employee ID
Job1
Job2
Job3

There are actually 12 jobs in each table but you get the idea. I'd like
to be able to create a relationship between the three tables and then create
a small pop-up form which would contain a drop down list of all the jobs
contained in the 3 tables.....and when you select a job from this list you
could maybe generate a report telling you who has been trained on this
particular job.

Ps...i should have mentioned that the fields Job1, Job2 and Job3 will
contain either the numbers 1,2 or 3 depending on the amount of training the
employee has had on that job.

Any help or advice would be great,
Thanks,
Jen

[Mo]

If the tables have an identical structure, why the need to split them up?

[Jen32 via AccessMonster.com]

Hi Mo,
The reason for the split is that the jobs in each table are different and
the managers from the three different sections asked me to keep them separate.
I knew it wasn't ideal but that's what they wanted.

Jen

Mo wrote:
>> Hi Guys,
>> I've got 3 separate tables for 3 separate sections of the workplace. Each

>[quoted text clipped - 20 lines]

>> Thanks,
>> Jen
>
>If the tables have an identical structure, why the need to split them up?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

[Duane Hookom]

In addition, it is a bad structure to have Job1, Job2,... Each job should
create a new record in a related table. If can't or won't change your table
structures, you can create a union query to normalize your records.
SELECT EmployeeID, Job1 As AmtTraining, "Job 1" as Job
FROM table1
WHERE Job1 Is Not Null
UNION ALL
SELECT EmployeeID, Job2, "Job 2"
FROM table1
WHERE Job2 Is Not Null
UNION ALL
-- etc --
UNION ALL
SELECT EmployeeID, Job12, "Job 12"
FROM table3
WHERE Job12 Is Not Null;

I would rework the entire table structure if it was my application.

--
Duane Hookom
Microsoft Access MVP

"Jen32 via AccessMonster.com" wrote:

> .
>

[John Spencer]

BAD table design. And bad design makes doing things with the data tougher.
Good design would require at least three tables.

Employees
== EmployeeID
== EmployeeName
== Department (unless an employee can be assigned to more than one department)

EmployeeJobs
== EmployeeID
== JobID
== TrainingReceived

Jobs
== JobID
== JobName

With this structure, getting a list of the jobs would be trivial. And getting
a list of employees with a specific skill or skill set would trivial.

There is no good way to get a list of jobs in your current structure since (if
I understand correctly) the job names are field names in your tables.

The best I could suggest is creating a table of all the possible jobs manually.

And you can keep the data separated for each department by using queries to
restrict what data is shown. If needed, you could even add the department to
the jobs table so you could isolate job list by department.

With your current structure I cannot envision any easy way to do what you
want. You could try building a union query on the each of the tables - Union
queries can only be built in the SQL view and not in query design view. A
union query would look something like the following - assuming only 3 job
columns in two tables

SELECT [Employee ID], Job1 as Level, "NameJob1" as JobType
FROM [Your Table One]
UNION
SELECT [Employee ID], Job2 as Level, "NameJob2" as JobType
FROM [Your Table One]
UNION
SELECT [Employee ID], Job3 as Level, "NameJob3" as JobType
FROM [Your Table One]
UNION
SELECT [Employee ID], Job1 as Level, "NameJob1" as JobType
FROM [Your Table Two]
UNION
SELECT [Employee ID], Job2 as Level, "NameJob2" as JobType
FROM [Your Table Two]
UNION
SELECT [Employee ID], Job3 as Level, "NameJob3" as JobType
FROM [Your Table Two]

With that as a saved query, you could use the saved query as if it were a
table and run a query against it to find individuals trained in a specific job

Create a new query
== Add the above saved union query to the new query
== Add the fields you want to see
== Enter criteria against JobType. Something like
= "Run Grinder"

Good luck (you need it with that structure).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Duane Hookom]

I would have a table of employees:
tblEmployees
=================
empEmpID autonumber primary key
empFirstName
empLastName
empSection
....

A table of jobs
tblJobs
=============
jobJobID autonumber primary key
jobTitle
jobSection (depends if jobs are dependent on sections)

A junction table of employees with jobs
tblEmployeeJobQuals
================
emjEmJID autonumber primary key
emjEmpID links to tblEmployees.empEmpID
emjJobID links to tblJobs.jobJobID
emjLevel level of training

--
Duane Hookom
Microsoft Access MVP

"Jen" wrote:

> Hi Duane,
> What would you do to change the table structure ?.....i've never tried a
> Union Query so i'd probably mess it up
>
> Jen

[Jen32 via AccessMonster.com]

Ok this is now officially a disaster :( i'm going to try and restructure
the tables the way you mentioned and see if i can get it to work.

Cheers for the help...i'll get back as soon as i can to harass ye all some
more

Thanks,
Jen

>> Hi Guys,
>> I've got 3 separate tables for 3 separate sections of the workplace. Each

>[quoted text clipped - 20 lines]
>> Thanks,
>> Jen

--

[John W. Vinson]

On Wed, 06 Jan 2010 12:18:31 GMT, "Jen32 via AccessMonster.com" <u57277@uwe>
wrote:

>the jobs in each table are different

... but you want to combine them anyway for this report!? Sorry, but that
makes no sense to me!

I agree with the other posters: you have a decent spreadsheet, but this is
*not* a properly structured relational database!
--

John W. Vinson [MVP]

[Jen32 via AccessMonster.com]

Ok i've been having a think and i need to ask you something

Currently i've got a data entry form which, when opened initially is blank
until you make a selection from a drop-down list of employees. When you make
the selection, that employee's details become visible in the various controls.
Basically it's just a list of the jobs in that section and his/her level of
training on each. I'm actually at a loss about how to redesign this form
using the structure you suggested above and at the same time keep it simple
for people who aren't familiar with Access (me included from the looks of it).
Do i need to have information being saved into more than one table at once ?.

Thanks,
Jen

Jen32 wrote:
>Ok this is now officially a disaster :( i'm going to try and restructure
>the tables the way you mentioned and see if i can get it to work.
>
>Cheers for the help...i'll get back as soon as i can to harass ye all some
>more
>
>Thanks,
>Jen
>

>>BAD table design. And bad design makes doing things with the data tougher.
>>Good design would require at least three tables.

>[quoted text clipped - 70 lines]

[John Spencer]

Once you have the tables built you would use a FORM and Sub-form to input the
data.

The employees table would have one record per employee
The Jobs table would have one record for each type of job
The employeeJobs table would have multiple records per employee - one for each
job and employee has.

The entry form would be based on the employees table and would show one
employee at a time (Single Form view). I would have an unbound combobox that
listed the employees and then moved to the correct record when the employee
was selected from the list. The wizard should help you build the combobox to
do this.

Then on the form, I would have a continuous sub-form based on EmployeeJobs table.
It would display a combobox based on the Jobs table and a textbox to input the
level of training (or skill level or whatever you call the value you use).
Since the sub-form would be linked to the main form by employeeid fields, you
would not need to enter the employeeid as Access will automatically do that
for you.

To ensure that you don't enter the same job and employee combination more than
once you could set a compound unique index on the combination of EmployeeID
and JobID in the EmployeeJobs table.

To create a multiple field unique index (Compound index)
--Open up the table in design mode
--Select View: Index from the menu
--Enter a name for the iIndex in first row under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
(Do NOT skip rows, do NOT enter the index name again)
--Continue moving down and selecting fieldnames until all needed fields are
included.
--Close the index window and close and save the table

Hope that is enough to get you started.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

Ok i've restructured the tables and taken your suggestion regarding the
subforms and it seems to be working quite well. One thing i need to be able
to do is to generate an individual report for each dept in a spreadsheet
style eg : Names down the side, jobs across the top and the amount of
training (a number) in the main body of the report. This goes up on the
notice board every month and was easy to do when i had my jobs set as field
names but now i'm not sure.

Also would i be right in saying that the EmployeeID field in the Employees
table is linked to the EmployeeID field in the EmployeeJobs table and the
JobID field in the EmployeeJobs table is linked to the JobID field in the
Jobs table ?

Thanks for your help,
Jen

>> Ok i've been having a think and i need to ask you something
>>

>[quoted text clipped - 24 lines]
>>>>> Thanks,
>>>>> Jen

--
Message posted via http://www.accessmonster.com

[John Spencer]

Ok. To generate that report (or query) you will need to use a crosstab query.

Yes, those are the correct relationships.

When you are ready to build the crosstab query, I would FIRST build a simple
select query that showed all the data you wanted. Once you have that built,
you can use the crosstab query wizard to build the basic crosstab query using
the select query as the source. After you have that built, you can post the
SQL of the crosstab query (and the select query it is based on) and ask for
help if there are things you want to do to tweak the query results.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Jen32 via AccessMonster.com wrote:
> Ok i've restructured the tables and taken your suggestion regarding the
> subforms and it seems to be working quite well. One thing i need to be able
> to do is to generate an individual report for each dept in a spreadsheet
> style eg : Names down the side, jobs across the top and the amount of
> training (a number) in the main body of the report. This goes up on the
> notice board every month and was easy to do when i had my jobs set as field
> names but now i'm not sure.
>
> Also would i be right in saying that the EmployeeID field in the Employees
> table is linked to the EmployeeID field in the EmployeeJobs table and the
> JobID field in the EmployeeJobs table is linked to the JobID field in the
> Jobs table ?
>
> Thanks for your help,
> Jen
>

>>>>>> Jen
>

[Jen32 via AccessMonster.com]

Alright i've got a Crosstab query going which looks promising......the only
thing i can't get it to do is to restrict the results to one department. Say
there's 3 depts A,B and C...how would i arrange it so that only the people
and jobs from Dept A are shown ?

Jen

John Spencer wrote:
>Ok. To generate that report (or query) you will need to use a crosstab query.
>
>Yes, those are the correct relationships.
>
>When you are ready to build the crosstab query, I would FIRST build a simple
>select query that showed all the data you wanted. Once you have that built,
>you can use the crosstab query wizard to build the basic crosstab query using
>the select query as the source. After you have that built, you can post the
>SQL of the crosstab query (and the select query it is based on) and ask for
>help if there are things you want to do to tweak the query results.
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>

>> Ok i've restructured the tables and taken your suggestion regarding the
>> subforms and it seems to be working quite well. One thing i need to be able

>[quoted text clipped - 11 lines]

>> Thanks for your help,
>> Jen
>
>>>>>>> Jen

--

[John Spencer]

Would you care to post the query you have?

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

Select Query SQL

SELECT tbl_Jobs.Job, tbl_Employees.EmployeeName, tbl_TrainingRecs.Training,
tbl_Employees.EmployeeID, tbl_Employees.Manager, tbl_Employees.DateStarted,
tbl_Employees.CurrentDept
FROM (tbl_Employees INNER JOIN tbl_TrainingRecs ON tbl_Employees.EmployeeID =
tbl_TrainingRecs.EmployeeID) INNER JOIN tbl_Jobs ON tbl_TrainingRecs.JobID =
tbl_Jobs.JobID;

Crosstab Query SQL

TRANSFORM Sum(Test.Training) AS SumOfTraining
SELECT Test.EmployeeName
FROM Test
GROUP BY Test.EmployeeName
PIVOT Test.Job;

I should mention that the table called EmployeeJobs is called TrainingRecs
in my database.

Also can i ask how to make the value appear on the report as it is entered in
the database....i mean each job has a value of 1,2 or 3 but the crosstab
query is making me display the numbers as an Avg or Count or Sum etc. Unless
i'm missing something obvious

Jen

Jen32 wrote:
>Alright i've got a Crosstab query going which looks promising......the only
>thing i can't get it to do is to restrict the results to one department. Say
>there's 3 depts A,B and C...how would i arrange it so that only the people
>and jobs from Dept A are shown ?
>
>Jen
>

>>Ok. To generate that report (or query) you will need to use a crosstab query.
>>

>[quoted text clipped - 19 lines]
>>
>>>>>>>> Jen

--
Message posted via http://www.accessmonster.com

[John Spencer]

You can limit the department in the Select query or in the crosstab query.

You can use First to return the training level. This assumes that you have
only one record per combination of employee and job.

Select Query SQL

SELECT tbl_Jobs.Job, tbl_Employees.EmployeeName, tbl_TrainingRecs.Training,
tbl_Employees.EmployeeID, tbl_Employees.Manager, tbl_Employees.DateStarted,
tbl_Employees.CurrentDept
FROM (tbl_Employees INNER JOIN tbl_TrainingRecs ON tbl_Employees.EmployeeID =
tbl_TrainingRecs.EmployeeID) INNER JOIN tbl_Jobs ON tbl_TrainingRecs.JobID =
tbl_Jobs.JobID

WHERE tbl_Employees.CurrentDept = "A"

Crosstab Query SQL

TRANSFORM FIRST(Test.Training) AS TrainingLevel
SELECT Test.EmployeeName
FROM Test
WHERE CurrentDept = "A"

GROUP BY Test.EmployeeName
PIVOT Test.Job;

You could just use the following query
Parameters [Specify Department] Text(255) ;
TRANSFORM First(tbl_TrainingRecs.Training) as Level
SELECT tbl_Employees.EmployeeName
, tbl_Employees.EmployeeID

FROM (tbl_Employees INNER JOIN tbl_TrainingRecs
ON tbl_Employees.EmployeeID =tbl_TrainingRecs.EmployeeID)
INNER JOIN tbl_Jobs
ON tbl_TrainingRecs.JobID =tbl_Jobs.JobID

WHERE tbl_Employees.CurrentDept= [Specify Department]
GROUP BY tbl_Employees.EmployeeName, tbl_Employees.EmployeeID
PIVOT tbl_Jobs.Job

IF you want, you can create a form to input the the department and call the
crosstab or a report based on the crosstab.
Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm

Or check out this from MS
http://office.microsoft.com/en-us/access/HA011730581033.aspx

Or for another example
http://allenbrowne.com/ser-62.html

If you do decide to use the form method, you will need to be sure that you
replace [Specify Department] in the query with something like
[Forms]![Name of the Form]![Name of the control on the form]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

Cheers for all your help to date John... i think i'm almost there. There's
one strange thing happening at the moment. When i use the data entry form to
input the information, the EmployeeName, EmployeeID and CurrentDept are going
into the Employees table (that's fine). The Job and Training record are going
into the TrainingRecs table (which is also fine) but then the Select Query is
not picking up ANYTHING i enter into those tables using the data entry form
although the records are physically in those tables. I'm pretty sure i have
the query set up correctly with all those tables added and linked properly,
but nothing happening. The SQL of the query as it stands is below

SELECT tbl_Employees.EmployeeName, tbl_Employees.CurrentDept, tbl_Employees.
Manager, tbl_Employees.DateStarted, tbl_TrainingRecs.Training,
tbl_TrainingRecs.Job

FROM (tbl_Employees INNER JOIN tbl_TrainingRecs ON tbl_Employees.EmployeeID =
tbl_TrainingRecs.EmployeeID) INNER JOIN tbl_Jobs ON tbl_TrainingRecs.JobID =

tbl_Jobs.JobID;

Thanks for your patience,
Jen

>> Select Query SQL
>>
>[quoted text clipped - 34 lines]

>>> [quoted text clipped - 19 lines]
>>>>>>>>>> Jen

--

[John Spencer]

Let me understand this.

If you run the query (by itself) you are not getting any records returned.
True or False?

If you are not getting any records returned and no error message, then we need
to look at the table structure and make sure that the correct values and
correct value types are being entered.

If you are getting records returned when you run the query, then we need to
look at where you are using the query. One thing to check is the form's
properties - the Data Entry property should be set to NO. If it is set to YES
then the form will not show existing records when it is opened. It will only
show new records added while the form remains open.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

Yes there are some records returned but these are records which were in the
tables from 2 or 3 days ago. If i add new records now, using the data entry
form created yesterday, they will go into the correct tables but will not be
picked up by the select query (the query at the moment is set to return all
records...no criteria are set yet)

The 3 tables used in the query are
Employees
Jobs
TrainingRecs

The fields added to the grid are

EmployeeName
EmployeeID
Manager
DateStarted
CurrentDept

(All above from the Employees table)

And also...

Training
Job

(Above fields from the TrainingRecs table)

The Data Entry property on the form is set to NO

Thanks,
Jen

John Spencer wrote:
>Let me understand this.
>
>If you run the query (by itself) you are not getting any records returned.
>True or False?
>
>If you are not getting any records returned and no error message, then we need
>to look at the table structure and make sure that the correct values and
>correct value types are being entered.
>
>If you are getting records returned when you run the query, then we need to
>look at where you are using the query. One thing to check is the form's
>properties - the Data Entry property should be set to NO. If it is set to YES
>then the form will not show existing records when it is opened. It will only
>show new records added while the form remains open.
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>

>> Cheers for all your help to date John... i think i'm almost there. There's
>> one strange thing happening at the moment. When i use the data entry form to

>[quoted text clipped - 14 lines]
>>
>> Thanks for your patience,

[John Spencer]

Ok, something is wrong here and it is probably data related, but I can't tell
what is happening. As a guess the values in the linking fields are not being
set correctly.

Can you compact the database and zip it? And can you send me a copy?

If so, my email is
j Spencer <AT> hilltop. EWE EM BEE SEE <Dot> EDU

To make that a valid address remove the spaces, change the the words to
letters or the appropriate characters.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[John Spencer]

Received the copy of the database and proposed the following changes.

Quick look.

Tbl_TrainingRecs.JobID should NOT be an autonumber field. It should be a
number field with field Size set to LONG and NO default value.

The combobox on TrainingRecs Subform2 should be bound to the JobID field and
should have
Row source: SELECT JobID, Job FROM tbl_Jobs ORDER BY Job
Column Count: 2
Column Widths: 0

The subform2 should have a record source of
SELECT tbl_TrainingRecs.EmployeeID, tbl_TrainingRecs.Training,
tbl_TrainingRecs.JobID
FROM tbl_TrainingRecs;

After fixing the data I’ve set up the relationships so that you cannot add a
trainingRec without having a corresponding record in both tbl_Jobs and
tbl_Employees. You also cannot delete a record from either of those tables if
there is a trainingRec using that value.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

Thanks again...it seems to be working now although i don't understand what
you did really. Almost finished now and hopefully there'll be no more
problems (some hope)

Cheers,
Jen

John Spencer wrote:
>Received the copy of the database and proposed the following changes.
>
>Quick look.
>
>Tbl_TrainingRecs.JobID should NOT be an autonumber field. It should be a
>number field with field Size set to LONG and NO default value.
>
>The combobox on TrainingRecs Subform2 should be bound to the JobID field and
>should have
>Row source: SELECT JobID, Job FROM tbl_Jobs ORDER BY Job
>Column Count: 2
>Column Widths: 0
>
>The subform2 should have a record source of
>SELECT tbl_TrainingRecs.EmployeeID, tbl_TrainingRecs.Training,
>tbl_TrainingRecs.JobID
>FROM tbl_TrainingRecs;
>
>After fixing the data I’ve set up the relationships so that you cannot add a
>trainingRec without having a corresponding record in both tbl_Jobs and
>tbl_Employees. You also cannot delete a record from either of those tables if
>there is a trainingRec using that value.
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>

>> Ok, something is wrong here and it is probably data related, but I can't
>> tell what is happening. As a guess the values in the linking fields are

>[quoted text clipped - 76 lines]

>>>> [quoted text clipped - 14 lines]
>>>>> Thanks for your patience,

--

[John Spencer]

If you want an explanation of each change, post back. I will be happy to
expand on the reasons.

Tbl_TrainingRecs.JobID should NOT be an autonumber field.

WHY:
An autonumber is a special case of a long integer. It is automatically
generated and is unique in the table - no duplicates allowed. JobID in
tbl_TrainingRecs is a foreign key field and references the autonumber field
JobID in the Jobs table. So tbl_TrainingRecs.JobID should duplicate the
number in the Jobs tables. That is the same JOBID number will occur multiple
times in tbl_TrainingRecs as you match the same job to numerous employees.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

No that's ok John...i've gone through it a few times and i understand now the
reasoning behind the changes. You'll be surprised to know that i've got one
more question though :)

I mentioned a few posts back that i wanted to create a report based on only
one section and that's now done using the Select and Crosstab queries......
but say a person works in Section A for a year and gets trained on 5 jobs
then moves to Section B and gets training on 2 more jobs from that section....
how can i still generate the report and keep that persons training in Section
B off it ?. In other words i just want information from Section A only, even
if some of the employees have training in Section B. I only want Section A
jobs to appear on the report.

I realise you are now probably contacting an assassin to come shoot me but i
promise this is the last question !

Thanks,
Jen

John Spencer wrote:
>If you want an explanation of each change, post back. I will be happy to
>expand on the reasons.
>
>Tbl_TrainingRecs.JobID should NOT be an autonumber field.
>WHY:
>An autonumber is a special case of a long integer. It is automatically
>generated and is unique in the table - no duplicates allowed. JobID in
>tbl_TrainingRecs is a foreign key field and references the autonumber field
>JobID in the Jobs table. So tbl_TrainingRecs.JobID should duplicate the
>number in the Jobs tables. That is the same JOBID number will occur multiple
>times in tbl_TrainingRecs as you match the same job to numerous employees.
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>

>> Thanks again...it seems to be working now although i don't understand what
>> you did really. Almost finished now and hopefully there'll be no more

>[quoted text clipped - 29 lines]

>>> The Hilltop Institute
>>> University of Maryland Baltimore County

--

[John Spencer]

To do that you are going to need to be able to distinguish the Section A jobs
from the Section B jobs OR you need to determine the distinguish the time
period when the employee works in Section A and then in Section B.

Both of those imply table changes.
You can add a field to tbl_Jobs that specifies which department the skill
applies to.

Tracking employees time period(s) in various departments involves adding
another table.
EmployeeInDepartment
EmployeeID
DepartmentName (or DepartmentID)
StartDate
EndDate

If you choose to do the former you can filter by the department in tbl_Jobs.
Based on the CurrentDept field in Employees table. However you would need to
have multiple records in tbl_Jobs for the same job if it appeared in multiple
departments. For instance, you might have
Oven A Block
Oven B Block
Oven C Block
(or you could add another table to store JobDepartments. The additional table
would further normalize the data but if you had little overlap in jobs it is
not needed. If you have considerable overlap then you might consider it.)

Going with the simplest solution, your qry_ASelMatrix becomes
SELECT tbl_Employees.EmployeeName, tbl_Employees.CurrentDept
, tbl_Employees.Manager
, tbl_Employees.DateStarted, tbl_TrainingRecs.Training
, tbl_TrainingRecs.Job
, tbl_Jobs.Dept
FROM tbl_Jobs INNER JOIN

(tbl_Employees INNER JOIN tbl_TrainingRecs
ON tbl_Employees.EmployeeID = tbl_TrainingRecs.EmployeeID)

ON (tbl_Employees.CurrentDept = tbl_Jobs.Dept)
AND (tbl_Jobs.JobID = tbl_TrainingRecs.JobID)

And your crosstab query might look like
TRANSFORM First(Qry_ASelMatrix.Training) AS FirstOfTraining
SELECT Qry_ASelMatrix.EmployeeName
FROM Qry_ASelMatrix
WHERE (((Qry_ASelMatrix.CurrentDept)="A Block"))
GROUP BY Qry_ASelMatrix.EmployeeName
PIVOT Qry_ASelMatrix.Job;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

Hi John,
Sorry i've been off for a couple of days sick :(
I've tried adding the extra field to tbl_Jobs and it worked perfectly for the
query.

Now though, when i add new records with the DataEntry form, everything goes
to the right table except the job the person is receiving the training on.
The table tbl_TrainingRecs saves the EmployeeID, JobID and Training....but
does not save the Job. Can't work out why ??. When you look at the table, all
the records are there except for gaps where the job should be.

Jen

>> No that's ok John...i've gone through it a few times and i understand now the
>> reasoning behind the changes. You'll be surprised to know that i've got one

>[quoted text clipped - 14 lines]

>> Thanks,
>> Jen

[John Spencer]

Because the JOB name (description) is stored in the tblJobs table. When you
need the description attached to the JobID you link to the tblJobs table and
pull the text description of the job from that table.

For instance, a simple query would look like this in the SQL view

SELECT tbl_Employees.EmployeeName, tbl_Jobs.Job, tbl_TrainingRecs.Training

FROM tbl_Jobs INNER JOIN
(tbl_Employees INNER JOIN
tbl_TrainingRecs ON tbl_Employees.EmployeeID = tbl_TrainingRecs.EmployeeID)

ON tbl_Jobs.JobID = tbl_TrainingRecs.JobID;

You can add that query to your queries by opening a new query, not selecting
any tables, and switching to SQL view. Copy the above and paste it into the
window. You can then switch back to query design view to see what it looks
like using that tool.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

Oh ok....so i've changed my existing query to take the Job field from
tbl_Jobs instead of tbl_TrainingRecs and it's working fine. Can i assume then
that there's no point in having a Job field in tbl_TrainingRecs at all ?....
and can i delete it ?

Jen

John Spencer wrote:
>Because the JOB name (description) is stored in the tblJobs table. When you
>need the description attached to the JobID you link to the tblJobs table and
>pull the text description of the job from that table.
>
>For instance, a simple query would look like this in the SQL view
>
>SELECT tbl_Employees.EmployeeName, tbl_Jobs.Job, tbl_TrainingRecs.Training
>FROM tbl_Jobs INNER JOIN
>(tbl_Employees INNER JOIN
>tbl_TrainingRecs ON tbl_Employees.EmployeeID = tbl_TrainingRecs.EmployeeID)
>ON tbl_Jobs.JobID = tbl_TrainingRecs.JobID;
>
>You can add that query to your queries by opening a new query, not selecting
>any tables, and switching to SQL view. Copy the above and paste it into the
>window. You can then switch back to query design view to see what it looks
>like using that tool.
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>

>> Hi John,
>> Sorry i've been off for a couple of days sick :(

>[quoted text clipped - 66 lines]
>>>> Thanks,
>>>> Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

[John Spencer]

Yes, there is no need to have that field (Jobs) in the tbl_TrainingRecs.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Jen32 via AccessMonster.com]

I think that's everything working now John....thank you very much for all
your help and especially your patience....i don't know how you guys correct
all these errors from thousands of miles away, although i'm certain i'll be
back in the near future to haunt ye all again !

Thanks again for everything
Jen xoxo

John Spencer wrote:
>Yes, there is no need to have that field (Jobs) in the tbl_TrainingRecs.
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>

>> Oh ok....so i've changed my existing query to take the Job field from
>> tbl_Jobs instead of tbl_TrainingRecs and it's working fine. Can i assume then

>[quoted text clipped - 30 lines]