Multiple Table Search

[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

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

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

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,

 

[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

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

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]

 

[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

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

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

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

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

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

 

[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

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]

 

[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

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

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

 

[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

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]