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 can't 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?

Edited March 20, 2007 by amir100

[q1w2e3r4t7]

Amir100,

 

i'm 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 i'm creating a pivot table view. (AND i can acheive this by doing a pivot table within Access 2007, however i don't 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?

 

You'll 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 you'll get your sub views. After that you'll be joining the sub-views into a large table.

 

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

 

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

 

Did you get the picture?

 

Btw, I'm Amir. Nice to meet you. :D