Display columns without data in cross tab query

  • Thread starter Thread starter Der Musensohn
  • Start date Start date
D

Der Musensohn

I have a table of accounting transactions. I want to create a cross tab
query that will display totals based on accounts by week. So far I have:

PARAMETERS [Begin week date] DateTime, [End week date] DateTime;
TRANSFORM Sum([Transactions fixed].Amount) AS Amount
SELECT Therapists.TherapistID, [Transactions fixed].Class
FROM Therapists INNER JOIN [Transactions fixed] ON Therapists.[Customer
Type] = [Transactions fixed].Class
WHERE ((([Transactions fixed].Date) Between [Begin week date] And [end week
date]) AND (([Transactions fixed].Type)="invoice"))
GROUP BY Therapists.TherapistID, [Transactions fixed].Class
ORDER BY [Transactions fixed].Class
PIVOT [Transactions fixed].Account;

Ultimately, I need the query to display all accounts even if there are 0
transactions.

Thanks for any help.
 
Add the accounts to the PIVOT statement in the order to be displayed --
PIVOT [Transactions fixed].Account In("Account_A","Account_B","Account_C");
 
Further clarification: I am using this to create a larger spreadsheet. I
run the query based on date parameters and then paste the information into
Excel. Not every account has an amount based on the dates so I want a query
that will show all accounts so I can paste each successive result without
having to add and subtract cells in the spreadsheet. Make sense?

Thanks.
 
Thanks Karl. By "Account_A" etc. do you mean the accounts themselves? ie.
I have accounts titled Individual , Group, Training. So do you mean:
PIVOT [Transactions fixed].Account In("Individual","Group","Training")

Thanks.

KARL DEWEY said:
Add the accounts to the PIVOT statement in the order to be displayed --
PIVOT [Transactions fixed].Account In("Account_A","Account_B","Account_C");

--
Build a little, test a little.


Der Musensohn said:
I have a table of accounting transactions. I want to create a cross tab
query that will display totals based on accounts by week. So far I have:

PARAMETERS [Begin week date] DateTime, [End week date] DateTime;
TRANSFORM Sum([Transactions fixed].Amount) AS Amount
SELECT Therapists.TherapistID, [Transactions fixed].Class
FROM Therapists INNER JOIN [Transactions fixed] ON Therapists.[Customer
Type] = [Transactions fixed].Class
WHERE ((([Transactions fixed].Date) Between [Begin week date] And [end week
date]) AND (([Transactions fixed].Type)="invoice"))
GROUP BY Therapists.TherapistID, [Transactions fixed].Class
ORDER BY [Transactions fixed].Class
PIVOT [Transactions fixed].Account;

Ultimately, I need the query to display all accounts even if there are 0
transactions.

Thanks for any help.
Click to expand...
Click to expand...
 
Ignore that last question. Test a little!!!!!!

Thank you Karl.

Der Musensohn said:
Thanks Karl. By "Account_A" etc. do you mean the accounts themselves? ie.
I have accounts titled Individual , Group, Training. So do you mean:
PIVOT [Transactions fixed].Account In("Individual","Group","Training")

Thanks.

KARL DEWEY said:
Add the accounts to the PIVOT statement in the order to be displayed --
PIVOT [Transactions fixed].Account In("Account_A","Account_B","Account_C");

--
Build a little, test a little.


Der Musensohn said:
I have a table of accounting transactions. I want to create a cross tab
query that will display totals based on accounts by week. So far I have:

PARAMETERS [Begin week date] DateTime, [End week date] DateTime;
TRANSFORM Sum([Transactions fixed].Amount) AS Amount
SELECT Therapists.TherapistID, [Transactions fixed].Class
FROM Therapists INNER JOIN [Transactions fixed] ON Therapists.[Customer
Type] = [Transactions fixed].Class
WHERE ((([Transactions fixed].Date) Between [Begin week date] And [end week
date]) AND (([Transactions fixed].Type)="invoice"))
GROUP BY Therapists.TherapistID, [Transactions fixed].Class
ORDER BY [Transactions fixed].Class
PIVOT [Transactions fixed].Account;

Ultimately, I need the query to display all accounts even if there are 0
transactions.

Thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top