I have 4 Access production tables each with two production indicators
and each with an employeeID and a WeekOfYear (eg 2008-09) field. I
want to join them in a query such that I get one line for each
employeeID - WeekOfYear combination and have the 8 production
indicators all in the same record. The problem is that each employee
does not work in each production area every week and so some
employeeID - WeekOfYear combinations exist in some tables and not in
others. The typical joins then always seem to leave some data behind.

I want to end up with something like:

EmployeeID WeekOfYear PI1 PP2 PI3 PI4
a432 2008-11 17
21 87

The blank is ok.

Any ideas on getting them all to come into the query.

Thanks
Kevin

On Jan 29, 8:44*am, KC-Mass <connear... (AT) comcast (DOT) net> wrote:
If you have a weekOfYear table, you can build a cross-product query
select employeeId, weekOfYear from tblEmployee,tblWeekOfYear

to give you all combinations that you can use with other queries

Greetings,

If you could show some samples of your source data - the data the works
as you desire and some samples of the data which doesn't work as desired
(at least a few records - a few fields) -- then show a sample of what
you want the result set to look like to include the data which works and
the data which does not work (right now) (show at least a few records) -
it would be easier to provide some suggestions what you could do to make
it happen like that.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

On Jan 29, 11:04*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Roger

Does that WeekOfYear table need only the weekOfYear field?

Thanks Kevin

Hi Rich
Thanks for your time. Here's sample data
If I join on Give me everything from Tbl1 and all matching from Tbl2
I will lose Rec2 of Tbl2. If I join on give me everything in Tbl2 and
matches from Tbl1, I will lose Rec2 and Rec3 of Tbl1.
If I join on give me everything that matches in both, I will lose
Rec2 and Rec3 of Tbl1 and Rec2 of Tbl2.


Tbl1
Rec1 - ca112, 2008-10, 14,43
Rec2 - ca112, 2008-11, 23,56
Rec3 - cb321, 2008-14, 32,54

Tbl2
Rec1 - ca112, 2008-10, 7, 2
Rec2 - cb321, 2008-13, 12,32

Hope this helps



On Jan 29, 11:08*am, Rich P <rpng... (AT) aol (DOT) com> wrote:

Hi Kevin,

Try something like this:

Select t1.*, t2.* from Tbl1 t1 left Join Tbl2 t2 on t1.JoinField =
t2.JoinField

This will return all records from Tbl1 and only the records which match
in Tbl2 by using the "Left Join" operation If you switch the tables
around then you would get all recs from Tbl2 and only the recs from Tbl1
which match.

I wasn't clear exactly what data you wanted to retrieve. If you want
everything from Tbl1 and Tbl2 you can use a union query.

Rich

*** Sent via Developersdex http://www.developersdex.com ***