Total's Query (1 Viewer)

[alpapak]

Hi,

I have a table :
tblIncome
- IncomeID : key, autonumber
- DateIncome : date, format dd/mm/yyyy
- Income : number

I want to create a query that will look like this the result:

Month1, Month2, ..., Month12, Year
1200 ***, 1500 ***, ..., 1350 *****, 2009
1400 ***, 1250 ***, ..., 1700 *****, 2010
1350 ***, 1100 ***, ..., 1600 *****, 2011

Where month is sum of each month of the each year and in the end it is year that it shows.


Any ideas ???

 

[vbaInet]

Alright alpapak, you need to look into using a Crosstab Query for this.

 

[alpapak]

Thank you very much for the reply..

Is it possible to explain more in details or show it.

 

[vbaInet]

There are so many examples on the Internet, even video examples on youtube.com

Have a look there and show us what you've tried. And if you're struggling, we'll advise.

 

[alpapak]

Thxs...

my query:
Month: Format([DateIncome];"mm")
GroupBy
RowHeading

Year: Format([DateIncome];"yyyy")
GroupBy
ColumnHeading

Price: sum([Income])
expression
Value



It is working fine... But if it shows only the months that there is data...
Not all months... from 01, 02 ... 12 And is it possible the empty field to be "0"??

Year,__01__,_02_,__03_,_04_,_06_,__09
2009,______,____,_____,____,____,__12
2010,__16__,____,_____,____,_12_,_____
2011,______,179_,_____,_18_,____,__123
2012,__170_,____,__79_,____,____,__78

 

[alpapak]

Found how to replace empty field to null...

Code:
Price: Val(Nz(Sum([Income]);0))

Now i need to show all 12months and now only the once that has data...

 

[vbaInet]

Look into the Column Headings property of a query. And also look into the Month() and Format() functions.

By the way, you don't need Val()

 

[alpapak]

Found 12months display

Month: DatePart("m";[DateIncome]) In (1;2;3;4;5;6;7;8;9;10;11;12)

Thxs i remove Val()


I am so happy!!!

The Tricky part now...

Is it possible to add from a second table like the tblIncome, the tblExpences???
This two tables are not link together...

tblExpences
ExpencesID
DateExpences
Expences

So i will get per Year/month Income and Expences...

 

[vbaInet]

Good job!

Month would have been a much simpler function to use for this:

Month: Month([DateIncome]) In (1;2;3;4;5;6;7;8;9;10;11;12)

I don't understand your last question. If there's no relationship between the two tables, how do you expect it to be displayed?

 

[alpapak]

I will create two cross tab queries... qtrIncome and qtrExpences...

Then i will create a query between this two...
I will make a relationship between YEAR..

Is it going to work??

 

[vbaInet]

That should work as long as the Year is the Row Heading. Or if it's a Column Heading, then it will need to be fixed just like you've done with Month.

 

[alpapak]

I did some test and show that :

qtrIncome has : YEAR 2008, 2009, 2010, 2011
qtrExpences has :YEAR_____, 2009, 2010,_____

Same values only two years...

But i think i can make a query first with Values which are the same in both crosstabs... Example Months... 1,2,3,4,5,6,7,8,9,10,11,12
From there i will make a new query that will contains all the YEAR's.(qtrAllYears)

Then i will make a new query(qtrSum) from the 2crosstabs and the qtrAllYears, with the relationship YEAR...

I will test it...

 

[alpapak]

New Idea...

I need to add in one column all the DateIncome and DateExpences....

Is it possible from the two tables???

 

[vbaInet]

What are those fields? Add it in the final query as a calculated field.

 

[alpapak]

Hahahaha...
Found it.....

From the two tables create a union select query(qtrAllDays)
Then i make a quary and format AllDays to YEARs and GroupBY them...(qtrAllYears)

Last query was easy... Took qtrAllYears and the two crosstabs...
Make relashionship to YEARs...

Please be so kind to have a look and see if it can be done with another way...

 

[vbaInet]

You've done quite well. I would normally use subqueries but that would just confuse the matter.

One piece of advice is that you should put the Month() and Nz() functions in your final query, rather than the intermediate queries. All formatting are best in your final output.

 

[alpapak]

Thank you very much for everything...

I was going to put nz() but it was only a demo.. I will try also the month()...