MS Access : Transform and PIVOT

Dear Experts,

First of all I hope my explanation is clear to sort my issue. I have the following query :-

SELECT [tbl_Expense Reports].ID, [tbl_Expense Details].ID, [tbl_Expense Reports].Project_Short_Name, [tbl_Expense Reports].Expenses_Type, [tbl_Expense Reports].Maintenance_Type, [tbl_Expense Details].Category, [tbl_Expense Reports].Status, IIf([Status] Like "Paid","Paid","Pending") AS Status_Report, [tbl_Expense Details].[Expense Date], Format([Expense Date],"yyyymm") AS ExpenseDateYYYYMM, Format([Expense Date],"yyyy") AS ExpenseDateYYYY, DateDiff("m",Date(),DateSerial(Year(Date()),12,31)) AS RemainMM, [tbl_Expense Details].Cost, [tbl_Expense Details].Approved, IIf([Approved]=True,[Cost],0) AS Approved_Cost, IIf([Approved]=True,1,0) AS Approved_Invoice, IIf([Approved]=True And [Cost]>0,[Cost],0) AS Approved_Debit, IIf([Approved]=True And [Cost]<0,[Cost],0) AS Approved_Credit
FROM tbl_Expense_1_AC INNER JOIN ([tbl_Expense Reports] INNER JOIN [tbl_Expense Details] ON [tbl_Expense Reports].ID = [tbl_Expense Details].[Expense Report]) ON tbl_Expense_1_AC.pk_Exp_Ac_ID = [tbl_Expense Reports].Expenses_Type
WHERE ((([tbl_Expense Reports].Project_Short_Name) Not Like "Test"))
ORDER BY [tbl_Expense Reports].Project_Short_Name;

Open in new window


And based on this query, another qoury has been made to transofrmed and PIVOT the results to be demonistrated as in attachment Number 1 (kindly note that number from 1 to 12  in the column ) represent the month number. i.e. 1= Jan , 2=Feb and so on. The Code is

TRANSFORM Sum(QEXP_00_0000_ALL.Approved_Cost) AS SumOfApproved_Cost
SELECT QEXP_00_0000_ALL.Project_Short_Name, Sum(QEXP_00_0000_ALL.Approved_Cost) AS Total
FROM QEXP_00_0000_ALL
GROUP BY QEXP_00_0000_ALL.Project_Short_Name
PIVOT Year([Expense Date])*12+Format([Expense Date],"mm")-(Year(Date())*12+Month([RemainMM]))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

Open in new window


The issue started at the begging of this month "November"  for unknown reason , whereas the numbers that should be represented in the column from 1 to 12 is not showing, and only the Total is appear. (Please Refere to attachment Number 2) I think it is might be related to this line of code :

PIVOT Year([Expense Date])*12+Format([Expense Date],"mm")-(Year(Date())*12+Month([RemainMM]))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

Open in new window


Having said that, could you please help how to solve this issue.

Thanks a millions.
attachment_1.jpg
attachment_2.jpg
TAB-000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
I'm not sure what you are trying to accomplish with this calculation in your PIVOT clause:

Year([Expense Date])*12+Format([Expense Date],"mm")-(Year(Date())*12+Month([RemainMM]))+1

Can you explain in English what you are trying to accomplish?
0
TAB-000Author Commented:
Explanation and solution in English would be found HERE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TAB-000Author Commented:
The answer found in the link.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.