identifying inactive entities

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables joined by [VendorID] with referential integrity enforced:

tblVendors
[VendorID]
[VendorName]

tblInvoices
[VendorID]
[InvoiceDate]
[InvoiceAmount]

I would like create a query I can run monthly that will show me all vendors
who have not billed for six months so that I mark them as inactive. Any help
is appreciated. Thanks.
 
Use a subquery in the WHERE clause to get the most recent invoice date for
the vendor.

Create a new query, switch it to SQL View (View menu), and paste this:

SELECT tblVendors.*
FROM tblVendors
WHERE (SELECT Max([InvoiceDate])
FROM tblInvoices
WHERE tblInvoices.VendorID = tblVendors.VendorID)
< DateAdd("m", -6, Date());

Note that this query will not choose vendors who have no invoices at all.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Thanks, Allen. You're awesome. My late night posts are always rewarded by
some smart Australian (it seems always to be you) giving a succinct and
educational answer that fits the bill perfectly. I need to work late more
often. Cheers from the Pacific Standard Timezone.

thanks again,
spence

Allen Browne said:
Use a subquery in the WHERE clause to get the most recent invoice date for
the vendor

Create a new query, switch it to SQL View (View menu), and paste this:

SELECT tblVendors.*
FROM tblVendors
WHERE (SELECT Max([InvoiceDate])
FROM tblInvoices
WHERE tblInvoices.VendorID = tblVendors.VendorID)
< DateAdd("m", -6, Date());

Note that this query will not choose vendors who have no invoices at all.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

spence said:
I have two tables joined by [VendorID] with referential integrity enforced:

tblVendors
[VendorID]
[VendorName]

tblInvoices
[VendorID]
[InvoiceDate]
[InvoiceAmount]

I would like create a query I can run monthly that will show me all
vendors
who have not billed for six months so that I mark them as inactive. Any
help
is appreciated. Thanks.
Click to expand...
Click to expand...
 
Back
Top