Comparing the Results from two queries

[Guest]

I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list of
records from query 1 that do not appear in query two. The unmatched query
wizard doesn't work, because all fields in each query are to be matched, not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title, but what
training is actually taken can be different, I need an easy way to run a
query that will scroll through the list of all the training taken, compare it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training Modules with
three junction tables to link many to many i.e. each employee can take
multiple training modules, even the same modules across multiple years, and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how much
training, what type of training, but it will not deliver this one query.
I have constructed a form which does provide the information, but in a very
clumsy way, I have a form with 2 sub forms, the main being the employee
details and the sub form being 'training taken' and 'training required' - but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module, but that
is of limited help becuase in lots of cases, those people didn't actually
require that specific module, so it's not surprising that they didnt take it -

THis has got very complicated, but I"d very extremely grateful for any
assistance

Mel Morris

 

[Tom Ellison]

Dear Mel:

SELECT Q1.*
FROM Q1
LEFT JOIN Q2
ON Q2.employeeID = Q1.EmployeeID
AND Q2.[First Name] = Q1.[First Name]
AND Q2.[Last Name] = Q1.[Last Name]
AND Q2.Title = Q1.Title
AND Q2.[Training Modules] = Q1.[Traning Module]
WHERE Q2.employeeID IS NULL

In the above, you must substitute the actual names of your queries for Q1
and Q2. Also, check the exact spelling of all the columns.

I have assumed that employeeID in query 2 would never be NULL.

Tom Ellison

"Mel Morris (Global Egg Corporation)"

 

[John Spencer]

I would assume that EmployeeID is unique - therefore you don't need to match
on FirstName and LastName

SELECT Q1.*
FROM QueryOne as Q1 LEFT JOIN QueryTwo as Q2
ON Q1.employeID = Q2.employeID
AND Q1.Title = Q2.Title
AND Q1.[Training Modules] = Q2.[Training Module]
WHERE Q2.employeeID Is Null
AND Q2.Title is Null
AND Q2.[Training Module] is Null



"Mel Morris (Global Egg Corporation)"

 

[Guest]

Thanks for responding, I"ve pasted the formula into the SQL box thus:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison] LEFT JOIN [Training Completed By
Employee by Comparison] ON ([Training Completed By Employee by
Comparison].[employeeID]=[Name Title Module Req comparison].[EmployeeID]) AND
([Training Completed By Employee by Comparison].[FirstName]=[Name Title
Module Req comparison].[FirstName]) AND ([Training Completed By Employee by
Comparison].[LastName]=[Name Title Module Req comparison].[LastName]) AND
([Training Completed By Employee by Comparison].[Title]=[Name Title Module
Req comparison].[Title]) AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is Null;


ALhthough I've finally got the syntax acceptable to Access it's still not
working well, and everytime I try and run the query i diaglogue boxes which
is looking for user input which it shouldn't . I don't expect you to spend
much more time on this for me, but can you confirm that I have the syntax as
you had suggeste d- Iwas a bit confused because some of your AND statements
had square brackets around the field and some didn't and I wasn't sure if
that was intentional or not.

Regards
Mel Morris

Dear Mel:

SELECT Q1.*
FROM Q1
LEFT JOIN Q2
ON Q2.employeeID = Q1.EmployeeID
AND Q2.[First Name] = Q1.[First Name]
AND Q2.[Last Name] = Q1.[Last Name]
AND Q2.Title = Q1.Title
AND Q2.[Training Modules] = Q1.[Traning Module]
WHERE Q2.employeeID IS NULL

In the above, you must substitute the actual names of your queries for Q1
and Q2. Also, check the exact spelling of all the columns.

I have assumed that employeeID in query 2 would never be NULL.

Tom Ellison

"Mel Morris (Global Egg Corporation)"

