create a query to show month/year date columns. NEED HELP PLEASE!!

[Guest]

MyTable

ProjectID SumOfTotal I_ApprovalACD
5049 9112601 27-Jan-06
5081 127393 05-Jul-05
5084 99574 30-Jan-05
5101 599 17-Jul-05
5109 22049 16-Apr-05
5128 134489 28-Apr-05


Above is a table called MyTable. I want to create a query that display this
data like the following. What is the best way to do that?

ProjectID 04/05 07/05 01/06
5049 0 0 9112601
5081 0 127393 0
5084 0 0 99574
5101 0 599 0
5109 22049 0 0
5128 134489 0 0

 

[Guest]

First, I'd suggest changing your display name. It's about rude.

I want to create a query that display this
data like the following. What is the best way to do that?

Assuming 30-Jan-05 is a typo and should be 30-Jan-06 instead, the easiest
way is with a UNION query such as the following:

SELECT ProjectID, SumOfTotal AS Apr05, "0" AS Jul05, "0" AS Jan06
FROM MyTable
WHERE ((Month(I_ApprovalACD) = 4) AND (Year(I_ApprovalACD) = 2005))
UNION
SELECT ProjectID, "0" AS Apr05, SumOfTotal AS Jul05, "0" AS Jan06
FROM MyTable
WHERE ((Month(I_ApprovalACD) = 7) AND (Year(I_ApprovalACD) = 2005))
UNION
SELECT ProjectID, "0" AS Apr05, "0" AS Jul05, SumOfTotal AS Jan06
FROM MyTable
WHERE ((Month(I_ApprovalACD) = 1) AND (Year(I_ApprovalACD) = 2006));

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Gary Walter]

MyTable

ProjectID SumOfTotal I_ApprovalACD
5049 9112601 27-Jan-06
5081 127393 05-Jul-05
5084 99574 30-Jan-05
5101 599 17-Jul-05
5109 22049 16-Apr-05
5128 134489 28-Apr-05


Above is a table called MyTable. I want to create a query that display
this
data like the following. What is the best way to do that?

ProjectID 04/05 07/05 01/06
5049 0 0 9112601
5081 0 127393 0
5084 0 0 99574
5101 0 599 0
5109 22049 0 0
5128 134489 0 0

This looks like a perfect candidate for
a crosstab:

TRANSFORM Nz(Sum([SumOfTotal]),0) AS TSum
SELECT MyTable.ProjectID
FROM MyTable
GROUP BY MyTable.ProjectID
ORDER BY MyTable.ProjectID
PIVOT Format([I_ApprovalACD],"mm/yy") In ('04/05','07/05','01/06');

since the date format gives a string,
you would need to use "In (x,x,x)" to sort
like you wanted, i.e., as text they would
sort

01/06
04/05
07/05

I believe you could get sorting left-to-right
from earliest date to latest date if you could
change the format of the column heading to

PIVOT Format([I_ApprovalACD],"yyyy/mm")

so you would not need an "In (x,x,x)"

TRANSFORM Nz(Sum([SumOfTotal]),0) AS TSum
SELECT MyTable.ProjectID
FROM MyTable
GROUP BY MyTable.ProjectID
ORDER BY MyTable.ProjectID
PIVOT Format([I_ApprovalACD],"yyyy/mm")

should produce:

ProjectID 2005/04 2005/07 2006/01
5049 0 0 9112601
5081 0 127393 0
5084 0 0 99574
5101 0 599 0
5109 22049 0 0
5128 134489 0 0