Suming Table and crosstab query 1

[BMKanun]

Hi all,
is it possible to sum table with structure

ExpenceTypeID, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12 (months)(all possible ExpenceTypeID listed and all values in fields =0)

with Crosstab

ExpenceTypeID, 01, 03, 04
(not all ExpenceType or months return value)

the idea is to get crosstab
with all ExpenceTypeID's and all months, even if the value is 0.

Thanks a lot,
B

e.g.
ExpTypeID, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12

1 3 0 5 7 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0 0 0 0
etc...

 

[dhookom]

You can create a query [qcartExpTypeMonth] that joins all unique values of ExpTypeIDs and months. Then use this query in your crosstab and join it to your un-named table using a join that selects all records from your [qcartExpTypeMonth] query. You can use Nz() to make sure a 0 appears where there might not be a value.

Duane
Hook'D on Access
MS Access MVP

 

[BMKanun]

I'm afraid I can't get the result as you are saying.
Can you please explain a bit more?
My DefaultValue (un-named) already contains all the ID's of types as records and Months rows 01,02,...,12. All the values in the table are 0.
The qryExpencesCrosstab contains data from the Expences table. Nider all types nor all months are included because they are not all present in the table.
I thought to sum the DefValue table with the Crosstab, and thuss get the full format (60 records x 12 months) with data and zeros.
Maybe it's wrong. Maybe there is another way?

thanks, B

 

[dhookom]

You haven't provided any actual table and field names that might be needed in your solution.

Assuming we want the number of orders in the Northwind sample database by Customer and month. It is possible that not every customer will have an order each month. I use a table [tblNums] with a single numeric field [Num] and values from 1 to 1000. The first query [qcartCustomerMonths]would be:

SELECT tblNums.Num, Customers.CustomerID
FROM tblNums, Customers
WHERE tblNums.Num<=12;

I would need the month number in the Orders table so I would create a query [qselOrdersWithMonthNumber]

SELECT Month([OrderDate]) AS OrderMonth, Orders.*
FROM Orders;

Then create a crosstab from the results:

TRANSFORM Count(OrderID) AS CountOfOrderID
SELECT qcartCustomerMonths.CustomerID
FROM qcartCustomerMonths LEFT JOIN qselOrdersWithMonthNumber ON (qcartCustomerMonths.CustomerID = qselOrdersWithMonthNumber.CustomerID) AND (qcartCustomerMonths.Num = qselOrdersWithMonthNumber.OrderMonth)
GROUP BY qcartCustomerMonths.CustomerID
PIVOT Format([Num],"00");

Duane
Hook'D on Access
MS Access MVP

 

[BMKanun]

Thanks dhookom!
I finally made it, after I had trouble translating the code to the real tables and fields, but now I got what I needed.
I have an tblExpences where Month row already exist, so I didn't needed the second code.
In the firs one, I selected the Num value with the tblExpencesType.Type. the Crosstab has rows Type, column Months and Total of Sum: tblExpences.Price as values.

thanks again!
B