I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list of
records from query 1 that do not appear in query two. The unmatched query
wizard doesn't work, because all fields in each query are to be matched,
not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title, but
what
training is actually taken can be different, I need an easy way to run a
query that will scroll through the list of all the training taken, compare
it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training Modules with
three junction tables to link many to many i.e. each employee can take
multiple training modules, even the same modules across multiple years,
and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how much
training, what type of training, but it will not deliver this one query.
I have constructed a form which does provide the information, but in a
very
clumsy way, I have a form with 2 sub forms, the main being the employee
details and the sub form being 'training taken' and 'training required' -
but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module, but
that
is of limited help becuase in lots of cases, those people didn't actually
require that specific module, so it's not surprising that they didnt take
it -

THis has got very complicated, but I"d very extremely grateful for any
assistance

Mel Morris

 

[Guest]

Thanks JOhn,
I pasted your formula to a query too and got a similar response, when I run
a query it is asking for parameters that I think should be automatic

Thanks a lot for spending time on this, i may just be doomed to doing it
manually. I really thought that it would be simple but that just shows how
little i know about programming - I'm fine with basic queries etc, but
anything out of the ordinary and i'm stumped.

Mel Morris

I would assume that EmployeeID is unique - therefore you don't need to match
on FirstName and LastName

SELECT Q1.*
FROM QueryOne as Q1 LEFT JOIN QueryTwo as Q2
ON Q1.employeID = Q2.employeID
AND Q1.Title = Q2.Title
AND Q1.[Training Modules] = Q2.[Training Module]
WHERE Q2.employeeID Is Null
AND Q2.Title is Null
AND Q2.[Training Module] is Null



"Mel Morris (Global Egg Corporation)"

I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list of
records from query 1 that do not appear in query two. The unmatched query
wizard doesn't work, because all fields in each query are to be matched,
not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title, but
what
training is actually taken can be different, I need an easy way to run a
query that will scroll through the list of all the training taken, compare
it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training Modules with
three junction tables to link many to many i.e. each employee can take
multiple training modules, even the same modules across multiple years,
and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how much
training, what type of training, but it will not deliver this one query.
I have constructed a form which does provide the information, but in a
very
clumsy way, I have a form with 2 sub forms, the main being the employee
details and the sub form being 'training taken' and 'training required' -
but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module, but
that
is of limited help becuase in lots of cases, those people didn't actually
require that specific module, so it's not surprising that they didnt take
it -

THis has got very complicated, but I"d very extremely grateful for any
assistance

Mel Morris

 

[John Spencer]

What is in queryOne and QueryTwo? Do they have parameters?
What Parameter is it asking for? Is it possible that you have misspelled
field names?


"Mel Morris (Global Egg Corporation)"

Thanks JOhn,
I pasted your formula to a query too and got a similar response, when I
run
a query it is asking for parameters that I think should be automatic

Thanks a lot for spending time on this, i may just be doomed to doing it
manually. I really thought that it would be simple but that just shows how
little i know about programming - I'm fine with basic queries etc, but
anything out of the ordinary and i'm stumped.

Mel Morris

I would assume that EmployeeID is unique - therefore you don't need to
match
on FirstName and LastName

SELECT Q1.*
FROM QueryOne as Q1 LEFT JOIN QueryTwo as Q2
ON Q1.employeID = Q2.employeID
AND Q1.Title = Q2.Title
AND Q1.[Training Modules] = Q2.[Training Module]
WHERE Q2.employeeID Is Null
AND Q2.Title is Null
AND Q2.[Training Module] is Null



"Mel Morris (Global Egg Corporation)"
message

I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list of
records from query 1 that do not appear in query two. The unmatched
query
wizard doesn't work, because all fields in each query are to be
matched,
not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title, but
what
training is actually taken can be different, I need an easy way to run
a
query that will scroll through the list of all the training taken,
compare
it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training Modules
with
three junction tables to link many to many i.e. each employee can take
multiple training modules, even the same modules across multiple years,
and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how much
training, what type of training, but it will not deliver this one
query.
I have constructed a form which does provide the information, but in a
very
clumsy way, I have a form with 2 sub forms, the main being the employee
details and the sub form being 'training taken' and 'training
required' -
but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module, but
that
is of limited help becuase in lots of cases, those people didn't
actually
require that specific module, so it's not surprising that they didnt
take
it -

THis has got very complicated, but I"d very extremely grateful for any
assistance

Mel Morris

 

[Guest]

