outer joins using one table

J

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!
 
M

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
 
J

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
 
M

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
 
J

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
'====================================================
 
J

Jay

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top