Report based on query based on many tables

[sword856]

Hello all. I've tried for a while to get this to work, but I haven't
been able to find a solution yet:

These are my tables: Classes, Jobs, Personnel, Crew, TrainingHistory.
Classes is a list of classes offered, ClassName being the primary
key. Jobs contains JobOrderNumber(PK), StartDate, and EndDate.
Personnel contains Name(PK), and Department. Crew is the junction
table between Jobs and Personnel and has Name, Position, and
JobOrderNumber, where Name and JON form the PK. TrainingHistory is
the junction table between Classes and Personnel and contains Name,
ClassName, and DateTaken, where Name and ClassName form the PK. What
I need is a way to display a report that prompts the user for a JON,
and returns every member of the crew for that JON, and for each crew
member returns all the classes they have taken. Not every crew member
on every job will have a class history, and therefore will not be in
the TrainingHistory table. I can construct a report that returns all
the names and class history of all the people in the database, and I
can construct a query that returns all the people who have received
training and their class history for a given JON. I need to be able
to display (in a report) ALL the members of the crew for a given JON,
even those who have no Training History. This is important so the
lead crew member can identify who on his crew does not have training
and can schedule it before they go out on a job.
I'm not sure if it's a simple query design that I'm just missing, or
if my table/relationship construction is not ideal, or if it's
something too complicated for me to do at all. It seems like it
should be easy to do, but I just can't see it. I am new at Access, so
I'm not familiar with VBA outside (I know how to use it a bit for
forms) and I've gotten somewhat of a grasp on SQL, so I think. If you
can help, your help is greatly appreciated.

 

[Jerry Whittle]

You have INNER JOINs between the tables. If one side does not have a matching
record with the other, data from neither side will show.

You need to do a Left or Right Join. In the Query Design View double click
on the black line(s) between the tables/queries, such as TrainingHistory, and
select one of the other options in the dialog box.

You may run into a couple of problems.

(1) If you have multiple joins betweent the two record sets, you might have
to play with the combinations of joins to get the desired results.

(2) It's possible that you might need both Right and Left joins to get
records from both sides in one query. Access does not support this type of
Outer join. If you run into this problem, it's most likely that your database
is not properly normalized to support your needs.

 

[sword856]

You have INNER JOINs between the tables. If one side does not have a matching
record with the other, data from neither side will show.

You need to do a Left or Right Join. In the Query Design View double click
on the black line(s) between the tables/queries, such as TrainingHistory, and
select one of the other options in the dialog box.

You may run into a couple of problems.

(1) If you have multiple joins betweent the two record sets, you might have
to play with the combinations of joins to get the desired results.

(2) It's possible that you might need both Right and Left joins to get
records from both sides in one query. Access does not support this type of
Outer join. If you run into this problem, it's most likely that your database
is not properly normalized to support your needs.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.






- Show quoted text -

-Thanks for reading my post, Jerry.

If I try to change any of the join types from the way I have them now,
the query will not run. I'm not sure how to properly normalize my
database. I can't think of a different way to set up the tables.

This might be a better way to describe what I need:
I run a query that asks for a JON. Access then takes the names of the
crew members that show up, and queries them in TrainingHistory,
showing their classes. This should all go on a report that displays
the results of both queries, with the second query results going
beneath each respective name from the first query.

 

[sword856]

-Thanks for reading my post, Jerry.

If I try to change any of the join types from the way I have them now,
the query will not run. I'm not sure how to properly normalize my
database. I can't think of a different way to set up the tables.

This might be a better way to describe what I need:
I run a query that asks for a JON. Access then takes the names of the
crew members that show up, and queries them in TrainingHistory,
showing their classes. This should all go on a report that displays
the results of both queries, with the second query results going
beneath each respective name from the first query.- Hide quoted text -

- Show quoted text -

Ok, got it. I had to use an exiting query (one that didnt return
everything) as part of a new query, linking it to my Personnel table
and creating a right join. That's where I was making my mistake
because that was the correct place to create that right join. Works
great now, thanks for heading me in the right direction!