Missing Information in Query - HELP

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am new to access have had a little training but just can't figure
out what I'm doing wrong here.

My database is built of off basically two tables, one an "Employee
Information" table and the other an "Education/Course Table" I then
have a thrid table that is a junction table. This third table (Class
Input Table) basically tracks which employees attended what
educational offerings.

I have built a query to give me a list of employees who are CNAs and
what classes they have taken, and pulling in some additional
information from the two parent tables for a report.

My report is mostly correct, but, for a couple of employees, It is
pulling 1 out of 5 records from the Class imput, I mean to say the
table is showing that Employee A has attended 5 classes, but the query
is only listing one. Has this happened to anyone else? How do I get
all of the query to list everything? I have copied and pasted query
SQL below. Thanks in advance.

SQL:

SELECT [All Employee Data].[Last Name], [All Employee Data].[First
Name], [All Employee Data].[Job Class], [Education/Courses].Presenter,
[Education/Courses].[60 minute Clock hours], [All Employee Data].
[Staff ID], [Class Input Table].Topic, [All Employee Data].Location
FROM [Education/Courses] INNER JOIN ([All Employee Data] INNER JOIN
[Class Input Table] ON [All Employee Data].[Full Name] = [Class Input
Table].[Full Name]) ON [Education/Courses].Topic = [Class Input
Table].Topic
WHERE ((([All Employee Data].[Job Class])="CNA"))
ORDER BY [All Employee Data].[Last Name], [All Employee Data].[First
Name];
 
Steve

An INNER JOIN tells Access to find all of the records when BOTH tables have
matching values.

Is there a chance that you want to see all of the qualifying records in ONE
table, and ANY matching records in the other?

If so, use a LEFT (or RIGHT) JOIN. These are easier to visualize if you use
the query design window, not the SQL window.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Thanks Joyce, all of the records I want to see are on the same table,
they are just not being pulled into the query. And I'm not sure why, I
did create the query in design view and there are joins between all
three tables listed in the query. (The reason I posted the SQL was
that I saw in a previous posting a request to see that as it might be
helpful in determining the issue.)

The issue is that on the Class Input table CNA1 has 5 records but the
query is only pulling 1 or 2. I'm not sure what would cause this to
happen with some people and not others within the same query.
 
Your SQL statement where clause states:
[Job Class]="CNA"
but your most recent post states:
CNA1
These aren't the same and won't match.

I expect there may be an issue with the full names joining correctly.
Typically names are divided into first and last name fields. I would check
the values in the fields for the records that are missing.
 
Thanks Joyce and Duane, I have found the problem. Basically the tables
are not rationalized or in Normal Form. (It was bad before I started
in this position I promise.) Thanks for the help!
 
Your SQL statement mentions more than one table, and uses JOINS. That's why
my response pointed to the JOINS.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top