Query 1 and 2 are just select queries,
query one is based on based on 4 linked tables all very simple and with one
parameter (field: Active = Yes) and sorted by Last Name

Query 2 is slightly more complex but not much, based on a table and a
query (this query being based on 3 tables) again
I don't believe I've misspelled the fields, and none of queries require any
data input (unlike others where they require specific dates for starting and
finishing the query)

I'm puzzled and it's Friday and I'm not sure I want to spend too much more
time on this tonight.

I really appreciate the responses though, because this is bugging the heck
out of me.
Mel Morris

What is in queryOne and QueryTwo? Do they have parameters?
What Parameter is it asking for? Is it possible that you have misspelled
field names?


"Mel Morris (Global Egg Corporation)"

Thanks JOhn,
I pasted your formula to a query too and got a similar response, when I
run
a query it is asking for parameters that I think should be automatic

Thanks a lot for spending time on this, i may just be doomed to doing it
manually. I really thought that it would be simple but that just shows how
little i know about programming - I'm fine with basic queries etc, but
anything out of the ordinary and i'm stumped.

Mel Morris

I would assume that EmployeeID is unique - therefore you don't need to
match
on FirstName and LastName

SELECT Q1.*
FROM QueryOne as Q1 LEFT JOIN QueryTwo as Q2
ON Q1.employeID = Q2.employeID
AND Q1.Title = Q2.Title
AND Q1.[Training Modules] = Q2.[Training Module]
WHERE Q2.employeeID Is Null
AND Q2.Title is Null
AND Q2.[Training Module] is Null



"Mel Morris (Global Egg Corporation)"
message
I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list of
records from query 1 that do not appear in query two. The unmatched
query
wizard doesn't work, because all fields in each query are to be
matched,
not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title, but
what
training is actually taken can be different, I need an easy way to run
a
query that will scroll through the list of all the training taken,
compare
it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training Modules
with
three junction tables to link many to many i.e. each employee can take
multiple training modules, even the same modules across multiple years,
and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how much
training, what type of training, but it will not deliver this one
query.
I have constructed a form which does provide the information, but in a
very
clumsy way, I have a form with 2 sub forms, the main being the employee
details and the sub form being 'training taken' and 'training
required' -
but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module, but
that
is of limited help becuase in lots of cases, those people didn't
actually
require that specific module, so it's not surprising that they didnt
take
it -

THis has got very complicated, but I"d very extremely grateful for any
assistance

Mel Morris

 

[Tom Ellison]

Dear Mel:

The square bracket thing was intentional and deliberately and carefully
done. If you put a space inside a column or table name, then you must use
the square brackets. Overall, it's just an unnecessary nuisance (in my
opinion). You could call a column FirstName instead of [First Name] for
example, as I see you have done in what you posted. I put the space in
because that's what is in your original post, and this forced me to use the
square brackets.

The new query you posted I edited to look like this:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison]
LEFT JOIN [Training Completed By Employee by Comparison]
ON ([Training Completed By Employee by Comparison].[employeeID] =
[Name Title Module Req comparison].[EmployeeID])
AND ([Training Completed By Employee by Comparison].[FirstName]=[Name
Title Module Req comparison].[FirstName])
AND ([Training Completed By Employee by Comparison].[LastName]=[Name
Title Module Req comparison].[LastName])
AND ([Training Completed By Employee by Comparison].[Title]=[Name
Title Module Req comparison].[Title])
AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is Null;

That does seem much like what I posted. The dialog box will spell out the
name of some column the query couldn't find. Check the spelling of the
table and column names carefully. The query was unable to find this. It
should be pretty easy to remedy.

Tom Ellison


"Mel Morris (Global Egg Corporation)"

Thanks for responding, I"ve pasted the formula into the SQL box thus:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison] LEFT JOIN [Training Completed By
Employee by Comparison] ON ([Training Completed By Employee by
Comparison].[employeeID]=[Name Title Module Req comparison].[EmployeeID])
AND
([Training Completed By Employee by Comparison].[FirstName]=[Name Title
Module Req comparison].[FirstName]) AND ([Training Completed By Employee
by
Comparison].[LastName]=[Name Title Module Req comparison].[LastName]) AND
([Training Completed By Employee by Comparison].[Title]=[Name Title Module
Req comparison].[Title]) AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is Null;


