Another prob in a different query

[Ling]

I have 2 tables here:

tblDo tblJobs
------ ---------
QuantityDelivered QtyRequired
JobSheetNo JobSheetNo

tblDo.JobSheetNo is linked to tblJobs.JobSheetNo,
1 JobSheetNo can appear many times in the tblDo(1 to many
relationship)

Now, I would like to sum up all the quantity delivered in
tblDo in relation to the JobSheetNo.
But some JobSheetNo do not appear in tblDo.

Thus, the records shown are only those of JobSheetNo that
appear in tblDo.I want all the JobSheetNo to appear even
if it doesnt appear in tblDo, and the corresponding qty
delivered beside it.

Any ideas? I am trying to figure it out for a long time.

Thanx

Ling

 

[Michel Walsh]

Hi,


That is the job of an outer join, instead of an inner join.


SELECT tblJobs.JobSheerNo,
tblJobs.QtyRequired,
Nz(SUM(tblDo.QuantityDelivered), 0) As TotalDelivered
FROM tblJobs LEFT JOIN tblDo
ON tblJobs.JobSheetNo= tblDo.JobSheetNo



In an outer join, one of the two tables get all its records preserved
(ie, they will appear at least once, in the result). In a LEFT join, that is
the one appearing at the LEFT of the text, in the FROM clause (in a RIGTH,
it would be the table name appearing at the RIGHT on the text, in the from
clause).

FROM a LEFT JOIN b

is then same as

FROM b RIGHT JOIN a


since in both case, table "a" is the preserved table (appearing on the side
of the mentioned side, ie, to the LEFT side when using LEFT, or to the RIGHT
side when using RIGHT).


The unpreserved table may have no match to offer, for some records. In that
cases, the result fill an unmatched row with null. It is then possible that

tblDo.QuantityDelivered

is NULL, for a given group, so SUM(tblDo.QuantityDelivered) would be null
too. To revert that null to a zero, we used

Nz( expression , 0)


which returns expression, unless it is null, then it returns the second
argument, here, 0.




Hoping it may help,
Vanderghast, Access MVP

 

[Ling]

Here's another problem:
It could now show all the JobsheetNo even if the qty = 0.
Thanx
But now I have this query.(my code is below)
It could show all records but when I convert this query to
a pass-through, only selected records could show.(I have
to use a pass through query as the tables are linked to
the server).

Later, I found out that those records with material3 = 0
are not shown.

Any ideas?

Ling


My code:
------------------------------------------------------
SELECT tblJobs.JobSheetNo, tblPaperRaw.[Raw Paper Type]

FROM ((tblCustomers INNER JOIN tblCostWorksheet ON
tblCustomers.CustomerCode = tblCostWorksheet.CustomerCode)

INNER JOIN ((tblQuotation INNER JOIN tblJobs ON
tblQuotation.QuotationNumber = tblJobs.QuotationNo)

INNER JOIN tblDo ON tblJobs.JobSheetNo = tblDo.JobSheetNo)
ON tblCostWorksheet.EnquiryNo = tblQuotation.EnquiryNo)

INNER JOIN tblPaperRaw ON
(tblCostWorksheet.PaperTextRawSize = tblPaperRaw.[Raw
Paper Size])
AND (tblCostWorksheet.Material3 = tblPaperRaw.RawPaperID);

-----------------------------------------------------------

-----Original Message-----
Hi,


That is the job of an outer join, instead of an inner join.


SELECT tblJobs.JobSheerNo,
tblJobs.QtyRequired,
Nz(SUM(tblDo.QuantityDelivered), 0) As TotalDelivered
FROM tblJobs LEFT JOIN tblDo
ON tblJobs.JobSheetNo= tblDo.JobSheetNo



In an outer join, one of the two tables get all its records preserved
(ie, they will appear at least once, in the result). In a LEFT join, that is
the one appearing at the LEFT of the text, in the FROM clause (in a RIGTH,
it would be the table name appearing at the RIGHT on the text, in the from
clause).

FROM a LEFT JOIN b

is then same as

FROM b RIGHT JOIN a


since in both case, table "a" is the preserved table (appearing on the side
of the mentioned side, ie, to the LEFT side when using LEFT, or to the RIGHT
side when using RIGHT).


The unpreserved table may have no match to offer, for some records. In that
cases, the result fill an unmatched row with null. It is then possible that

tblDo.QuantityDelivered

is NULL, for a given group, so SUM

(tblDo.QuantityDelivered) would be null

 

[Michel Walsh]

Hi,


Since you have inner joins and only inner join, no outer join, only
fields that share a common value are kept. An inner join, if there is no
duplicated values on one of the tables, act like an INTERSECTION, in the set
theory. After the join is done, under those assumptions, only what is common
in both tables (sets) is left. It may be possible that all your records
about material3 are left out of the intersection?


Hoping it may help,
Vanderghast, Access MVP

Here's another problem:
It could now show all the JobsheetNo even if the qty = 0.
Thanx
But now I have this query.(my code is below)
It could show all records but when I convert this query to
a pass-through, only selected records could show.(I have
to use a pass through query as the tables are linked to
the server).

Later, I found out that those records with material3 = 0
are not shown.

Any ideas?

Ling


My code:
------------------------------------------------------
SELECT tblJobs.JobSheetNo, tblPaperRaw.[Raw Paper Type]

FROM ((tblCustomers INNER JOIN tblCostWorksheet ON
tblCustomers.CustomerCode = tblCostWorksheet.CustomerCode)

INNER JOIN ((tblQuotation INNER JOIN tblJobs ON
tblQuotation.QuotationNumber = tblJobs.QuotationNo)

INNER JOIN tblDo ON tblJobs.JobSheetNo = tblDo.JobSheetNo)
ON tblCostWorksheet.EnquiryNo = tblQuotation.EnquiryNo)

INNER JOIN tblPaperRaw ON
(tblCostWorksheet.PaperTextRawSize = tblPaperRaw.[Raw
Paper Size])
AND (tblCostWorksheet.Material3 = tblPaperRaw.RawPaperID);

-----------------------------------------------------------

-----Original Message-----
Hi,


That is the job of an outer join, instead of an inner join.


SELECT tblJobs.JobSheerNo,
tblJobs.QtyRequired,
Nz(SUM(tblDo.QuantityDelivered), 0) As TotalDelivered
FROM tblJobs LEFT JOIN tblDo
ON tblJobs.JobSheetNo= tblDo.JobSheetNo



In an outer join, one of the two tables get all its records preserved
(ie, they will appear at least once, in the result). In a LEFT join, that is
the one appearing at the LEFT of the text, in the FROM clause (in a RIGTH,
it would be the table name appearing at the RIGHT on the text, in the from
clause).

FROM a LEFT JOIN b

is then same as

FROM b RIGHT JOIN a


since in both case, table "a" is the preserved table (appearing on the side
of the mentioned side, ie, to the LEFT side when using LEFT, or to the RIGHT
side when using RIGHT).


The unpreserved table may have no match to offer, for some records. In that
cases, the result fill an unmatched row with null. It is then possible that

tblDo.QuantityDelivered

is NULL, for a given group, so SUM

(tblDo.QuantityDelivered) would be null

too. To revert that null to a zero, we used

Nz( expression , 0)


which returns expression, unless it is null, then it returns the second
argument, here, 0.




Hoping it may help,
Vanderghast, Access MVP






.