Re: Cross Tab Query and Totals

[grubnz]

Re: Cross Tab Query and Totals

Hi,

What I did first was to create a cross tab query as below,

TRANSFORM Sum(Tbl_Expenditure.Amount) AS SumOfAmount
SELECT Tbl_Main.Date
FROM Tbl_Main INNER JOIN Tbl_Expenditure ON Tbl_Main.MainID = Tbl_Expenditure.MainID
GROUP BY Tbl_Main.MainID, Tbl_Main.Date
ORDER BY Tbl_Main.Date
PIVOT Tbl_Expenditure.Description;

Then I created the below query,

SELECT Tbl_Main.MainID, Tbl_Main.ChequeNumber, Qry_ExpenditureCrossTab.Date, Qry_ExpenditureCrossTab.[Friday Night Comp], Qry_ExpenditureCrossTab.Miscellaneous, Qry_ExpenditureCrossTab.Suppers, Qry_ExpenditureCrossTab.Functions, Qry_ExpenditureCrossTab.Drinks, Qry_ExpenditureCrossTab.Prizes, Qry_ExpenditureCrossTab.Trophies, Qry_ExpenditureCrossTab.[Inter Club Fees], [Friday Night Comp ]+[Miscellaneous]+[Suppers]+[Functions]+[Drinks]+[Prizes]+[Trophies]+[Inter Club Fees] AS Total
FROM Tbl_Main INNER JOIN Qry_ExpenditureCrossTab ON Tbl_Main.Date = Qry_ExpenditureCrossTab.Date;

The above query works if there are values in each of the fields if there are null values it will not add up the total.

I know I will need to make the values default to 0 but I am not sure how to do this.

I have read some of the other threads on this subject but I have found it difficult to apply to my situation.

Thank you to anybody who can help me with this.

 

[JHB]

Re: Cross Tab Query and Totals

[FONT=&quot]Use nz around the fields which could be Null.
[/FONT]

nz(YourFildName,0)

 

[grubnz]

Re: Cross Tab Query and Totals

Hi JHB,

Thank you once again for your time and assistance I have followed what you have mention and it has worked

Expr1: Nz([Qry_ExpenditureCrossTab].[Friday Night Comp],0)

Also wondering if you are able to assist me with the below it has occurred once I did what had been suggested but I am now puzzled with a result I get in the total.

Adding the fields together it seems to take the numbers from the fields as a string rather than a number.

In my table I have the field as a currency data type. but when I created Expr1 it just became a number. Which is fine.

Total: Sum(([qryExpenditureTest]![Expr1]+[qryExpenditureTest]![Expr2]))
35 + 0 result 350 or 70 + 200 result 70200

Subtracting the fields the result is correct
Total: Sum(([qryExpenditureTest]![Expr1]-[qryExpenditureTest]![Expr2]))
35-0 result 35 or 70-200 result -130

Even multiplying
Total: Sum(([qryExpenditureTest]![Expr1]*[qryExpenditureTest]![Expr2]))
35*0 result 0 or 70 * 200 result 14000

Even division after changing the default value from 0 to 1 so it wasn't dividing by zero works

Total: Sum(([qryExpenditureTest]![Expr1]/[qryExpenditureTest]![Expr2]))
35/1 result 35 or 70/200 result 0.35

I have gone on line and the sum operator is + but also used in Concatenation operators - Combines two strings to form one string and propagates null values.

Thank you and a Happy New Year

 

[JHB]

Re: Cross Tab Query and Totals

Hi grubnz, first, Happy New Year to you too.

Maybe it could be because one of you table fields is a text field, even you think it is a number!

I've made one query example for you to show why I think so.
The numbers in the table look like numbers, but the numbers are stored in a text field.
The query 1. field show the numbers from the table and as you se from the sorting, it is text.
In the 2. field I'm adding a CStr(10), (10 converted to string), the result is as expected when you adding string together, (20+10 = 2010).
But if I "-", "*" or "/" it is treated like numbers, as you have observed!

But the really strange thing is, if I set the field type as number and set the query as "[EmpNo] & CStr(10)", the number is treated as string, result is 20+10 = 2010, I would have expected an error would arise.

 

[grubnz]

Re: Cross Tab Query and Totals

Hi JHB,

Thank you for your example it got me thinking as you were saying it must be storing it as a text field and not a number/currency field.

So I took a closer look at the query as I was creating it.

What seems to have occurred was when I created the crosstab query the value still maintained the Currency format $35.00. All was okay.
But once I created the expression below the format of the value change its format was no longer currency and the value reverted to the right hand side of the column. To me that meant showing that the value was now being stored as a text field.

Expr1: Nz([Qry_ExpenditureCrossTab].[Friday Night Comp],0)

To counteract what was happening to this value, was I added CInt to the expression converting the text value back to a numeric value. Once I did this the value shows up on the left hand side of the column showing it is now being stored as a number value once again.

Expr1: CInt(Nz([Qry_ExpenditureCrossTab].[Friday Night Comp],0))

I then ran the query again, the below expression now produces the correct result.

Total: Sum(([qryExpenditureTest]![Expr1]+[qryExpenditureTest]![Expr2]))
35 + 0 Result 35, 70 + 200 Result 270

Thank you for your time and effort.