Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009

    Question Unanswered: Run query to get the last two dates in a certain time period

    I'm having trouble with the MAX and Count expressions and putting them together.

    Basically, I have a DB with Vendor/Invoice information and I am needing a report that will show all of the vendors with a Frequency field set for "Quarterly" who we are due an invoice but have not yet received. Problem is that the Frequency field in the Master Vendor List table has never been manually populated when they enter new Vendors so out of 480 vendors, I only have 39 with a value in that field.

    So as a result, I am having to figure out which vendors send invoices monthly, which sent them quarterly and which send them yearly, then populate that field. I'm running a few date queries trying to get the last invoice date for a Vendor to see if there is a monthly recurrence or quarterly, - and I also need to get the last two invoice dates in Jan and the last two in Feb. I was able to create the query to get me the last invoice date (singular) by just doing a MAX and entering my dates. How do I get it to display two dates though?

    Here is my current Select statement. What should I add and where? I'm stumped! TIA everyone for your help!

    SELECT [Invoice Log 2009].Vendor, Max([Invoice Log 2009].[Invoice Date]) AS [MaxOfInvoice Date], [Master Vendor List].Frequency
    FROM [Master Vendor List] INNER JOIN [Invoice Log 2009] ON [Master Vendor List].[Vendor Name] = [Invoice Log 2009].Vendor
    GROUP BY [Invoice Log 2009].Vendor, [Master Vendor List].Frequency
    HAVING (((Max([Invoice Log 2009].[Invoice Date])) Between [Please Enter Starting Date] And [Please Enter Ending Date]));

  2. #2
    Join Date
    May 2003
    SELECT TOP 2 Table.InvoiceDate
    FROM Table
    ORDER BY Table.InvoiceDate DESC;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts