Can't solve this query problem.

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

Golfinray

SELECT [001_all payment Tracking].[New Project ID], [001_all payment
Tracking].[Payment Dates], [001_all payment Tracking].[Cumulative Payments],
[0001_all payment min and max dates].Mindate, [0001_all payment min and max
dates].maxdate
FROM [001_all payment Tracking] INNER JOIN [0001_all payment min and max
dates] ON [001_all payment Tracking].[New Project ID] = [0001_all payment min
and max dates].[New Project ID];

This query give me all project numbers, payment dates, first payment dat,
last payment date. Here is what it looks like now.
0607-1100-001 10/01/2007 $30,000 10/01/2007 1/6/2008
0607-1100-001 11/4/2007 $52,560 10/01/2007 1/6/2008
0607-1100-001 12/8/2007 $48,380 10/01/2007 1/6/2008

It is giving me the 3 payments made on the project. What I want is just ONE
project number with the first payment date (mindate) and last payment date
(maxdate) and a total of the payments. I'm just not having any luck. Thanks
so much.
 
Try SELECT TOP 1 [001_all payment Tracking].[New Project ID], [001_all payment
Tracking].[Payment Dates], [001_all payment Tracking].[Cumulative Payments],
[0001_all payment min and max dates].Mindate, [0001_all payment min and max
[dates].maxdate
FROM [001_all payment Tracking] INNER JOIN [0001_all payment min and max
dates] ON [001_all payment Tracking].[New Project ID] = [0001_all payment
min
and max dates].[New Project ID];

Good Luck
 
Thanks, but that gives me a syntax error in the join.

Leona Leal Educator said:
Try SELECT TOP 1 [001_all payment Tracking].[New Project ID], [001_all payment
Tracking].[Payment Dates], [001_all payment Tracking].[Cumulative Payments],
[0001_all payment min and max dates].Mindate, [0001_all payment min and max
[dates].maxdate
FROM [001_all payment Tracking] INNER JOIN [0001_all payment min and max
dates] ON [001_all payment Tracking].[New Project ID] = [0001_all payment
min
and max dates].[New Project ID];

Good Luck


Golfinray said:
SELECT [001_all payment Tracking].[New Project ID], [001_all payment
Tracking].[Payment Dates], [001_all payment Tracking].[Cumulative Payments],
[0001_all payment min and max dates].Mindate, [0001_all payment min and max
dates].maxdate
FROM [001_all payment Tracking] INNER JOIN [0001_all payment min and max
dates] ON [001_all payment Tracking].[New Project ID] = [0001_all payment min
and max dates].[New Project ID];

This query give me all project numbers, payment dates, first payment dat,
last payment date. Here is what it looks like now.
0607-1100-001 10/01/2007 $30,000 10/01/2007 1/6/2008
0607-1100-001 11/4/2007 $52,560 10/01/2007 1/6/2008
0607-1100-001 12/8/2007 $48,380 10/01/2007 1/6/2008

It is giving me the 3 payments made on the project. What I want is just ONE
project number with the first payment date (mindate) and last payment date
(maxdate) and a total of the payments. I'm just not having any luck. Thanks
so much.
Click to expand...
Click to expand...
 
SELECT [001_all payment Tracking].[New Project ID]
, SUM([001_all payment Tracking].[Cumulative Payments]) as TotalPayment
, [0001_all payment min and max dates].Mindate
, [0001_all payment min and max dates].maxdate
FROM [001_all payment Tracking] INNER JOIN
[0001_all payment min and max dates]
ON [001_all payment Tracking].[New Project ID] =
[0001_all payment min and max dates].[New Project ID]
GROUP BY 001_all payment Tracking].[New Project ID]
, [0001_all payment min and max dates].Mindate
, [0001_all payment min and max dates].maxdate

AS a guess, you should be able to do that with a query that looks like this.
Unless you are getting the Min and Max dates from some other table.

SELECT [New Project ID]
, Min([Payment Dates]) as MinDate
, Max([Payment Dates]) as MaxDate
, Sum([Cumulative Payments]) as TotalPayments
FROM [001_all payment Tracking]
GROUP BY [New Project ID]


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top