Duplicates in query

[stephanie]

I have 1 query called "inbound performance" that gives me the number of hours
spent by associate on unloading per day and 1 query called 'outbound
performance" that gives me the number of hours spent by associate on loading
per day.
I would like to make a query called "total performance" that would give me
the number of hours spent on unloading +loading by associate by day. When I
do the query, and link the user ID field, it gives me only where the
associate did both unloading and loading (where the 2 fields are equal), if I
choose option 2 it will give me all of the associates doing only inbound and
if I choose option 3, only the associates doing outbound performance. SO I am
sure it is pretty easy and I hope I am explaining it correctly--
Thanks
stephanie

 

[Michel Walsh]

Hi,


Make a query that supply all possible days:


SELECT associates FROM unloading
UNION
SELECT associates FROM loading


Saved it as qu1.


Next:


SELECT qu1.associates, Nz(unloading.performance, 0) +
Nz(loading.performance, 0)
FROM (qu1 LEFT JOIN loading ON qu1.associates = loading.associates)
LEFT JOIN unloading ON qu1.associates = unloading.associates


should do.



Hoping it may help,
Vanderghast, Access MVP

 

[stephanie]

First thank you for your help.
I built the 2 queries but now the only problem is that it disregards the
date information, for example:

Query 1 (unloading)
Date User ID Unloading
8/28 X 3.5
8/29 X 2

Query 2 (loading)
Date User ID Loading
8/29 X 11

The end query does the following
User ID Total
X 14.5
X 13

What I would like is
Date User ID Total
8/28 X 3.5
8/29 X 13

below is the query used
SELECT qu1.[USER ID], Nz([qry_Associates Inbound Productivity by
Day].[SumofTotal Time],0)+Nz([4 Total Productivity w %].[Total Prod hours],0)
AS Expr1
FROM (qu1 LEFT JOIN [4 Total Productivity w %] ON qu1.[USER ID] = [4 Total
Productivity w %].[USER ID]) LEFT JOIN [qry_Associates Inbound Productivity
by Day] ON qu1.[USER ID] = [qry_Associates Inbound Productivity by Day].[User
ID];

 

[Michel Walsh]

Hi,

Include the required field, date, in qu1:

SELECT [date], [user id] FROM unloading
UNION
SELECT [date], [user id] FROM loading


and then, SELECT it and also use it in the join (in the ON clause)


SELECT qu1.[date],
qu1.[USER ID],
Nz( a.[SumofTotal Time],0 )+Nz( b.[Total Prod hours],0) AS Expr1

FROM (qu1 LEFT JOIN [4 Total Productivity w %] As a
ON qu1.[USER ID] = a.[USER ID] AND qu1.[date]=a.[date])
LEFT JOIN [qry_Associates Inbound Productivity by Day] as b
ON qu1.[USER ID] =b.[User AND qu1.[date]=b.[date]




Hoping it may help,
Vanderghast, Access MVP

First thank you for your help.
I built the 2 queries but now the only problem is that it disregards the
date information, for example:

Query 1 (unloading)
Date User ID Unloading
8/28 X 3.5
8/29 X 2

Query 2 (loading)
Date User ID Loading
8/29 X 11

The end query does the following
User ID Total
X 14.5
X 13

What I would like is
Date User ID Total
8/28 X 3.5
8/29 X 13

below is the query used
SELECT qu1.[USER ID], Nz([qry_Associates Inbound Productivity by
Day].[SumofTotal Time],0)+Nz([4 Total Productivity w %].[Total Prod
hours],0)
AS Expr1
FROM (qu1 LEFT JOIN [4 Total Productivity w %] ON qu1.[USER ID] = [4 Total
Productivity w %].[USER ID]) LEFT JOIN [qry_Associates Inbound
Productivity
by Day] ON qu1.[USER ID] = [qry_Associates Inbound Productivity by
Day].[User
ID];

--
Thanks for your help again !
stephanie

Hi,


Make a query that supply all possible days:


SELECT associates FROM unloading
UNION
SELECT associates FROM loading


Saved it as qu1.


Next:


SELECT qu1.associates, Nz(unloading.performance, 0) +
Nz(loading.performance, 0)
FROM (qu1 LEFT JOIN loading ON qu1.associates = loading.associates)
LEFT JOIN unloading ON qu1.associates = unloading.associates


should do.



Hoping it may help,
Vanderghast, Access MVP