ALhthough I've finally got the syntax acceptable to Access it's still
not
working well, and everytime I try and run the query i diaglogue boxes
which
is looking for user input which it shouldn't . I don't expect you to spend
much more time on this for me, but can you confirm that I have the syntax
as
you had suggeste d- Iwas a bit confused because some of your AND
statements
had square brackets around the field and some didn't and I wasn't sure if
that was intentional or not.

Regards
Mel Morris

Dear Mel:

SELECT Q1.*
FROM Q1
LEFT JOIN Q2
ON Q2.employeeID = Q1.EmployeeID
AND Q2.[First Name] = Q1.[First Name]
AND Q2.[Last Name] = Q1.[Last Name]
AND Q2.Title = Q1.Title
AND Q2.[Training Modules] = Q1.[Traning Module]
WHERE Q2.employeeID IS NULL

In the above, you must substitute the actual names of your queries for Q1
and Q2. Also, check the exact spelling of all the columns.

I have assumed that employeeID in query 2 would never be NULL.

Tom Ellison

"Mel Morris (Global Egg Corporation)"
message

I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list of
records from query 1 that do not appear in query two. The unmatched
query
wizard doesn't work, because all fields in each query are to be
matched,
not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title, but
what
training is actually taken can be different, I need an easy way to run
a
query that will scroll through the list of all the training taken,
compare
it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training Modules
with
three junction tables to link many to many i.e. each employee can take
multiple training modules, even the same modules across multiple years,
and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how much
training, what type of training, but it will not deliver this one
query.
I have constructed a form which does provide the information, but in a
very
clumsy way, I have a form with 2 sub forms, the main being the employee
details and the sub form being 'training taken' and 'training
required' -
but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module, but
that
is of limited help becuase in lots of cases, those people didn't
actually
require that specific module, so it's not surprising that they didnt
take
it -

THis has got very complicated, but I"d very extremely grateful for any
assistance

Mel Morris

 

[Guest]

Dear Tom,

Yes, I remembered after I posted that the square brackets were to counteract
gaps in fields, and you are right, I don't have any gaps in my fields - you
were quite correct I had incorrect names for one field.

Thank you so much, the query seems to work very well.
I really appreciate all the work you and John Spencer have put into this.
It's been bugging me for months.

regards
Mel Morris

Dear Mel:

The square bracket thing was intentional and deliberately and carefully
done. If you put a space inside a column or table name, then you must use
the square brackets. Overall, it's just an unnecessary nuisance (in my
opinion). You could call a column FirstName instead of [First Name] for
example, as I see you have done in what you posted. I put the space in
because that's what is in your original post, and this forced me to use the
square brackets.

The new query you posted I edited to look like this:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison]
LEFT JOIN [Training Completed By Employee by Comparison]
ON ([Training Completed By Employee by Comparison].[employeeID] =
[Name Title Module Req comparison].[EmployeeID])
AND ([Training Completed By Employee by Comparison].[FirstName]=[Name
Title Module Req comparison].[FirstName])
AND ([Training Completed By Employee by Comparison].[LastName]=[Name
Title Module Req comparison].[LastName])
AND ([Training Completed By Employee by Comparison].[Title]=[Name
Title Module Req comparison].[Title])
AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is Null;

That does seem much like what I posted. The dialog box will spell out the
name of some column the query couldn't find. Check the spelling of the
table and column names carefully. The query was unable to find this. It
should be pretty easy to remedy.

Tom Ellison


"Mel Morris (Global Egg Corporation)"

Thanks for responding, I"ve pasted the formula into the SQL box thus:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison] LEFT JOIN [Training Completed By
Employee by Comparison] ON ([Training Completed By Employee by
Comparison].[employeeID]=[Name Title Module Req comparison].[EmployeeID])
AND
([Training Completed By Employee by Comparison].[FirstName]=[Name Title
Module Req comparison].[FirstName]) AND ([Training Completed By Employee
by
Comparison].[LastName]=[Name Title Module Req comparison].[LastName]) AND
([Training Completed By Employee by Comparison].[Title]=[Name Title Module
Req comparison].[Title]) AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is Null;


