outer joins using one table

[Jay]

I have a table tblJobNos with fields pkeyJobNos, strBatchNo, strOrderNo,
NumInvoiceNo, plus others that I'm not using in this querry.
The Query is as follows:
SELECT tblJobNos.strBatchNo, tblJobNos.strOrderNo, tblJobNos.pkeyJobNos
FROM tblJobNos LEFT OUTER JOIN tblJobNos AS tblJobNos_1 ON
tblJobNos.pkeyJobNos = tblJobNos_1.pkeyJobNos
WHERE (((tblJobNos_1.fkeyPattGetID)<>10) AND ((tblJobNos.numInvoiceNo) Is
Null))
ORDER BY tblJobNos.dtmDateSent, tblJobNos.strBatchNo, tblJobNos.pkeyJobNos;

My aim is to list ALL of the job nos in a batch, where there might be one or
more job no in the batch that haven't been invoiced yet, ie no invoice no..
The fkeyPattGetID<>10 indicates job nos that aren't 'Outstanding'.
The query above, only list the distinct job nos in the batch that haven't
been invoiced.

Help Please!

 

[Michel Walsh]

SELECT pkeyJobNos
FROM tblJobNos
GROUP BY pkeyJobNos
HAVING COUNT(numInvoiceNo) <> COUNT(*)


from what I understand. At least, it would return the groups where at least
one record of the group has a null for its numInvoiceNo field.

( Your prefixing disturbs me, is pkey used for something else than a
primary key field? )


Vanderghast, Access MVP

 

[Jay]

Yes, nothing special, just something to make it easier for me to read.

The job nos are grouped together in batches. While they are sent away
together as a batch, they are never returned together as a batch, and neither
are all the jobs in a batch invoiced together. I would like list only the
batches were there are still jobs that haven't been invoiced. Adding onto
that , I would like to see the complete batch (including the jobs that have
been invoiced).

Thanx

 

[Michel Walsh]

The first part should be like the already supplied query. The second part
can be solved with a join between the original table, and the said query,
the join occurring over their common field, pkeyJobNos.


Vanderghast, Access MVP

 

[John Spencer]

I might use a subquery to solve this problem.

SELECT *
FROM tblJobNos
WHERE strBatchNo in
(SELECT strBatchNo
FROM tblJobNos
WHERE NumInvoiceNo is Null)

You can do something similar with joins on two copies of the table, but
it will not be updatable. I prefer the above structure.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jay]

Thanx John and Michael.
Sometimes things are just too 'obvious' too see. Silly me!
You guys were great.