SQL - joining 5 tables - not all records retrieved

[Matt.]

Hi all!

The SQL is below. Here is the scenario. The table Production contains all
of the production information for our plant. Primary key is Job #, ProDate,
Shift, Machine #. The table T_Scrap contains a breakdown of the scrap
information. It has the same primary keys as the Production table. But the
Secondary scrap is recorded against the day it is discovered, rather then
the day it was cast, so for the problem I'm trying to solve, Shift and
Machine are irrelavent.

I'm trying to get all of the production information summarized by week. If
there is Secondary Scrap, I'd like to add that data, and if there's External
Scrap, I'd like to add that data, and if there's Customer Returns, I'd like
to add that data. But even if there is no scrap or returns, I should still
get all Production information summarized by week.

The query I've developed below is only returning 44 rows, and I know there
should be at least 500. So it appears as though rows are being dropped.
But logically, the LEFT Joins seem correct. Additionally, the totals I'm
getting are incorrect (in some cases by a factor of exactly 18).

Any light that can be shed on this would be greatly appreciated.

cheers,
Matt.

SELECT Production.[Job #],
Year([Production].[ProDate]) & "-" & WeekNumber([Production].[ProDate]) AS
YearWeek,
Machine.Unit,
Sum(T_Scrap.[Scrap Quantity]) AS SecondaryScrap,
Sum(T_Scrap_1.[Scrap Quantity]) AS ExternalScrap,
Sum([Production].[Cast Shots]*[Job Information Link].[Cavities]) AS
CastParts
FROM (((Production LEFT JOIN T_Scrap ON (Production.ProDate =
T_Scrap.ProDate) AND (Production.[Job #] = T_Scrap.[Job #])) LEFT JOIN
T_Scrap AS T_Scrap_1 ON (Production.ProDate = T_Scrap_1.ProDate) AND
(Production.[Job #] = T_Scrap_1.[Job #])) INNER JOIN [Job Information Link]
ON Production.[Job #] = [Job Information Link].[Job #]) INNER JOIN Machine
ON Production.[Machine #] = Machine.[Machine #]
WHERE (((T_Scrap.ScrapCode) Between "200" And "204")
AND ((T_Scrap_1.ScrapCode)="205")
AND ((Production.[Cast Shots])>0))
GROUP BY Production.[Job #], Year([Production].[ProDate]) & "-" &
WeekNumber([Production].[ProDate]), Machine.Unit;

[Pieter Linden]

User OUTER joins instead of inner joins. then you'll see all your
records. In the QBE grid, right-click on the join and then choose
option 2 or option 3.

[Salad]

"Matt." wrote:

Hi all!

The SQL is below. Here is the scenario. The table Production contains all
of the production information for our plant. Primary key is Job #, ProDate,
Shift, Machine #. The table T_Scrap contains a breakdown of the scrap
information. It has the same primary keys as the Production table. But the
Secondary scrap is recorded against the day it is discovered, rather then
the day it was cast, so for the problem I'm trying to solve, Shift and
Machine are irrelavent.

I'm trying to get all of the production information summarized by week. If
there is Secondary Scrap, I'd like to add that data, and if there's External
Scrap, I'd like to add that data, and if there's Customer Returns, I'd like
to add that data. But even if there is no scrap or returns, I should still
get all Production information summarized by week.

The query I've developed below is only returning 44 rows, and I know there
should be at least 500. So it appears as though rows are being dropped.
But logically, the LEFT Joins seem correct. Additionally, the totals I'm
getting are incorrect (in some cases by a factor of exactly 18).

Any light that can be shed on this would be greatly appreciated.

cheers,
Matt.

SELECT Production.[Job #],
Year([Production].[ProDate]) & "-" & WeekNumber([Production].[ProDate]) AS
YearWeek,
Machine.Unit,
Sum(T_Scrap.[Scrap Quantity]) AS SecondaryScrap,
Sum(T_Scrap_1.[Scrap Quantity]) AS ExternalScrap,
Sum([Production].[Cast Shots]*[Job Information Link].[Cavities]) AS
CastParts
FROM (((Production LEFT JOIN T_Scrap ON (Production.ProDate =
T_Scrap.ProDate) AND (Production.[Job #] = T_Scrap.[Job #])) LEFT JOIN
T_Scrap AS T_Scrap_1 ON (Production.ProDate = T_Scrap_1.ProDate) AND
(Production.[Job #] = T_Scrap_1.[Job #])) INNER JOIN [Job Information Link]
ON Production.[Job #] = [Job Information Link].[Job #]) INNER JOIN Machine
ON Production.[Machine #] = Machine.[Machine #]
WHERE (((T_Scrap.ScrapCode) Between "200" And "204")
AND ((T_Scrap_1.ScrapCode)="205")
AND ((Production.[Cast Shots])>0))
GROUP BY Production.[Job #], Year([Production].[ProDate]) & "-" &
WeekNumber([Production].[ProDate]), Machine.Unit;

Hmmmm, I like autonumbers. I think that'd simplify things in your life.
Probably too late not tho.

You need to do some debugging. Copy the query to a new query and open the new
one in design. Remove the Totals thus making it a Select query. Now run it and
review the results. You might as well see what the detail is and what you are
grouping.

Now start futzing with your Where clause. That's your culprit. For
example....I think you either have too many ANDs or not enough ORs. I'd wager
the lone
WHERE (((T_Scrap.ScrapCode) Between "200" And "204")
AND ((T_Scrap_1.ScrapCode)="205")
is it.and mist likely the
AND ((T_Scrap_1.ScrapCode)="205")
line.
Maybe it needs to be
WHERE (((T_Scrap.ScrapCode) Between "200" And "204")
AND (T_Scrap_1.ScrapCode="205" Or T_Scrap_1.ScrapCod Is Null)
since T_Scrap_1 may not have a record.

The bottom line is your filter is doing too much filtering. You need to find
out where. One you figure that out then convert back to a totals.

[Matt.]

Hi again!

I ended up breaking the query down into 3 separate queries (One each for
primary scrap, secondary scrap, and customer returns) each keyed by YearWeek
and JobNo, and then using another query to join them together.

It runs a little slower than I wanted, but at least I know the data's right.

cheers,
Matt.

"Matt." <ma*******************@hotmail.com> wrote in message
news:0M********************@news20.bellglobal.com. ..

Hi all!

The SQL is below. Here is the scenario. The table Production contains all of the production information for our plant. Primary key is Job #, ProDate, Shift, Machine #. The table T_Scrap contains a breakdown of the scrap
information. It has the same primary keys as the Production table. But the Secondary scrap is recorded against the day it is discovered, rather then
the day it was cast, so for the problem I'm trying to solve, Shift and
Machine are irrelavent.

I'm trying to get all of the production information summarized by week. If there is Secondary Scrap, I'd like to add that data, and if there's External Scrap, I'd like to add that data, and if there's Customer Returns, I'd like to add that data. But even if there is no scrap or returns, I should still get all Production information summarized by week.

The query I've developed below is only returning 44 rows, and I know there
should be at least 500. So it appears as though rows are being dropped.
But logically, the LEFT Joins seem correct. Additionally, the totals I'm
getting are incorrect (in some cases by a factor of exactly 18).

Any light that can be shed on this would be greatly appreciated.

cheers,
Matt.

SELECT Production.[Job #],
Year([Production].[ProDate]) & "-" & WeekNumber([Production].[ProDate]) AS YearWeek,
Machine.Unit,
Sum(T_Scrap.[Scrap Quantity]) AS SecondaryScrap,
Sum(T_Scrap_1.[Scrap Quantity]) AS ExternalScrap,
Sum([Production].[Cast Shots]*[Job Information Link].[Cavities]) AS
CastParts
FROM (((Production LEFT JOIN T_Scrap ON (Production.ProDate =
T_Scrap.ProDate) AND (Production.[Job #] = T_Scrap.[Job #])) LEFT JOIN
T_Scrap AS T_Scrap_1 ON (Production.ProDate = T_Scrap_1.ProDate) AND
(Production.[Job #] = T_Scrap_1.[Job #])) INNER JOIN [Job Information Link] ON Production.[Job #] = [Job Information Link].[Job #]) INNER JOIN Machine
ON Production.[Machine #] = Machine.[Machine #]
WHERE (((T_Scrap.ScrapCode) Between "200" And "204")
AND ((T_Scrap_1.ScrapCode)="205")
AND ((Production.[Cast Shots])>0))
GROUP BY Production.[Job #], Year([Production].[ProDate]) & "-" &
WeekNumber([Production].[ProDate]), Machine.Unit;