MSQuery quandary

Ok,

I need to pull out of the database the oldest invoice for each of my clients. Yet i think either or both of myself and MSQuery criteria are limited in this capacity.

please help.

Effectively

ClientA inv00001
ClientA inv00004
ClientA inv00007
ClientA inv00008
ClientB inv00002
ClientB inv00003
ClientB inv00006
ClientC inv00005
ETC...

Where i need to pull out just the bold records... sql script syntax or MSQuery criteria advice is appreciated.

cheers,
Oostersselect client, min(invoice) as min_invoice from daTable group by client|||Darn the socks of my bosses, they've shot me down again.

oldest invoice won't do now...

Also not sure if i understood you, please elaborate...

Here's my SQL with your remarks...

SELECT ClientView.AccountManagerCode, ClientView.Code, Min(InvoiceView.InceptionDate) AS 'Min of InceptionDate', InvoiceView.InvoicePrintDate
FROM pno.dbo.ClientView ClientView, pno.dbo.InvoiceView InvoiceView, pno.dbo.PolicyView PolicyView
WHERE ClientView.ClientID = PolicyView.ClientID AND InvoiceView.PolicyID = PolicyView.PolicyID AND ((ClientView.FSGDate Is Null) AND (InvoiceView.Category='n/b') AND (InvoiceView.InceptionDate>{ts '2003-12-31 00:00:00'}))
GROUP BY ClientView.AccountManagerCode, ClientView.Code, InvoiceView.InvoicePrintDate
ORDER BY ClientView.AccountManagerCode

It's not working, as above effectively i need the earliest New Business Invoice since 1/1/2004 for each client, and to cross reference those invoices with their client codes to determine whether an FSG has been issued or not.

Presently the script is still pulling multiple invoices for some clients. My last resort is to run the final filter through a macro, but i'd prefer a one step option.

Thankyou for your advice so far.

Oosters|||it's probably the GROUP BY that isn't working for you, you have more columns in there than what you originally told us

try this --SELECT C.AccountManagerCode
, C.Code
, I.InceptionDate AS Min_InceptionDate
, I.InvoicePrintDate
FROM pno.dbo.ClientView ClientView as C
, pno.dbo.InvoiceView InvoiceView as I
, pno.dbo.PolicyView PolicyView as P
WHERE C.ClientID = P.ClientID
AND I.PolicyID = P.PolicyID
AND C.FSGDate Is Null
AND I.Category = 'n/b'
AND I.InceptionDate
= ( select min(InceptionDate)
from InvoiceView
where PolicyID = P.PolicyID
and Category = 'n/b'
and InceptionDate
> {ts '2003-12-31 00:00:00'} )
GROUP
BY C.AccountManagerCode
, C.Code
, I.InvoicePrintDate
ORDER
BY C.AccountManagerCode|||Something's not right...

I copied and pasted your statements, and reviewed the logic. It all looks good, but when i run it...

"SQL query cannot be represented graphically. Continue anyway?"
[OK, Cancel, Help]

so i click ok and pray...

"Column InvoiceView.InceptionDate is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Atatement(s) could not be prepared."
[OK, Help]

So, not knowing anything about aggregate functions, i left that bit alone and added InvoiceView.InceptionDate to the GROUP BY clause, just after ClientView.Code

This permitted the statement to display a result set, however i'm still getting multiple invoices for each client.

Understandable if you can't help me any further, it's hard to teach an old noobie new tricks afterall. So thanks heaps for taking the time to review my Quandary nonetheless.

kind regards,
Oosters|||my sincere apologies

i was a bit too quick on the copy/paste

by introducing the subquery, i did away with the need for the GROUP BY, but i forgot to actually remove the GROUP BY from the query!!