ALhthough I've finally got the syntax acceptable to Access it's still
not
working well, and everytime I try and run the query i diaglogue boxes
which
is looking for user input which it shouldn't . I don't expect you to spend
much more time on this for me, but can you confirm that I have the syntax
as
you had suggeste d- Iwas a bit confused because some of your AND
statements
had square brackets around the field and some didn't and I wasn't sure if
that was intentional or not.

Regards
Mel Morris

Dear Mel:

SELECT Q1.*
FROM Q1
LEFT JOIN Q2
ON Q2.employeeID = Q1.EmployeeID
AND Q2.[First Name] = Q1.[First Name]
AND Q2.[Last Name] = Q1.[Last Name]
AND Q2.Title = Q1.Title
AND Q2.[Training Modules] = Q1.[Traning Module]
WHERE Q2.employeeID IS NULL

In the above, you must substitute the actual names of your queries for Q1
and Q2. Also, check the exact spelling of all the columns.

I have assumed that employeeID in query 2 would never be NULL.

Tom Ellison

"Mel Morris (Global Egg Corporation)"
message
I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list of
records from query 1 that do not appear in query two. The unmatched
query
wizard doesn't work, because all fields in each query are to be
matched,
not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title, but
what
training is actually taken can be different, I need an easy way to run
a
query that will scroll through the list of all the training taken,
compare
it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training Modules
with
three junction tables to link many to many i.e. each employee can take
multiple training modules, even the same modules across multiple years,
and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how much
training, what type of training, but it will not deliver this one
query.
I have constructed a form which does provide the information, but in a
very
clumsy way, I have a form with 2 sub forms, the main being the employee
details and the sub form being 'training taken' and 'training
required' -
but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module, but
that
is of limited help becuase in lots of cases, those people didn't
actually
require that specific module, so it's not surprising that they didnt
take
it -

THis has got very complicated, but I"d very extremely grateful for any
assistance

Mel Morris

 

[Tom Ellison]

Dear Mel:

Well, John is a good friend of mine, and it's very pleasant to collaborate
with him in this newsgroup. I've only met him on, well, is it 3 occasions:
once in NY and twice in Seattle? Memory does not serve me that well.

Tom Ellison


"Mel Morris (Global Egg Corporation)"

Dear Tom,

Yes, I remembered after I posted that the square brackets were to
counteract
gaps in fields, and you are right, I don't have any gaps in my fields -
you
were quite correct I had incorrect names for one field.

Thank you so much, the query seems to work very well.
I really appreciate all the work you and John Spencer have put into this.
It's been bugging me for months.

regards
Mel Morris

Dear Mel:

The square bracket thing was intentional and deliberately and carefully
done. If you put a space inside a column or table name, then you must
use
the square brackets. Overall, it's just an unnecessary nuisance (in my
opinion). You could call a column FirstName instead of [First Name] for
example, as I see you have done in what you posted. I put the space in
because that's what is in your original post, and this forced me to use
the
square brackets.

The new query you posted I edited to look like this:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison]
LEFT JOIN [Training Completed By Employee by Comparison]
ON ([Training Completed By Employee by Comparison].[employeeID] =
[Name Title Module Req comparison].[EmployeeID])
AND ([Training Completed By Employee by
Comparison].[FirstName]=[Name
Title Module Req comparison].[FirstName])
AND ([Training Completed By Employee by
Comparison].[LastName]=[Name
Title Module Req comparison].[LastName])
AND ([Training Completed By Employee by Comparison].[Title]=[Name
Title Module Req comparison].[Title])
AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is
Null;

That does seem much like what I posted. The dialog box will spell out
the
name of some column the query couldn't find. Check the spelling of the
table and column names carefully. The query was unable to find this. It
should be pretty easy to remedy.

Tom Ellison


"Mel Morris (Global Egg Corporation)"
message

