View columns as rows

[Marco]

Hi.

I'm building a databse to naanlize budgets.

I build a table and I put every diferente month as a column. So I can have
all depts as records and store the diferent cost for each month.

Example:
Dept Month1 Month2 Month3 ......
IT 20 30 25
Fnances 25 10 25
HR 10 35 25


So, now I have a problem.

My users want to get in into this diferents months like this:

Budget1 Month1
Budget1 Month2
Budget1 Month3

So when they click on month 2 they only get in in month 2 and only see the
value for month 2.

I though in create records instead of columns/fileds but then it was more
dificult to compare between the different months.


How can I solve my problem?

Best Regards,
Marco

 

[Stefan Hoffmann]

hi Marco,

I build a table and I put every diferente month as a column. So I can have
all depts as records and store the diferent cost for each month.

Dept Month1 Month2 Month3 ......
IT 20 30 25
Fnances 25 10 25
HR 10 35 25
So, now I have a problem.

Yes, this is a bad table design. You need to normalize your table.

My users want to get in into this diferents months like this:
Budget1 Month1
Budget1 Month2
Budget1 Month3

This is the correct way to store the information:

Dept Month Budget
----------------------
IT 1 20
IT 2 30
IT 3 25
HR 1 10
....

Where the primary key consists of the fields Dept and Month.

How can I solve my problem?

When you want to display the data like in your first example, you use a
pivot query. This will transform the data from rows to columns.


mfG
--> stefan <--

 

[Marco]

Hello.

But how will I make queries to find the diference between months?

Regards,
Marco

 

[Stefan Hoffmann]

hi Marco,

But how will I make queries to find the diference between months?

What kind of difference?

You can use a pivot query like your table, so that your existing queries
will still work.

mfG
--> stefan <--