Create View Group by ...

[q1w2e3r4t7]

I have an accounting table with enties with date, account & value.

ie:

Date Account Value
1/01/07 Petrol $500
2/01/07 Wages $400
1/02/07 Food $30
2/02/07 Wages $400
15/02/07 Food $20

i would like to create a view sorted by Account & Date (in months)
ie

Account Jan-07 Feb-07
Petrol $500 $0
Wages $400 $400
Food $0 $50

is this possible?
i know i can get dates into months by using dateserial(year(date),month(date),1) however i cant workout how to sort by date & account.

Any help would be greatly appreciated.

Thanks.

 

[amir100]

Are you trying to create a view in the database or in your application?

Are you trying to sort or filter your data?

 

[q1w2e3r4t7]

Amir100,

im trying to create a datatable from filtering data from the database.

I have one table with a series of accounts, and another table with entries.
what i want to acheive is a matrix generated from the entries table filtered by accounts as the rows, dates as the columns, and the values corresponding to these to be the data. Basically im creating a pivot table view. (AND i can acheive this by doing a pivot table within Access 2007, however i dont know how to save that as a view or to write the SQL to generate the same)

Thanks for any help you can provide

 

[amir100]

I get what you mean.

I did some pivoting to once in a while. In my experience, the first thing I should do is create the view in the database without the pivoting. That should be easier right?

Youll get something like:

Month Account
Jan-07 Petrol
Jan-07 Wages
Jan-07 Food
Feb-07 Petrol
Feb-07 Wages
Feb-07 Food
...

That would be your main view. The next step would be using that main view and filter them by month. This is where youll get your sub views. After that youll be joining the sub-views into a large table.

Youll create your main view in the database. Filtering and joining should be done in your application because the filtering should be dynamic.

Thats what I usually do. That is of course a straight-forward solution.

Did you get the picture?

Btw, Im Amir. Nice to meet you.