Thanks for responding, I"ve pasted the formula into the SQL box thus:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison] LEFT JOIN [Training Completed
By
Employee by Comparison] ON ([Training Completed By Employee by
Comparison].[employeeID]=[Name Title Module Req
comparison].[EmployeeID])
AND
([Training Completed By Employee by Comparison].[FirstName]=[Name Title
Module Req comparison].[FirstName]) AND ([Training Completed By
Employee
by
Comparison].[LastName]=[Name Title Module Req comparison].[LastName])
AND
([Training Completed By Employee by Comparison].[Title]=[Name Title
Module
Req comparison].[Title]) AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is
Null;


ALhthough I've finally got the syntax acceptable to Access it's
still
not
working well, and everytime I try and run the query i diaglogue boxes
which
is looking for user input which it shouldn't . I don't expect you to
spend
much more time on this for me, but can you confirm that I have the
syntax
as
you had suggeste d- Iwas a bit confused because some of your AND
statements
had square brackets around the field and some didn't and I wasn't sure
if
that was intentional or not.

Regards
Mel Morris

:

Dear Mel:

SELECT Q1.*
FROM Q1
LEFT JOIN Q2
ON Q2.employeeID = Q1.EmployeeID
AND Q2.[First Name] = Q1.[First Name]
AND Q2.[Last Name] = Q1.[Last Name]
AND Q2.Title = Q1.Title
AND Q2.[Training Modules] = Q1.[Traning Module]
WHERE Q2.employeeID IS NULL

In the above, you must substitute the actual names of your queries for
Q1
and Q2. Also, check the exact spelling of all the columns.

I have assumed that employeeID in query 2 would never be NULL.

Tom Ellison

"Mel Morris (Global Egg Corporation)"
message
I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list
of
records from query 1 that do not appear in query two. The unmatched
query
wizard doesn't work, because all fields in each query are to be
matched,
not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title,
but
what
training is actually taken can be different, I need an easy way to
run
a
query that will scroll through the list of all the training taken,
compare
it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training
Modules
with
three junction tables to link many to many i.e. each employee can
take
multiple training modules, even the same modules across multiple
years,
and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how
much
training, what type of training, but it will not deliver this one
query.
I have constructed a form which does provide the information, but in
a
very
clumsy way, I have a form with 2 sub forms, the main being the
employee
details and the sub form being 'training taken' and 'training
required' -
but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module,
but
that
is of limited help becuase in lots of cases, those people didn't
actually
require that specific module, so it's not surprising that they didnt
take
it -

THis has got very complicated, but I"d very extremely grateful for
any
assistance

Mel Morris

 

[Guest]

Well, I'm sure that the wider Access users' community appreciates all the
great advice, the problem is a lot of us only use the program sporadically
(from a programming perspective anyway) and if you are not a true programmer,
once it gets out of the basics, it can get very frustrating, - all the
manuals are very very basic and it's great to have access to this wonderful
resource

Thanks again to you both

Dear Mel:

Well, John is a good friend of mine, and it's very pleasant to collaborate
with him in this newsgroup. I've only met him on, well, is it 3 occasions:
once in NY and twice in Seattle? Memory does not serve me that well.

Tom Ellison


"Mel Morris (Global Egg Corporation)"

Dear Tom,

Yes, I remembered after I posted that the square brackets were to
counteract
gaps in fields, and you are right, I don't have any gaps in my fields -
you
were quite correct I had incorrect names for one field.

Thank you so much, the query seems to work very well.
I really appreciate all the work you and John Spencer have put into this.
It's been bugging me for months.

regards
Mel Morris

Dear Mel:

The square bracket thing was intentional and deliberately and carefully
done. If you put a space inside a column or table name, then you must
use
the square brackets. Overall, it's just an unnecessary nuisance (in my
opinion). You could call a column FirstName instead of [First Name] for
example, as I see you have done in what you posted. I put the space in
because that's what is in your original post, and this forced me to use
the
square brackets.

