Mess of a query

  • Thread starter Thread starter Golfinray
  • Start date Start date
G

Golfinray

I have a spreadsheet inherited that I need to query. I imported it, changed
my datatypes to get them correct, etc. Here is the problem:
Payment Date Amount Payment
10/2/2007 $32,476 Partial
12/15/2007 $55,025 Partial
3/4/2008 $70,132 Partial
6/1/2008 $62,154 Final
I need to extract the first partial date and the final date. Then I can use
datediff to find out how long the project took to complete. I have tried
first, last, max, min, and have had no luck. Sometimes there are 3, 4, 5, or
more partials, sometimes just one payment all at once. All are tied to
project numbers. Thanks so much!
 
SELECT Golfinray.[project number],
Min(Golfinray.[Payment Date]) AS [MinDate],
Max(Golfinray.[Payment Date]) AS [MaxDate],
[MaxDate]-[MinDate] AS Days
FROM Golfinray
GROUP BY Golfinray.[project number];

My Logic is that the Max of the Payment Date is going to be the Final. The
Min of Payment Date is going to be the first Partial. If they are the same
date, then there was only one final payment. Subtracting the Min from the Max
should give you the number of days.
 
Thanks Jerry, you the man!

Jerry Whittle said:
SELECT Golfinray.[project number],
Min(Golfinray.[Payment Date]) AS [MinDate],
Max(Golfinray.[Payment Date]) AS [MaxDate],
[MaxDate]-[MinDate] AS Days
FROM Golfinray
GROUP BY Golfinray.[project number];

My Logic is that the Max of the Payment Date is going to be the Final. The
Min of Payment Date is going to be the first Partial. If they are the same
date, then there was only one final payment. Subtracting the Min from the Max
should give you the number of days.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Golfinray said:
I have a spreadsheet inherited that I need to query. I imported it, changed
my datatypes to get them correct, etc. Here is the problem:
Payment Date Amount Payment
10/2/2007 $32,476 Partial
12/15/2007 $55,025 Partial
3/4/2008 $70,132 Partial
6/1/2008 $62,154 Final
I need to extract the first partial date and the final date. Then I can use
datediff to find out how long the project took to complete. I have tried
first, last, max, min, and have had no luck. Sometimes there are 3, 4, 5, or
more partials, sometimes just one payment all at once. All are tied to
project numbers. Thanks so much!
Click to expand...
Click to expand...
 
Back
Top