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]

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

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

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?

 

[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

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

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?

--



.

 

[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

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

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

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?

--



.

 

[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

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

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]

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!

 

[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

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

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

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]

Thanks,
Jen

 

[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

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

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

[quoted text clipped - 24 lines]

 

[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]

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.

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

 

[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

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]

 

[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

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

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

 

[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

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

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

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

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,