The new query you posted I edited to look like this:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison]
LEFT JOIN [Training Completed By Employee by Comparison]
ON ([Training Completed By Employee by Comparison].[employeeID] =
[Name Title Module Req comparison].[EmployeeID])
AND ([Training Completed By Employee by
Comparison].[FirstName]=[Name
Title Module Req comparison].[FirstName])
AND ([Training Completed By Employee by
Comparison].[LastName]=[Name
Title Module Req comparison].[LastName])
AND ([Training Completed By Employee by Comparison].[Title]=[Name
Title Module Req comparison].[Title])
AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is
Null;

That does seem much like what I posted. The dialog box will spell out
the
name of some column the query couldn't find. Check the spelling of the
table and column names carefully. The query was unable to find this. It
should be pretty easy to remedy.

Tom Ellison


"Mel Morris (Global Egg Corporation)"
message
Thanks for responding, I"ve pasted the formula into the SQL box thus:

SELECT [Name Title Module Req comparison].*
FROM [Name Title Module Req comparison] LEFT JOIN [Training Completed
By
Employee by Comparison] ON ([Training Completed By Employee by
Comparison].[employeeID]=[Name Title Module Req
comparison].[EmployeeID])
AND
([Training Completed By Employee by Comparison].[FirstName]=[Name Title
Module Req comparison].[FirstName]) AND ([Training Completed By
Employee
by
Comparison].[LastName]=[Name Title Module Req comparison].[LastName])
AND
([Training Completed By Employee by Comparison].[Title]=[Name Title
Module
Req comparison].[Title]) AND ([Training Completed By Employee by
Comparison].[TrainingModule]=[Name Title Module Req
comparison].[TraningModule])
WHERE [Training Completed By Employee by Comparison].[employeeID] Is
Null;


ALhthough I've finally got the syntax acceptable to Access it's
still
not
working well, and everytime I try and run the query i diaglogue boxes
which
is looking for user input which it shouldn't . I don't expect you to
spend
much more time on this for me, but can you confirm that I have the
syntax
as
you had suggeste d- Iwas a bit confused because some of your AND
statements
had square brackets around the field and some didn't and I wasn't sure
if
that was intentional or not.

Regards
Mel Morris

:

Dear Mel:

SELECT Q1.*
FROM Q1
LEFT JOIN Q2
ON Q2.employeeID = Q1.EmployeeID
AND Q2.[First Name] = Q1.[First Name]
AND Q2.[Last Name] = Q1.[Last Name]
AND Q2.Title = Q1.Title
AND Q2.[Training Modules] = Q1.[Traning Module]
WHERE Q2.employeeID IS NULL

In the above, you must substitute the actual names of your queries for
Q1
and Q2. Also, check the exact spelling of all the columns.

I have assumed that employeeID in query 2 would never be NULL.

Tom Ellison

"Mel Morris (Global Egg Corporation)"
message
I've not had much luck getting help with this, so am trying again.
I need to compare the results from two queries, and produce a list
of
records from query 1 that do not appear in query two. The unmatched
query
wizard doesn't work, because all fields in each query are to be
matched,
not
just one.

eg.
query one returns all records showing
1) - Training required by Employee
employeID; First Name;Last Name; Title;Training Modules,

2) - Training completed by Employee
employeID; First Name;Last Name; Title;Training Module

What training is required for each employee is determined by title,
but
what
training is actually taken can be different, I need an easy way to
run
a
query that will scroll through the list of all the training taken,
compare
it
to the list of all that's needed and show me the missing training

The database is 3 main tables (employees, positions, Training
Modules
with
three junction tables to link many to many i.e. each employee can
take
multiple training modules, even the same modules across multiple
years,
and
of course each module can be taken many times, by many employees.
It seems to work well for me for most other queries i want - how
much
training, what type of training, but it will not deliver this one
query.
I have constructed a form which does provide the information, but in
a
very
clumsy way, I have a form with 2 sub forms, the main being the
employee
details and the sub form being 'training taken' and 'training
required' -
but
i have to manually scan each page to work out whose missed what.
I can get easy queries to tell me who has actually missed a module,
but
that
is of limited help becuase in lots of cases, those people didn't
actually
require that specific module, so it's not surprising that they didnt
take
it -

THis has got very complicated, but I"d very extremely grateful for
any
assistance

Mel Morris